Reading text or Excel files in execution phase

GAMS
1

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?

2

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.

[...]
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

3

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?

4

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_GamsCall.html#UG_GamsCall_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

5

Hi

Thanks for the reply.

I am literally executing the example:

$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).

6

Hi,

in an earlier post I already suggested to do

[...]
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

7

Dear Fred

Thanks again. The following worked for me:

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 ;