How to import CSV file in OPL?

Question

I'm trying to import CSV files as my variables in OPL and I can't find any easy way to do it like CSVConnection. Do some of you know how to do it ? I know I can do a script but I would want an other way if it exists

Thanks,

I tried CSVConnection and then linked the connection to my variable like I was doing with SheetRead and SheetConnection but it doesn't work

Answer 1

with csvread and csvpublish

you may have a look at

.mod https://github.com/AlexFleischerParis/zooopl/blob/master/zoocsv.mod

int nbKids=...;

// a tuple is like a struct in C, a class in C++ or a record in Pascal
tuple bus
{
  key int nbSeats;
  float cost;
}

// This is a tuple set
{bus} buses=...;

// asserts help make sure data is fine
assert forall(b in buses) b.nbSeats>0;
assert forall(b in buses) b.cost>0;

// decision variable array
dvar int+ nbBus[buses];

// objective
minimize
     sum(b in buses) b.cost*nbBus[b];
     
// constraints
subject to
{
   sum(b in buses) b.nbSeats*nbBus[b]>=nbKids;
}

tuple t_result
{
  key int nbSeats;
  int nb;
}

{t_result} result={<b.nbSeats,nbBus[b]> | b in buses};

.dat https://github.com/AlexFleischerParis/zooopl/blob/master/zoocsv.dat

/*

the csv file is

nbseats;cost;
40;500;
30;400;

*/

nbKids=300;

// New feature in CPLEX 20.10

CSVConnection csvinput("zoobusesinput.csv?firstIsHeader=true", "");
buses from CSVRead(csvinput, "");

CSVConnection csvoutput("zoobusesoutput.csv?outputOnly=true&append=false&outputSeparator=;", "");
result to CSVPublish(csvoutput, "");

/*

and the output file

40;6
30;2

*/

or if you want to do everything in scripting see https://github.com/AlexFleischerParis/opltipsandtricks/blob/master/turncsvintodat.mod

// Suppose you have export.csv
//
//    Name;Rank;
//    Nicolas;2;
//    Alexander;3;
//
//and you need to use that with OPL
//
//Then the OPL model

    execute
    {

    function readCSV(csvfilename,datfilename,tuplesetname)
    {
        var quote="\"";

        var python=new IloOplOutputFile("c:\\temp\\readCSV.py");
        python.writeln("from pandas import *");
        python.writeln("df=read_csv(",quote,csvfilename,quote,",sep=\';\')");
        python.writeln("res = open(",quote,datfilename,quote,",",quote,"w",quote,")");
        python.writeln("res.write(",quote,tuplesetname,"={",quote,")");
        python.writeln("res.write(",quote,"\\","n",quote,")");
        python.writeln("for i, row in enumerate(df.values):");
       
        python.writeln("   res.write(",quote,"<",quote,")");
        python.writeln("   for j in row:");
        python.writeln("      if (j==j):");
        python.writeln("         res.write(str(j))");
        python.writeln("         res.write(\",\")");
        python.writeln("   res.write(\">,\")    ");
        python.writeln("   res.write(",quote,"\\","n",quote,")");
        python.writeln("res.write(\"};\")");
        python.writeln("res.close()");
        python.close();
        
        IloOplExec("C:\\Python36\\python.exe c:\\temp\\readCSV.py");
    }


    readCSV("c:\\\\temp\\export.csv","c:\\\\temp\\\\kings.dat","s");

    }

//will generate kings.dat
//
//    s={
//    <Nicolas,2,>,
//    <Alexander,3,>,
//    };
//