hello, I'm trying to pass several cells of different sheets of an excel to gams using gdx, and I get strange errors, this I have until now:
Parameter DH1 (K0, J01)
$ call GDXXRW Alt_Tables.xlsx par = DH1 rng = H1(K0,J01)! A1: B7 output = DH1.gdx
$ GDXIN DH1.gdx
$ LOAD DH1
$ GDXIN
Parameter DH2 (K0, J02)
$ call GDXXRW Alt_Tables.xlsx par = DH2 rng = H2(K0,J02)! A1: AB7 output = DH2.gdx
$ GDXIN DH2.gdx
$ LOAD DH2
$ GDXIN
Parameter DH3 (K0, ND01)
$ call GDXXRW Alt_Tables.xlsx par = DH2 rng = H3(K0,ND01)! A1: I8 output = DH3.gdx
$ GDXIN DH3.gdx
$ LOAD DH3
$ GDXIN
At the beginning it appears that the sheet does not exist, then I write again the name of the sheets in excel and the program recognizes them, this is very rare. The other strange thing is that he does not run me because he tells me that the DH3 has 'symbol not in gdx' since I can open DH3.gdx Does anyone know what the problem is?
Thank you!
Help with gams, gdx and xlsx
Re: Help with gams, gdx and xlsx
- Having characters outside [a-z][A-Z][0-9] in sheet names might confuse the command line of gdxxrw
- Always check the return code when you do a $call with $if errorlevel 1 $abort "problems with the $call command"
- You might want to post you spreadsheet so one of us can try to reproduce
Re: Help with gams, gdx and xlsx
Hi,my sheets are:
H1 (K0, J01):
13VZ
13VZ
0_1M1 0
0_1M2 31
0_2NE 613
0_2NW 613
0_2SE 613
0_2SW 613
H2(K0,J02):
11QR 13C 11A 11D 11SE 11PH 11PA 11L 13RP 13P 11EC 12Q 11V 11G 11MC 11ES 11T 12SM 12SMAPA 14SMAPA 12ENEA 11VE 12SB 12CV 12LA 12IQ 14SI
0_1M1 173 176 307 223 130 0 145 2095 28 0 2055 0 0 0 88 104 77 94 0 0 0 328 0 0 0 0 0
0_1M2 217 220 351 267 174 0 189 2139 72 0 2099 0 32 28 132 148 121 138 0 0 0 372 0 0 0 0 0
0_2NE 799 802 933 849 756 560 771 2721 654 572 2681 333 614 610 714 730 703 720 363 339 334 954 430 328 353 341 280
0_2NW 799 802 933 849 756 560 771 2721 654 572 2681 333 614 610 714 730 703 720 363 339 334 954 430 328 353 341 280
0_2SE 799 802 933 849 756 560 771 2721 654 572 2681 333 614 610 714 730 703 720 363 339 334 954 430 328 353 341 280
0_2SW 799 802 933 849 756 560 771 2721 654 572 2681 333 614 610 714 730 703 720 363 339 334 954 430 328 353 341 280
H3(K0,ND01)
13_11NE1 13_11NE2 13_12NW1 13_12NW2 13_13SE1 13_13SE2 13_14SW1 13_14SW2
0_1M1 0 0 0 0 0 0 0 0
0_1M2 0 0 0 0 0 38 0 0
0_2NE 568 494 386 355 450 620 430 358
0_2NW 568 494 386 355 450 620 430 358
0_2SE 568 494 386 355 450 620 430 358
0_2SW 568 494 386 355 450 620 430 358
Although the problem shows me in H3 where there are no cells AZ
H1 (K0, J01):
13VZ
13VZ
0_1M1 0
0_1M2 31
0_2NE 613
0_2NW 613
0_2SE 613
0_2SW 613
H2(K0,J02):
11QR 13C 11A 11D 11SE 11PH 11PA 11L 13RP 13P 11EC 12Q 11V 11G 11MC 11ES 11T 12SM 12SMAPA 14SMAPA 12ENEA 11VE 12SB 12CV 12LA 12IQ 14SI
0_1M1 173 176 307 223 130 0 145 2095 28 0 2055 0 0 0 88 104 77 94 0 0 0 328 0 0 0 0 0
0_1M2 217 220 351 267 174 0 189 2139 72 0 2099 0 32 28 132 148 121 138 0 0 0 372 0 0 0 0 0
0_2NE 799 802 933 849 756 560 771 2721 654 572 2681 333 614 610 714 730 703 720 363 339 334 954 430 328 353 341 280
0_2NW 799 802 933 849 756 560 771 2721 654 572 2681 333 614 610 714 730 703 720 363 339 334 954 430 328 353 341 280
0_2SE 799 802 933 849 756 560 771 2721 654 572 2681 333 614 610 714 730 703 720 363 339 334 954 430 328 353 341 280
0_2SW 799 802 933 849 756 560 771 2721 654 572 2681 333 614 610 714 730 703 720 363 339 334 954 430 328 353 341 280
H3(K0,ND01)
13_11NE1 13_11NE2 13_12NW1 13_12NW2 13_13SE1 13_13SE2 13_14SW1 13_14SW2
0_1M1 0 0 0 0 0 0 0 0
0_1M2 0 0 0 0 0 38 0 0
0_2NE 568 494 386 355 450 620 430 358
0_2NW 568 494 386 355 450 620 430 358
0_2SE 568 494 386 355 450 620 430 358
0_2SW 568 494 386 355 450 620 430 358
Although the problem shows me in H3 where there are no cells AZ
Re: Help with gams, gdx and xlsx
Sorry, here I post the excel, there are many tables of parameters.
- Attachments
-
- Tablas_Alturas.xlsx
- (239.04 KiB) Downloaded 273 times
Re: Help with gams, gdx and xlsx
I have no problems whatsoever reading the data with gdxxrw with the following code:
There were plenty of spaces in the code in your original posting. They will confuse gdxxrw. Moreover, your third call to gdxxrw was saving the results again in DH2 but you try to extract DH3. So I am not sure what you really tried.
-Michael
Code: Select all
$onecho > gdxxrw.in
i=Tablas_Alturas.xlsx
o=data.gdx
par=DH1 rng=H1(K0,J01)!A1:B7
par=DH2 rng=H2(K0,J02)!A1:AB7
par=DH3 rng=H3(K0,ND01)!A1:I8
$offecho
$call gdxxrw @gdxxrw.in
$if errorlevel 1 $abort "problems with gdxxrw"
set K0,J01,J02,ND01;
Parameter DH1(K0,J01), DH2(K0,J02), DH3(K0,ND01)
$gdxin data
$loadm K0<DH1.dim1 K0<DH2.dim1 K0<DH3.dim1
$load J01<DH1.dim2 J02<DH2.dim2 ND01<DH3.dim2
$loadDC DH1 DH2 DH3
display DH1, DH2, DH3;
-Michael
Re: Help with gams, gdx and xlsx
I need to transport some parameters from *.gdx file into excel.
I would like to do it precisely column by column, no transportation of columns into rows.
My code is the following:
The excel output is attached. I was trying to play with trace and filter options to have the year span as column as well - like in .gdx file. So what I need is to have all (six) dimensions as columns with extra (seventh) column containing values of my parameter.
And get the names for the columns as well.
Can you help me to fix this problem?
Best
Irena
I would like to do it precisely column by column, no transportation of columns into rows.
My code is the following:
Code: Select all
execute_unload 'VarToBrugegruppe.gdx', qabg quabg qsabg;
execute 'gdxxrw VarToBrugegruppe.gdx o=TabelA15.xlsx filter=1 trace=1 par=qabg rng=qabg!A2'
And get the names for the columns as well.
Can you help me to fix this problem?
Best
Irena
Re: Help with gams, gdx and xlsx
Hi Jarenka
Why don't you add rdim=5 cdim=1 to your call to read this data?
Otherwise, reformulate your question and attach code and the excel sheet.
Cheers
Renger
PS. This is a new question, so better start always a new topic. If you add a reaction to an existing topic, it will not be shown under the "unanswered topics".
Why don't you add rdim=5 cdim=1 to your call to read this data?
Otherwise, reformulate your question and attach code and the excel sheet.
Cheers
Renger
PS. This is a new question, so better start always a new topic. If you add a reaction to an existing topic, it will not be shown under the "unanswered topics".
____________________________________
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