Error in reading 4 dimensional data from EXCEL to GAMS

GAMS
1

Hello Experts,

I have a 4-dimensional data in Excel as follows.

  1. Row 1: Set a with 2 elements, /‘Refinery 1’,‘Refinery 2’/
  2. Row 2: Set b with 2 elements, /‘Pipeline’,‘Rail’/
  3. Column 1: Set q with 2 elements, /‘Warehouse 1’,‘Warehouse 2’/
  4. Column 2: Set r with 3 elements, /‘Petroleum’,‘Diesel’, ‘Jet Fuel’/


    My data looks like this in Excel.
    data.JPG
    I try to import the data from Excel to GAMS by the following code.
   sets  a  first row entries
          b  second row entries
          q  first column entries
          r  second column entries
    
    parameter data3(a,b,q,r);
    
    $CALL GDXXRW.EXE i=Try1.xlsx o=Try1.gdx  set=a rng=Sheet1!A3:A6 rdim=1 set=b rng=Sheet1!B3:B6 rdim=1 set=q rng=Sheet1!C1:H1 cdim=1 set=r rng=Sheet1!C2:H2 cdim=1 par=data3 rng=Sheet1!A1:F6 Rdim=2 Cdim=2
    
    $GDXIN Try1.gdx $LOAD a,b,q,r,data3 $GDXIN
    
    display data3;

It should produce the resulting parameter, i.e data3 as 24 elements (2x2x2x3), But it only shows 16 elements, due to some problems and ignores other elements.

The resulting GDX looks like this!
result.JPG
What am I doing wrong? Any help will be highly appreciated. Thanks!

2

Hi,

I am surprised that you claim to get the shown gdx file. Actually, for the Excel file shown in the screenshot, your GDXXRW call should produce a couple of errors and not create a GDX file at all:

**** There were 2 duplicate entries for symbol a
 1: Refinery 1
 2: Refinery 2
**** There were 2 duplicate entries for symbol b
 1: Pipeline
 2: Rail
**** There were 4 duplicate entries for symbol q
 1: Warehouse 1
 2: Warehouse 1
 3: Warehouse 2
 4: Warehouse 2
**** There were 3 duplicate entries for symbol r
 1: Petroleum
 2: Diesel
 3: Jet Fuel
Output gdx file deleted: C:\Users\user\Documents\support\Try1.gdx
Total time = 984 Ms
The option "MaxDupeErrors = <integer>" can be used to allow for some duplicate records

You should use “dset” instead of “set” to avoid these errors. For data3, you explicitly instruct GDXXRW to stop reading at F6. This should be H6 and you will get all 24 records instead of just 16.

I am also a bit confused by how you use the terms “row” and “column”. From my perspective your explanation of the data in Excel should read:1. Column A: Set a with 2 elements, /‘Refinery 1’,‘Refinery 2’/
2. Column B: Set b with 2 elements, /‘Pipeline’,‘Rail’/
3. Row 1: Set q with 2 elements, /‘Warehouse 1’,‘Warehouse 2’/
4. Row 2: Set r with 3 elements, /‘Petroleum’,‘Diesel’, ‘Jet Fuel’/

Also, the $GDXIN and $LOAD commands cannot be in the same row. I replicated the Excel file (attached) and slightly modified your code:

sets a  first column entries
     b  second column entries
     q  first row entries
     r  second row entries;
 
parameter data3(a,b,q,r);

*create gdxxrw parameter file to improve readability
$onecho > gdxxrw_in.txt
dset=a rng=Sheet1!A3:A6 rdim=1
dset=b rng=Sheet1!B3:B6 rdim=1
dset=q rng=Sheet1!C1:H1 cdim=1
dset=r rng=Sheet1!C2:H2 cdim=1
par=data3 rng=Sheet1!A1:H6 Rdim=2 Cdim=2
$offecho
 
$CALL GDXXRW.EXE i=Try1.xlsx o=Try1.gdx @gdxxrw_in.txt
$ife errorlevel<>0 $abort Problems calling GDXXRW
$GDXIN Try1.gdx
$LOAD a,b,q,r,data3
$GDXIN
display a,b,q,r,data3;

Note that you could also avoid reading all the domain sets explicitly with GDXXRW but project them out of the parameter domain when you load the data into GAMS which some users find more convenient:

sets a  first column entries
     b  second column entries
     q  first row entries
     r  second row entries;
 
parameter data3(a,b,q,r);
$CALL GDXXRW.EXE i=Try1.xlsx o=Try1.gdx par=data3 rng=Sheet1!A1:H6 Rdim=2 Cdim=2
$ife errorlevel<>0 $abort Problems calling GDXXRW
$GDXIN Try1.gdx
$LOAD a<data3.dim1 b<data3.dim2 q<data3.dim3 r<data3.dim4 data3
$GDXIN
display a,b,q,r,data3;

I hope this helps!

Fred
try1.xlsx (8.71 KB)

3

Dear Fred,

Thanks a ton!
It works perfectly.