Page 1 of 2
Looping over Different Excel Sheets
Posted: Wed Mar 18, 2020 8:15 am
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
Re: Looping over Different Excel Sheets
Posted: Wed Mar 18, 2020 9:45 am
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.
Re: Looping over Different Excel Sheets
Posted: Fri Mar 20, 2020 12:01 am
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
Re: Looping over Different Excel Sheets
Posted: Fri Mar 20, 2020 9:15 am
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
Re: Looping over Different Excel Sheets
Posted: Mon Mar 23, 2020 8:29 am
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?
Re: Looping over Different Excel Sheets
Posted: Mon Mar 23, 2020 8:31 am
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.
Re: Looping over Different Excel Sheets
Posted: Mon Mar 23, 2020 1:29 pm
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.
Re: Looping over Different Excel Sheets
Posted: Mon Mar 23, 2020 10:31 pm
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
Re: Looping over Different Excel Sheets
Posted: Tue Mar 24, 2020 8:00 am
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?
Re: Looping over Different Excel Sheets
Posted: Thu Mar 26, 2020 7:47 am
by Renger
Hi
Please post your excel sheet or report the errors you get.
Cheers
Renger