excel to gams Topic is solved

Problems with syntax of GAMS
garvisgarg
User
User
Posts: 5
Joined: 6 years ago

excel to gams

Post by garvisgarg »

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
Luby
User
User
Posts: 2
Joined: 6 years ago

Re: excel to gams

Post by Luby »

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
;
Dbroz
User
User
Posts: 2
Joined: 6 years ago

Re: excel to gams

Post by Dbroz »

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
User avatar
bussieck
Moderator
Moderator
Posts: 1033
Joined: 7 years ago

Re: excel to gams

Post by bussieck »

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/articl ... c59a628534). 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/articl ... 86ce62b4f5 for details).

-Michael
Newcomer
User
User
Posts: 3
Joined: 6 years ago

Re: excel to gams

Post by Newcomer »

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 /i1*i6/
j row labels /j1*j6/;

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
User avatar
bussieck
Moderator
Moderator
Posts: 1033
Joined: 7 years ago

Re: excel to gams

Post by bussieck »

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
Newcomer
User
User
Posts: 3
Joined: 6 years ago

Re: excel to gams

Post by Newcomer »

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
This is my Excel sheet
(9.78 KiB) Downloaded 409 times
User avatar
bussieck
Moderator
Moderator
Posts: 1033
Joined: 7 years ago

Re: excel to gams

Post by bussieck »

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

Code: Select all

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
Newcomer
User
User
Posts: 3
Joined: 6 years ago

Re: excel to gams

Post by Newcomer »

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
Job
User
User
Posts: 6
Joined: 6 years ago

Re: excel to gams

Post by Job »

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
Post Reply