Looping over Different Excel Sheets

Problems with syntax of GAMS
Luqman Hakeem
User
User
Posts: 30
Joined: 4 years ago

Looping over Different Excel Sheets

Post by Luqman Hakeem »

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
Luqman Hakeem
User
User
Posts: 30
Joined: 4 years ago

Re: Looping over Different Excel Sheets

Post by Luqman Hakeem »

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 KiB) Downloaded 230 times
Pakdata.xlsx
(1.2 MiB) Downloaded 224 times
GFA
User
User
Posts: 50
Joined: 5 years ago

Re: Looping over Different Excel Sheets

Post by GFA »

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
Rodrigue
User
User
Posts: 33
Joined: 5 years ago

Re: Looping over Different Excel Sheets

Post by Rodrigue »

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
Attachments
seethis.gms
(3.34 KiB) Downloaded 195 times
Packdata3.xlsx
(10.53 KiB) Downloaded 189 times
Packdata2.xlsx
(11.81 KiB) Downloaded 219 times
Luqman Hakeem
User
User
Posts: 30
Joined: 4 years ago

Re: Looping over Different Excel Sheets

Post by Luqman Hakeem »

GFA wrote: 4 years ago 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
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?
Luqman Hakeem
User
User
Posts: 30
Joined: 4 years ago

Re: Looping over Different Excel Sheets

Post by Luqman Hakeem »

Rodrigue wrote: 4 years ago 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
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.
Luqman Hakeem
User
User
Posts: 30
Joined: 4 years ago

Re: Looping over Different Excel Sheets

Post by Luqman Hakeem »

GFA wrote: 4 years ago 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
Here is updated GAMS file.
Updatedfile.gms
(20.8 KiB) Downloaded 215 times
GFA
User
User
Posts: 50
Joined: 5 years ago

Re: Looping over Different Excel Sheets

Post by GFA »

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
Luqman Hakeem
User
User
Posts: 30
Joined: 4 years ago

Re: Looping over Different Excel Sheets

Post by Luqman Hakeem »

GFA wrote: 4 years ago 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
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 KiB) Downloaded 214 times
User avatar
Renger
Posts: 639
Joined: 7 years ago

Re: Looping over Different Excel Sheets

Post by Renger »

Hi
Please post your excel sheet or report the errors you get.
Cheers
Renger
____________________________________
Enjoy modeling even more: Read my blog on modeling at The lazy economist
Post Reply