How to read a tuple in CPLEX, OPL which has integer, string, and other sets

Question

I am trying to read a tuple from excel. The tuple has integers, strings and sets. I have tried the following but am getting error as : Data element "Pbd" of type {Path} not supported for sheets. And the Processing fails.

This is part of my .mod file

tuple Path {
int id;
string source;
string dest;
{string} pitblockSet;
{string} roadPoints; // not used
{string} dumpblockSet;
{string} others;
float dist;
};


{Path} Pbd=...; 

The corresponding part in the dat file is :
Pbd from SheetRead(sheet,"all_paths!A2:C30910");

In the Excel File on sheet all_paths I have the following. There are several other variables being read from the same excel in this model.

Part of the excel data being read to this tuple is below :

PathId  Source  Dest    pitblockSet RoadPoints  dumpblockSet    others  dist
1       P1      D1      P1          R8         D45 D42 D39 D14 D1       581.3956
2       P1      D1      P1          R8         D40 D14 D1               587.1185
3       P1      D1      P1          R8         D43 D16 D2 D1            588.7774
4       P2      D1      P2          R8         D45 D42 D39 D14 D1       539.7307
5       P2      D1      P2          R8         D40 D14 D1               545.4535
6       P2      D1      P2          R8         D43 D16 D2 D1            547.1124
7       P3      D1      P3          R8         D45 D42 D39 D14 D1       500.0794

I have also tried by changing the data to comma separated sets like below

PathId  Source  Dest    pitblockSet RoadPoints  dumpblockSet    Others  Distance
1       P1      D1      P1,          R8,        D45,D42,D39,D14,D1,     581.3956
2       P1      D1      P1,          R8,        D40,D14,D1,             587.1185
3       P1      D1      P1,          R8,        D43,D16,D2,D1,          588.7774
4       P2      D1      P2,          R8,        D45,D42,D39,D14,D1,     539.7307
5       P2      D1      P2,          R8,        D40,D14,D1,             545.4535
6       P2      D1      P2,          R8,        D43,D16,D2,D1,          547.1124
7       P3      D1      P3,          R8,        D45,D42,D39,D14,D1,     500.0794
8       P3      D1      P3,          R8,        D40,D14,D1,             505.8023

But I keep getting the same error.

The purpose why I want these is, I am using them in the .mod file as below :

float hc[Pathid][TimePeriods];    //PathId is another int variable read seperately

//determine haulage cost for each path
execute {
//distances to plant
for (var i in Pbm.id) {
for (var t in TimePeriods){
  hc[i][t] = Pbm.dist*HaulageCost[t];
}
}
}

And finally want to use it in a constraint as

forall( i in Pbd.pitblockSet ,  t in TimePeriods) { 
       // blockabove exposed Pbd:
        sum(j in BlockBelow[i]) schedulePit[j.id][t] * totalVolume[j.id] <= 
        (sum(j in BlockBelow[i],r in TimePeriods : r <= t,d in DumpBlocks)(Xbdt[j.id][d][r])  
        + sum(j in BlockBelow[i],r in TimePeriods : r <= t, s in Stockpiles)(Xbst[j.id][s][r]/density[j.id])
        +sum(j in BlockBelow[i],r in TimePeriods : r <= t, m in Plants)(Xbmt[j.id][m][r]/density[j.id]))  ;      
        }  

What is the best way to read the tuple named Path, not sure why I am getting the error.

Answer 1

You cannot do this because OPL cannot know how to create a set from a flat string. However, you can do this initialization using scripting: You first read your data into a raw tuple in which the data for the fields is just a plain string (the content of the cell in Excel). Next you use scripting to convert those flat strings into actual sets:

tuple Raw {
  int id;
  string source;
  string dest;
  string pitblockSet;
  string roadPoints;
  string dumpblockSet;
  string others;
  float dist;
}
{Raw} raw = ...; // This is read from the spreadsheet

tuple Path {
  int id;
  string source;
  string dest;
  {string} pitblockSet;
  {string} roadPoints;
  {string} dumpblockSet;
  {string} others;
  float dist;
}
{Path} path = {}; // This is filled in the scripting block below.
{string} emptyset = {}; // Helper to create new empty sets in scripting.

// Populate the path set.
execute {
  // Split a string by a separator and add all the tokens to a set.
  function splitAndAdd(set, data, sep) {
    var fields = data.split(sep);
    for (var i = 0; i < fields.length; ++i) {
      set.add(fields[i]);
    }
  }
  for (var r in raw) {
    var s = Opl.operatorUNION(emptyset, emptyset);
    // Add a new tuple. The fields of type set are empty sets for now.
    // We cannot just pass 'emptyset' because then all fields would
    // reference the same set. So we create a new emptyset as the union
    // of two empty sets.
    var t = path.add(r.id, r.source, r.dest,
                     Opl.operatorUNION(emptyset, emptyset),
                     Opl.operatorUNION(emptyset, emptyset),
                     Opl.operatorUNION(emptyset, emptyset),
                     Opl.operatorUNION(emptyset, emptyset),
                     r.dist);
    // Now populate the fields of type set in the newly created tuple.
    splitAndAdd(t.pitblockSet, r.pitblockSet, " ");
    splitAndAdd(t.roadPoints, r.roadPoints, " ");
    splitAndAdd(t.dumpblockSet, r.dumpblockSet, " ");
  }
  writeln(path);
}