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
Looping over Different Excel Sheets
-
- User
- Posts: 30
- Joined: 4 years ago
Re: Looping over Different Excel Sheets
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.
GAMS code and Excel files are attached.
Re: Looping over Different Excel Sheets
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
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
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
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 196 times
-
- Packdata3.xlsx
- (10.53 KiB) Downloaded 192 times
-
- Packdata2.xlsx
- (11.81 KiB) Downloaded 220 times
-
- User
- Posts: 30
- Joined: 4 years ago
Re: Looping over Different Excel Sheets
Thank you GFA for your kind reply.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
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?
-
- User
- Posts: 30
- Joined: 4 years ago
Re: Looping over Different Excel Sheets
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.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
-
- User
- Posts: 30
- Joined: 4 years ago
Re: Looping over Different Excel Sheets
Here is updated GAMS file.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
Re: Looping over Different Excel Sheets
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
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
-
- User
- Posts: 30
- Joined: 4 years ago
Re: Looping over Different Excel Sheets
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
Hi
Please post your excel sheet or report the errors you get.
Cheers
Renger
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
Enjoy modeling even more: Read my blog on modeling at The lazy economist