Looping over Different Excel Sheets

Problems with syntax of GAMS
Luqman Hakeem
User
User
Posts: 24
Joined: 6 months ago

Looping over Different Excel Sheets

Post by Luqman Hakeem » 6 months 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: 24
Joined: 6 months ago

Re: Looping over Different Excel Sheets

Post by Luqman Hakeem » 6 months 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 46 times
Pakdata.xlsx
(1.2 MiB) Downloaded 48 times

GFA
User
User
Posts: 13
Joined: 2 years ago

Re: Looping over Different Excel Sheets

Post by GFA » 6 months 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: 15
Joined: 2 years ago

Re: Looping over Different Excel Sheets

Post by Rodrigue » 6 months 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 43 times
Packdata3.xlsx
(10.53 KiB) Downloaded 51 times
Packdata2.xlsx
(11.81 KiB) Downloaded 46 times

Luqman Hakeem
User
User
Posts: 24
Joined: 6 months ago

Re: Looping over Different Excel Sheets

Post by Luqman Hakeem » 6 months ago

GFA wrote:
6 months 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: 24
Joined: 6 months ago

Re: Looping over Different Excel Sheets

Post by Luqman Hakeem » 6 months ago

Rodrigue wrote:
6 months 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: 24
Joined: 6 months ago

Re: Looping over Different Excel Sheets

Post by Luqman Hakeem » 6 months ago

GFA wrote:
6 months 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 46 times

GFA
User
User
Posts: 13
Joined: 2 years ago

Re: Looping over Different Excel Sheets

Post by GFA » 6 months 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: 24
Joined: 6 months ago

Re: Looping over Different Excel Sheets

Post by Luqman Hakeem » 6 months ago

GFA wrote:
6 months 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 46 times

User avatar
Renger
Posts: 588
Joined: 4 years ago

Re: Looping over Different Excel Sheets

Post by Renger » 6 months 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