excel to gams

GAMS
1

Hello,
I want to retrieve my data from excel to gams by following this code.


set t /1*48/;
parameter ac(t)
$call gdxxrw.exe acforecasted.xls par=ac rng=sheet1!a1
$gdxin acforecasted.gdx
$load ac
$gdxin
display ac

but here error 492 and 141 are occurred then please tell me what is the mistake in this code.

Thanks
Garvis Garg

2

Hi,
you can try with this code, for example:

table NAME(i,j)
$call =xls2gms r=sheet1!a1:b2 i=NAME_of_excel.xlsx o=name_what_ever_you_want.inc
$include name_what_ever_you_want.inc
;

3

Hello group,
I would like export from GAMS to EXCEL but with open Excel (no close).
Does anybody knows how it’s done?
But this code can not be written if Excel is open.

*//////OUTPUT CODE/////////
display IngBrutoTOTALRema.l, CosVarTOTALAse.l
execute_unload “SalidaAserradero2.gdx” IngBrutoTOTALRema.l CosVarTOTALAse.l
execute ‘gdxxrw.exe SalidaAserradero2.gdx var=IngBrutoTOTALRema.l rng=Economica!b2 var=CosVarTOTALAse.l rng=Economica!b3’

Best regards

4

Hi,

Writing to an open workbook is possible but very much discouraged. You need to share you workbook (see https://support.office.com/en-us/article/About-the-shared-workbook-feature-49b833c0-873b-48d8-8bf2-c1c59a628534). Problem is that you only see the changes after “saving” the open workbook. Moreover writing throughput is a fraction of what you get when you write to a closed workbook. If you need to write small amounts of data this method is okay, but not for large amounts of data. This “feature” of Excel is independent of GAMS, everyone has the same problem. I personally like to write to an independent workbook and “link” the written data to my current workbook (see https://support.office.com/en-us/article/Connect-to-another-workbook-3a557ddb-70f3-400b-b48c-0c86ce62b4f5 for details).

-Michael

5

Hey,

I have a similar problem. I used the following codes to create a gdx file and then to import the file into GAMS:

set
i column lables /i1i6/
j row labels /j1j6/;

Parameter d(i,j) distance in km;

$call GDXXRW.exe Daten-TSP.xlsx trace=3 par=d rng=Tabelle1!A2 rdim=1 cdim=1
$GDXIN Daten-TSP.gdx
$LOAD d
$GDXIN

display d;

The first part (creating a gdx file) works. I also receive no error message when loading the gdx file in GAMS. But the parameters are not displayed. Where the parameters should be displayed, the programm says instead:

23 PARAMETER d distance in km

( ALL 0.000 )

Can anyone tell me what is wrong?
I am quite sure that the code is correct. Do I have to modify any settings in the programm?

Best regards

6

You should check the return code after a $call and you should load data with $loadDC to make sure that you don’t leave record behind.

$call GDXXRW.exe Daten-TSP.xlsx trace=3 par=d rng=Tabelle1!A2 rdim=1 cdim=1
$if errorlevel 1 $abort problems with gdxxrw
$GDXIN Daten-TSP.gdx
$LOADDC d

Hope this helps,
-Michael

7

Thanks for your quick response. Unfortunately, now I get the Error 649 - Domain violation.

I double checked the domain and I have no Idea what is wrong here.
Daten-TSP.xlsx (9.78 KB)

8

If you look at the content of the GDX file it will be pretty obvious that the read did not succeed as you planned:

C:\Users\Michael\Downloads>gdxdump Daten-TSP.gdx
$onempty

Parameter d(*,*) /
'j2'.'0' 8.9,
'j2'.'2.2' 9.3,
'j2'.'1.8' 10.5,
'j2'.'10.1' 11.4,
'j2'.'0.6' 8.6,
'j3'.'0' 2.3,
...

Problem is you start to read in A2 while you have good labels already in row 1, so just change the range to rng=Tabelle1!A1, not A2 and everything will be fine.

Hope this helps,
-Michael

9

Oh I am sorry, I forgot to adjust the table before sending it to you. I already changed A2 to A1 in my program.
I just tried to solve my problem on a different computer and it worked after I changed trace=3 to trace=4.
I guess it is a problem with our server.
But still thank you very much for your help.

Kind regards Christina

10

I have similar issues with gdx.

First the codes below worked well but changed all the values for set tyyz imported to 1.000, what went wrong?

$CALL GDXXRW.EXE Nigeria_data.xlsx par=SAM rng=sheet1!c3…ca79 par=tyyz rng=sheet2!a1…k10

  • Open the GDX, import the SAM parameter, close the GDX file
    $GDXIN Nigeria_data.gdx
    $LOADDC SAM, tyyz
    $GDXIN

Second, the codes below is meant to import a single column of data but it won’t work please help!

$CALL GDXXRW.EXE Nigeria_data.xlsx par=tykz rng=sheet3!A1:B19

  • Open the GDX, import the SAM parameter, close the GDX file
    $GDXIN Nigeria_data.gdx
    $LOADDC tykz
    $GDXIN
11

The codes below turns all my data into “1.000”. Please help.

$CALL GDXXRW.EXE Nigeria_data.xlsx par=tyyz rng=sheet2!a1…k10
$GDXIN Nigeria_data.gdx
$LOADDC tyyz
$GDXIN
;

12

Hello group.
I can print date and hour of GAMS execution in TXT. But I need print date and hour of GAMS execution in excel.
Does anyone know a syntax to print date and hour ejecution in excel?
Best regards

13

GAMS and Excel (and many other program work with the Julian date or serial date, a single number represents date and time). Just write this number to Excel and format as date:

scalar x; x = jnow;
execute_unload 'time', x;
execute 'gdxxrw time.gdx o=time.xlsx par=x rng=a1';

t2.JPG
t1.JPG
t3.JPG
There are function to go from serial dates to regular calendar dates and vice versa: https://www.gams.com/latest/docs/UG_Parameters.html#UG_Parameters_TimeAndCalendarFunctions

-Michael