Looping over Different Excel Sheets

Problems with syntax of GAMS
Luqman Hakeem
User
User
Posts: 10
Joined: 2 weeks ago

Looping over Different Excel Sheets

Post by Luqman Hakeem » 2 weeks ago

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: 10
Joined: 2 weeks ago

Re: Looping over Different Excel Sheets

Post by Luqman Hakeem » 2 weeks ago

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 10 times
Pakdata.xlsx
(1.2 MiB) Downloaded 11 times

GFA
User
User
Posts: 12
Joined: 1 year ago

Re: Looping over Different Excel Sheets

Post by GFA » 2 weeks 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

Rodrigue
User
User
Posts: 12
Joined: 1 year ago

Re: Looping over Different Excel Sheets

Post by Rodrigue » 2 weeks 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
Attachments
seethis.gms
(3.34 KiB) Downloaded 11 times
Packdata3.xlsx
(10.53 KiB) Downloaded 11 times
Packdata2.xlsx
(11.81 KiB) Downloaded 13 times

Luqman Hakeem
User
User
Posts: 10
Joined: 2 weeks ago

Re: Looping over Different Excel Sheets

Post by Luqman Hakeem » 2 weeks ago

GFA wrote:
2 weeks 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: 10
Joined: 2 weeks ago

Re: Looping over Different Excel Sheets

Post by Luqman Hakeem » 2 weeks ago

Rodrigue wrote:
2 weeks 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: 10
Joined: 2 weeks ago

Re: Looping over Different Excel Sheets

Post by Luqman Hakeem » 2 weeks ago

GFA wrote:
2 weeks 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 10 times

GFA
User
User
Posts: 12
Joined: 1 year ago

Re: Looping over Different Excel Sheets

Post by GFA » 1 week 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

Luqman Hakeem
User
User
Posts: 10
Joined: 2 weeks ago

Re: Looping over Different Excel Sheets

Post by Luqman Hakeem » 1 week ago

GFA wrote:
1 week 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 13 times

User avatar
Renger
Posts: 496
Joined: 3 years ago

Re: Looping over Different Excel Sheets

Post by Renger » 1 week ago

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