Looping over Different Excel Sheets

GAMS
1

I am currently working on a code which requires data to be imported from different Excel sheets. I do not know how to do it. Can you help me regarding this?
Looking forward to hearing from you.

Regards,

Luqman Hakeem

2

First, I developed the code for summer and winter season of a year (in total 13 years, 26 files), but now I want to have all the 26 files in a single code. I have imported data from 26 Excel sheets of file named “Pakdata”. I am a bit confused about how to take data from 26 Excel sheets through loop in a single code. Can anyone help me?
GAMS code and Excel files are attached.
Updatedfile.gms (8.34 KB)
Pakdata.xlsx (1.2 MB)

3

Hi,

Maybe you can do your parameter declaration first:

Parameters
FixC(u,k)
VarC(u,k)
GrsC(u,k)

  • etc.
    ;

*And then make a text file of all your parameters + ranges + dimensions you want to read in:

$onecho > GDXXRW.txt
par=FixC rng=S2018!b120:c184 rdim=1
par=VarC rng=S2018!f120:g184 rdim=1
par=GrsC rng=S2018!j120:k184 rdim=1

  • … etc.
    $offecho

*And then read in all with one GDXXRW call:

$call GDXXRW I=Pakdata.xlsx @GDXXRW.txt O=Pakdata.gdx trace=3

  • And afer that load all parameters from GDX file:

$gdxin Pakdata.gdx
$load FixC, Varc, GrsC, <etc.>
$gdxin



Hope this helps.

Regards,
GFA

4

Hi,

I thing the way you compile your .xls data is not good. To my knowledge you should prepare your excel data such that they deal with your sets declaration. I suggested you a way to do that in attachments if it corresponds to what you want. Some comments are given in it.

Best regards

Rodrigue
seethis.gms (3.34 KB)
Packdata3.xlsx (10.5 KB)
Packdata2.xlsx (11.8 KB)

5

Thank you GFA for your kind reply.
I have modified my file as per your direction. Errors regarding the dimensions are now removed but few errors related to GDXIN (502: GDXIN file not open - ignore rest of line & 510: Unable to open gdx file for $GDXIN) are still there. Can you help me to remove these?

6

Thank you Rodrigue for your help. Your idea is good but i have to consider each year in my file and i have to take outputs for each and every year separatley.

7

Here is updated GAMS file.
Updatedfile.gms (20.8 KB)

8

Hi Luqman,

In your GDXXRW call you also have to specify an output file (GDX):

$CALL GDXXRW.EXE Pakdata.xlsx @Pakdata.txt Pakdata.gdx

Hope this helps.

GFA

9

Hi GFA,

I have updated my GAMS file as per your reply. But i am still facing errors.
Can you please suggest me something else?
Updatedfile.gms (20.8 KB)

10

Hi
Please post your excel sheet or report the errors you get.
Cheers
Renger

11

Hi Renger
Following are the errors.

Error Messages

141 Symbol declared but no values have been assigned. Check for missing
data definition, assignment, data loading or implicit assignment
via a solve statement.
A wild shot: You may have spurious commas in the explanatory
text of a declaration. Check symbol reference list.
257 Solve statement not checked because of previous errors
502 GDXIN file not open - ignore rest of line

Please find the attachments if you want.
Error Report.pdf (227 KB)
Pakdata.xlsx (1.2 MB)

12

Hi
I am getting different errors, but anyway, here are my comments:

In your Gams file you read over and over again the same parameters (e.g. FIXC) from different sheets in the same gdx-file. However, this would mean that you would overwrite these values each time you call gdxxrw.
As you have everything hardcoded now, I would proceed as follows:
[-] Check your reading over the first block (there are at the moment dimensionality errors). You can do this easily by slightly adjusting your code and using $exit to stop further compilation:

$onecho > Pakdata.txt
par=FixC rng=S2018!b120:c184
par=VarC rng=S2018!f120:g184
par=GrsC rng=S2018!j120:k184
par=CapF rng=S2018!n120:o184
par=FC rng=S2018!b2:e56
par=VC rng=S2018!i2:l56
par=GC rng=S2018!p2:s56
par=CF rng=S2018!w2:z56
par=FuelExi rng=S2018!w60:z114
par=HRExi rng=S2018!p60:s114
par=CO2 rng=S2018!b60:e114
par=FCost rng=S2018!b188:d228
par=VCost rng=S2018!h188:j228
par=GCap rng=S2018!n188:p228
par=CFac rng=S2018!t188:v228
par=CapC rng=S2018!b279:d319
par=CO2new rng=S2018!b233:d273
par=HRNew rng=S2018!n233:p273
par=FuelNew rng=S2018!t233:v273
$offecho

$CALL GDXXRW.EXE Pakdata.xlsx o=2018.gdx @Pakdata.txt
$GDXIN Pakdata.gdx
$LOAD FixC, VarC, GrsC, CapF, FC, VC, GC, CF, FuelExi, HRExi, CO2, FCost, VCost, Gcap, CFac, CapC, CO2new, HRNew, FuelNew
$GDXIN
$exit

[-] If that is error-free, you can add delete the the lines from $LOAD to $exit and add after each block the following lines

$offecho
$CALL GDXXRW.EXE Pakdata.xlsx o=2018.gdx @Pakdata.txt

* And for the next block the same
..
par=FuelNew rng=S2019!t233:v273
$offecho
$CALL GDXXRW.EXE Pakdata.xlsx o=2019.gdx @Pakdata.txt

... etc.

Note the different name for the gdx file.
[-] Run all this code in a separate file (e.g. readdata.gms).
[-] use the gdxmerge command to merge all these files which will result in your parameters with the additional index of the year (in fact the name of your gdx file). e.g. FixC(year, …).
[-] Now read the newly built parameters into your model file and adjust code for the additional index.

I hope this helps
Cheers
Renger

13

Hi
Thank you Renger for your help.
I have prepared readata.gms and merged.gdx files but i am facing issues of dimenions (Error: Load dimensions are different) when i call parameter from merged.gdx file.
Files are attached for your kind review.
Pak2.xlsx (1.54 MB)
readdata.gms (28 KB)
Updatedfile1.gms (34.6 KB)

14

Hi Renger
I have found my errors regarding the different load dimensions. But i am getting infeasible results now. Can you suggest me something.
Corrected files are attached below.
modelfile.gms (8.13 KB)
Pak3.xlsx (1.27 MB)
readdata2.gms (27.3 KB)

15

Hi

Sorry, I can’t be of any help here. Perhaps you could search for ideas in this forum.

Renger

PS. If you add the option CheckDate at the end of your gdxxrw command, it checks if the excel file changed, if not, it doesn’t read the data and saves you time.

16

Thank you Renger. It means a lot to me.