Better way for Using GDXXRW to import from excel

GAMS
1

I have an excel file that want to read its coulmn separately and assign them to some paprameters. So, for example I have a set of locations " set i /1*12/"

each of the location has some attributes: density, FD,S price, and income

The only way I know how to import these is using this format

for example for density and FDS:

parameter density(i,d), FDS(i,f);

*$call GDXXRW.exe filename.xlsx  par=density rng=sheet1!a1:b12   trace=2 rdim=1 cdim=0
*$GDXIN filename.gdx
*$LOAD density
*$GDXIN

*$call GDXXRW.exe filename.xlsx  par=density rng=sheet1!c1:d12   trace=2 rdim=1 cdim=0
*$GDXIN filename.gdx
*$LOAD density
*$GDXIN

This is the file. The actual data set in the one on the right. However, as I don’t know a better way, I use something like the left ones (I copy every location columns near the desired coulmn to match indicies). However, this is not a good practice I guess. And I think there would be a better way to easily import pameters while having correct indicies.


My question:

How to import every columns in the spreadsheet? I’d like to have a parameter d(location, density) = value that occurs in associated cell, likewise for other parameters.
image.png

2

gdxxrw has the IgnoreColumn option this allows you to skip over columns and the following works:

gdxxrw Book1.xlsx par=B rng=A1 rdim=1 cdim=0 IgnoreColumns=B par=C rng=A1 rdim=1 cdim=0 IgnoreColumns=B:C

for the following sheet:
image.png
If I have data like this I usually read this is as a 2-dim table and split the data inside GAMS (or just work with the 2-dim table in the equations etc):

set i, h /A,B,C,D/;
parameter d(i<,h);
$call.checkErrorLevel gdxxrw gdxxrw Book1.xlsx par=d rng=A1 rdim=1 cdim=1
$gdxIn Book1
$loadDC d
Parameter A(i),B(I);
A(i) = d(i,'A'); B(i) = d(i,'B');
variable x(i); equation e; e.. sum(i$(d(i,'C')>5), x(i)) =e= 1;

-Michael

3

Hi Michael, It’s very informative to me. Thanks a lot!