Excel - reading different data from same file Topic is solved

questions about GAMS' tools
Post Reply
User avatar
sanjadzeletovic
User
User
Posts: 7
Joined: 4 months ago

Excel - reading different data from same file

Post by sanjadzeletovic » 4 months ago

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:

Code: Select all

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

Fred
Posts: 74
Joined: 2 years ago

Re: Excel - reading different data from same file

Post by Fred » 4 months ago

Sanja,

As discussed in another thread (viewtopic.php?f=2&p=24182#p24182), 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.

Code: Select all

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:

Code: Select all

$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

User avatar
sanjadzeletovic
User
User
Posts: 7
Joined: 4 months ago

Re: Excel - reading different data from same file

Post by sanjadzeletovic » 4 months ago

Thank you Fred, this really helped me.

Post Reply