Excel - reading different data from same file

GAMS
1

Hi,

I have one excel file with data that should be loaded in different variables, one scalar and one table. The only way I succeeded in reading them is by creating two .xls files with different names, and load data in different .gdx files.
This is not so elegant solution so I wanted to ask whether there is a solution to this issue, to read multiple data from same .xls file, either in same or separate gdx. Here is example:

parameter p(r,c);
$CALL GDXXRW Curve.xlsx trace=3 par=p rng=Sheet1!a1:f97 rdim=1 cdim=1
$gdxin Curve.gdx
$load p
$gdxin

parameter h;
$CALL GDXXRW load.xlsx trace=3 par=h rng=Sheet1!i2:i2 dim=0
$GDXIN load.gdx
$LOAD h
$GDXIN

Both data, p and h are in the same file but in different fields. One of the issues was that GAMS was asking for the same name for xlsx and gdx file. Then the second one would override the first table when they have the same name.

Thank you in advance,
Sanja

2

Sanja,

As discussed in another thread (https://newforum.gams.com/t/error-121-issue-with-indexing-gdx-data/2268/4), you can read multiple symbols from a single Excel file in one GDXXRW call. Assuming that p and h are in the same file (e.g. Curve.xlsx) you can do the following.

parameter p(r,c), h;
$CALL GDXXRW Curve.xlsx trace=3 par=p rng=Sheet1!a1:f97 rdim=1 cdim=1 par=h rng=Sheet1!i2:i2 dim=0
$gdxin Curve.gdx
$load p h
$gdxin

If you plan to read many symbols in a single GDXXRW call, the command line can become too long to be practical. Hence, it is sometimes more convenient to put the GDXXRW instructions in a parameter, e.g. as follows:

$onecho > in.txt
par=p rng=Sheet1!a1:f97 rdim=1 cdim=1 
par=h rng=Sheet1!i2:i2 dim=0
$offecho
$CALL GDXXRW Curve.xlsx trace=3 @in.txt
$gdxin Curve.gdx
$load p h
$gdxin

I hope this helps

3

Thank you Fred, this really helped me.