Looping over Different Excel Sheets

[Luqman Hakeem]

Hi
Please post your excel sheet or report the errors you get.
Cheers
Renger

Hi Renger
Following are the errors.

Error Messages

141 Symbol declared but no values have been assigned. Check for missing
data definition, assignment, data loading or implicit assignment
via a solve statement.
A wild shot: You may have spurious commas in the explanatory
text of a declaration. Check symbol reference list.
257 Solve statement not checked because of previous errors
502 GDXIN file not open - ignore rest of line

Please find the attachments if you want.

Error Report.pdf

(226.56 KiB) Downloaded 158 times

Pakdata.xlsx

(1.2 MiB) Downloaded 144 times

[Renger]

Hi
I am getting different errors, but anyway, here are my comments:

In your Gams file you read over and over again the same parameters (e.g. FIXC) from different sheets in the same gdx-file. However, this would mean that you would overwrite these values each time you call gdxxrw.
As you have everything hardcoded now, I would proceed as follows:
[-] Check your reading over the first block (there are at the moment dimensionality errors). You can do this easily by slightly adjusting your code and using $exit to stop further compilation:

Code: Select all

$onecho > Pakdata.txt
par=FixC rng=S2018!b120:c184
par=VarC rng=S2018!f120:g184
par=GrsC rng=S2018!j120:k184
par=CapF rng=S2018!n120:o184
par=FC rng=S2018!b2:e56
par=VC rng=S2018!i2:l56
par=GC rng=S2018!p2:s56
par=CF rng=S2018!w2:z56
par=FuelExi rng=S2018!w60:z114
par=HRExi rng=S2018!p60:s114
par=CO2 rng=S2018!b60:e114
par=FCost rng=S2018!b188:d228
par=VCost rng=S2018!h188:j228
par=GCap rng=S2018!n188:p228
par=CFac rng=S2018!t188:v228
par=CapC rng=S2018!b279:d319
par=CO2new rng=S2018!b233:d273
par=HRNew rng=S2018!n233:p273
par=FuelNew rng=S2018!t233:v273
$offecho

$CALL GDXXRW.EXE Pakdata.xlsx o=2018.gdx @Pakdata.txt
$GDXIN Pakdata.gdx
$LOAD FixC, VarC, GrsC, CapF, FC, VC, GC, CF, FuelExi, HRExi, CO2, FCost, VCost, Gcap, CFac, CapC, CO2new, HRNew, FuelNew
$GDXIN
$exit

[-] If that is error-free, you can add delete the the lines from $LOAD to $exit and add after each block the following lines

Code: Select all

$offecho
$CALL GDXXRW.EXE Pakdata.xlsx o=2018.gdx @Pakdata.txt

* And for the next block the same
..
par=FuelNew rng=S2019!t233:v273
$offecho
$CALL GDXXRW.EXE Pakdata.xlsx o=2019.gdx @Pakdata.txt

... etc.

Note the different name for the gdx file.
[-] Run all this code in a separate file (e.g. readdata.gms).
[-] use the gdxmerge command to merge all these files which will result in your parameters with the additional index of the year (in fact the name of your gdx file). e.g. FixC(year, ....).
[-] Now read the newly built parameters into your model file and adjust code for the additional index.

I hope this helps
Cheers
Renger

[Luqman Hakeem]

Hi
I am getting different errors, but anyway, here are my comments:

In your Gams file you read over and over again the same parameters (e.g. FIXC) from different sheets in the same gdx-file. However, this would mean that you would overwrite these values each time you call gdxxrw.
As you have everything hardcoded now, I would proceed as follows:
[-] Check your reading over the first block (there are at the moment dimensionality errors). You can do this easily by slightly adjusting your code and using $exit to stop further compilation:

Code: Select all

$onecho > Pakdata.txt
par=FixC rng=S2018!b120:c184
par=VarC rng=S2018!f120:g184
par=GrsC rng=S2018!j120:k184
par=CapF rng=S2018!n120:o184
par=FC rng=S2018!b2:e56
par=VC rng=S2018!i2:l56
par=GC rng=S2018!p2:s56
par=CF rng=S2018!w2:z56
par=FuelExi rng=S2018!w60:z114
par=HRExi rng=S2018!p60:s114
par=CO2 rng=S2018!b60:e114
par=FCost rng=S2018!b188:d228
par=VCost rng=S2018!h188:j228
par=GCap rng=S2018!n188:p228
par=CFac rng=S2018!t188:v228
par=CapC rng=S2018!b279:d319
par=CO2new rng=S2018!b233:d273
par=HRNew rng=S2018!n233:p273
par=FuelNew rng=S2018!t233:v273
$offecho

$CALL GDXXRW.EXE Pakdata.xlsx o=2018.gdx @Pakdata.txt
$GDXIN Pakdata.gdx
$LOAD FixC, VarC, GrsC, CapF, FC, VC, GC, CF, FuelExi, HRExi, CO2, FCost, VCost, Gcap, CFac, CapC, CO2new, HRNew, FuelNew
$GDXIN
$exit

[-] If that is error-free, you can add delete the the lines from $LOAD to $exit and add after each block the following lines

Code: Select all

$offecho
$CALL GDXXRW.EXE Pakdata.xlsx o=2018.gdx @Pakdata.txt

* And for the next block the same
..
par=FuelNew rng=S2019!t233:v273
$offecho
$CALL GDXXRW.EXE Pakdata.xlsx o=2019.gdx @Pakdata.txt

... etc.

Note the different name for the gdx file.
[-] Run all this code in a separate file (e.g. readdata.gms).
[-] use the gdxmerge command to merge all these files which will result in your parameters with the additional index of the year (in fact the name of your gdx file). e.g. FixC(year, ....).
[-] Now read the newly built parameters into your model file and adjust code for the additional index.

I hope this helps
Cheers
Renger

Hi
Thank you Renger for your help.
I have prepared readata.gms and merged.gdx files but i am facing issues of dimenions (Error: Load dimensions are different) when i call parameter from merged.gdx file.
Files are attached for your kind review.

Pak2.xlsx

(1.54 MiB) Downloaded 145 times

readdata.gms

(28.03 KiB) Downloaded 153 times

Updatedfile1.gms

(34.62 KiB) Downloaded 148 times

[Luqman Hakeem]

Hi Renger
I have found my errors regarding the different load dimensions. But i am getting infeasible results now. Can you suggest me something.
Corrected files are attached below.

modelfile.gms

(8.13 KiB) Downloaded 182 times

Pak3.xlsx

readdata2.gms

[Renger]

Hi

Sorry, I can't be of any help here. Perhaps you could search for ideas in this forum.

Renger

PS. If you add the option CheckDate at the end of your gdxxrw command, it checks if the excel file changed, if not, it doesn't read the data and saves you time.

[Luqman Hakeem]

Hi

Sorry, I can't be of any help here. Perhaps you could search for ideas in this forum.

Renger

PS. If you add the option CheckDate at the end of your gdxxrw command, it checks if the excel file changed, if not, it doesn't read the data and saves you time.

Thank you Renger. It means a lot to me.