Help with gams, gdx and xlsx

GAMS
1

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!

2
  • 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

-Michael

3

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
image.png
image.png
image.png

4

Sorry, here I post the excel, there are many tables of parameters.
Tablas_Alturas.xlsx (239 KB)

5

I have no problems whatsoever reading the data with gdxxrw with the following code:

$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;

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

6

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:

execute_unload 'VarToBrugegruppe.gdx', qabg quabg qsabg;
execute 'gdxxrw VarToBrugegruppe.gdx o=TabelA15.xlsx filter=1 trace=1 par=qabg rng=qabg!A2'

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
output.JPG

7

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”.