Page 1 of 1

excel to gams

Posted: Mon Jul 24, 2017 6:25 am
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

Re: excel to gams  Topic is solved

Posted: Wed Jul 26, 2017 12:47 pm
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
;

Re: excel to gams

Posted: Sat Sep 09, 2017 8:14 pm
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

Re: excel to gams

Posted: Mon Sep 11, 2017 9:35 am
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

Re: excel to gams

Posted: Fri Sep 15, 2017 11:58 am
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

Re: excel to gams

Posted: Fri Sep 15, 2017 12:25 pm
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

Re: excel to gams

Posted: Fri Sep 15, 2017 1:52 pm
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 2 times

Re: excel to gams

Posted: Fri Sep 15, 2017 3:58 pm
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

Re: excel to gams

Posted: Mon Sep 18, 2017 2:02 pm
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