excel to gams Topic is solved

Problems with syntax of GAMS
garvisgarg
User
User
Posts: 5
Joined: 3 months ago

excel to gams

Postby garvisgarg » 1 month ago

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: 1 month ago

Re: excel to gams  Topic is solved

Postby Luby » 1 month ago

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: 1
Joined: 1 week ago

Re: excel to gams

Postby Dbroz » 1 week ago

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: 48
Joined: 11 months ago

Re: excel to gams

Postby bussieck » 1 week ago

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: 5 days ago

Re: excel to gams

Postby Newcomer » 5 days ago

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: 48
Joined: 11 months ago

Re: excel to gams

Postby bussieck » 5 days ago

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: 5 days ago

Re: excel to gams

Postby Newcomer » 5 days ago

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

User avatar
bussieck
Moderator
Moderator
Posts: 48
Joined: 11 months ago

Re: excel to gams

Postby bussieck » 5 days ago

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: 5 days ago

Re: excel to gams

Postby Newcomer » 2 days ago

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


Return to “Syntax”

Who is online

Users browsing this forum: No registered users and 1 guest