Page 1 of 1

Reading text or Excel files in execution phase

Posted: Fri Aug 24, 2018 8:56 am
by LaurentFranckx
Dear all

A few days ago I had posted a question under the title "Data exchange between R and GAMS" - I realize now that this heading may have been too restrictive, so I would like to broaden the question:

In my model, GAMS needs to be able to read data in the execution of a loop (because the values of these data are changed by an external application in each iteration).

I had considered using csv files for the exchange of data, but I understand you can not read text files during execution:
https://www.gams.com/latest/docs/UG_Dat ... ASCII.html

A similar issue arises if I would use Excel files for the data exchange (using this solution requires the declaration of a set during the loop, which is not allowed)
https://www.gams.com/latest/docs/T_XLS2GMS.html

Are there any alternative solutions?

Re: Reading text or Excel files in execution phase

Posted: Mon Aug 27, 2018 3:28 pm
by Fred
Hi,
There are tools to convert data from csv or Excel to gdx:
https://www.gams.com/latest/docs/T_CSV2GDX.html
https://www.gams.com/latest/docs/T_GDXXRW.html

You can run these tools during execution time and load data from gdx at execution time, e.g.

Code: Select all

[...]
execute 'CSV2GDX data.csv output=data.gdx...';
execute_load 'data.gdx', ...;
[...]
Please note that loading data at at execution time works subject to certain restrictions. If you have for example used a set in the domain of another symbol, that set cannot be changed (i.e. loaded) at execution time. Also, no new labels can be added during execution time.
It may help, if you share a small executable example that illustrates your problem.

Best,
Fred

Re: Reading text or Excel files in execution phase

Posted: Mon Aug 27, 2018 4:23 pm
by LaurentFranckx
Dear Fred

Thank you for the reply.

I am currently trying to implement the example with csv2gdx but I always get the error message "GDXIN failed".

I have literally copied the examples from https://www.gams.com/latest/docs/T_CSV2GDX.html
The csv file has been created correctly and has the correct extension.

Do I need to install add-ons before I can use csv2gdx?

Re: Reading text or Excel files in execution phase

Posted: Tue Aug 28, 2018 8:47 am
by Fred
Hi,

Is seems that you made a common mistake and confused compilation and execution time commands. GAMS is a two pass system with a compliation phase (1) and an execution phase (2). You can read more about that here: https://www.gams.com/latest/docs/UG_Gam ... ll_TwoPass

While "execute '...';" is carried out during execution time, dollar commands like "$GDXIN" are carried out during compilation. Hence, it seems that you try to read the gdx file before it is actually created.

No need to install any add-ons, csv2gdx comes with your GAMS system. Maybe it makes sense to execute the tool as a standalone via the comand line to see if it works as expected before you embed it in a bigger application.
Still, you are also welcome to share a (failing) example that illustrates the issue.

Best,
Fred

Re: Reading text or Excel files in execution phase

Posted: Tue Aug 28, 2018 8:54 am
by LaurentFranckx
Hi

Thanks for the reply.

I am literally executing the example:

Code: Select all

$call csv2gdx data id=A Index=(1,2,6) Values=(3..5) UseHeader=Y StoreZero=Y
set color(*), number(*);
$gdxin data.gdx
$load  color=dim1
$loadm color=dim2
$loadm color=dim3
$load number=dim4
parameter A(color, color, color, number);
$load A
$gdxin
display color, number, A;
Where the data file is:

one,two,three,four,five,six
red,red,1.1,2.2,3.3,red
red,red,4.4,5.5,Eps,green
red,green,7.7,8.8,9.9,blue
blue,blue,10,0,NA,purple

This being said, if GDXIN can only be called during the compilation phase, it seems to be that it doesn't solve my problem: that I need GAMS to read a csv or Excell file whose contents are modified by an R script in a loop (thus, during the execution phase).

Re: Reading text or Excel files in execution phase

Posted: Tue Aug 28, 2018 11:17 am
by Fred
Hi,

in an earlier post I already suggested to do

Code: Select all

[...]
execute 'CSV2GDX data.csv output=data.gdx...';
execute_load 'data.gdx', ...;
[...]
"execute" is the execution time counterpart to "$call".
"execute_load" is the execution time counterpart to "$gdxin" and "$load".

Cheers,
Fred

Re: Reading text or Excel files in execution phase

Posted: Tue Aug 28, 2018 2:04 pm
by LaurentFranckx
Dear Fred

Thanks again. The following worked for me:

Code: Select all

set color /red,green , blue ,purple/,
   number /three, four, five/;
parameter A(color, color, color, number);
execute "C:\GAMS\win64\25.0\csv2gdx C:\Usr\GAMSvsRviaExcel\data.csv id=A Index=(1,2,6) Values=(3..5) UseHeader=Y StoreZero=Y"
execute_load 'C:\Usr\GAMSvsRviaExcel\data.gdx', A;
display  A ;