Importing multiple parameters from an excel table

Problems with syntax of GAMS
Post Reply
pitters
User
User
Posts: 9
Joined: 3 months ago

Importing multiple parameters from an excel table

Post by pitters » 2 months ago

Hello,

Im fairly new at GAMS, and im having trouble trying to import multiple parameters from an excel table.

The table im trying to import looks like "ATTACHMENT 1" , asumme the table starts at a1, and the sheet is called "sheet1"

My easy fix is dividing the table as "ATTACHMENT 2" and using the following code, but it would be desirable to keep all the data in one table

$CALL GDXXRW.EXE i=test.xlsx o=rep\test par=param1 rng=sheet1!a1 Rdim=2 DSET=set1 rng=sheet1!a2 Rdim=1 DSET=set2 rng=test!b2 Rdim=1
$CALL GDXXRW.EXE i=test.xlsx o=rep\test par=param2 rng=sheet1!a1 Rdim=2 DSET=set1 rng=sheet1!a2 Rdim=1 DSET=set2 rng=test!b2 Rdim=1


Image
Attachments
imagen.png
imagen.png (4.26 KiB) Viewed 582 times
imagen.png
imagen.png (2.73 KiB) Viewed 582 times

abhosekar
Moderator
Moderator
Posts: 208
Joined: 1 year ago

Re: Importing multiple parameters from an excel table

Post by abhosekar » 2 months ago

Few points:
1. default is Sheet!a1 so no need to specify.
2 .The option you are missing here is ignoreRows and ignoreColumns to tell gdxxrw that the columns should be ignored. Check here: https://www.gams.com/latest/docs/T_GDXX ... ND_COLUMNS
3. You have to use these options because a natural way to provide 2D parameters is to have it as a matrix where rows represent one dimension and the columns represents the other.
4. It would have been better if you had uploaded the spreadsheet.

I don't think you should read parameters in the same line as you are doing with multiple rdim and cdim. A better way is to use options file as shown here https://www.gams.com/latest/docs/T_GDXX ... DSHEET_PAR

I created a table as you show in the second pictuer (where both param1 and param2 are present as two columns)

To read param1
$call GDXXRW.EXE i=test.xlsx o=rep\test par=param1 rdim=2 ignoreRows=1 ignoreColumns =4

Similarly, for param2
$call GDXXRW.EXE i=test.xlsx o=rep\test par=param1 rdim=2 ignoreRows=1 ignoreColumns =3

HTH
- Atharv

pitters
User
User
Posts: 9
Joined: 3 months ago

Re: Importing multiple parameters from an excel table

Post by pitters » 2 months ago

Thanks abhosekar, very very helpful answer!. I solved the problem. Also thanks for pointing the reading error
"I don't think you should read parameters in the same line as you are doing with multiple rdim and cdim. A better way is to use options file as shown here https://www.gams.com/latest/docs/T_GDXX ... DSHEET_PAR"
This change a lot of stuff, I was reading the sets and then (without knowing it) re-reading sets when reading parameters (thinking this would link the parameters with the sets)

I was writing:
Sets
set1
set2

Parameters:
param1(set1, set2)

$CALL GDXXRW.EXE i=sets.xlsx o=rep\set1 set=set1 rng=set1!a1 Rdim=1
$CALL GDXXRW.EXE i=sets.xlsx o=rep\set2 set=set2 rng=set2!a1 Rdim=1
$CALL GDXXRW.EXE i=params.xlsx o=rep\param1 par=param1 rng=param1!a1 Rdim=2 DSET=set1 .... DSET=set2....

**LOADS Here **

What I think is the correct way with your help :
Sets
set1
set2

Parameters:
param1(set1, set2)

$CALL GDXXRW.EXE i=sets.xlsx o=rep\set1 set=set1 rng=set1!a1 Rdim=1
$CALL GDXXRW.EXE i=sets.xlsx o=rep\set2 set=set2 rng=set2!a1 Rdim=1
$CALL GDXXRW.EXE i=params.xlsx o=rep\param1 par=param1 rng=param1!a1 Rdim=2

**LOADS Here **

Is this better?

Thanks a lot.

abhosekar
Moderator
Moderator
Posts: 208
Joined: 1 year ago

Re: Importing multiple parameters from an excel table

Post by abhosekar » 2 months ago

I have not tested your code but this is what I was referring to in one of my points. Please follow remaining points too as I don't think your last line for param1 should work because you are not telling gdxxrw to ignore the last column for param2.

The best way to debug this is by running just this one line of code independently and then checking the resulting gdx file.

You can even combine all calls to a single file in one using an options file.

create the following file, say gdxxrw_options.txt that contains the following lines

o=rep\set1 set=set1 rng=set1!a1 Rdim=1
o=rep\set2 set=set2 rng=set2!a1 Rdim=1


You can then simply call
$call gdxrrw sets.xlsx @gdxxrw_options.txt

- Atharv

Post Reply