Data Exchange with Text Files

This tutorial describes some ways on how to exchange data between GAMS and text files (usually in ASCII format).

# Reading Text Files During Compilation

GAMS can read arbitrary text files during compile time by inserting them into the compiler input stream. The file content is then assumed to be GAMS code. Thus, including a text file within a data statement (see also Data Entry: Parameters, Scalars and Tables) allows for an easy way to include data from a text file, as long as the syntax in the text file can be understood by the GAMS compiler. This way, model specification and data input can be separated into different files.

The $include compile-time command is used to instruct the GAMS compiler to include the context of a different file at the current position of the input stream. As a result, the GAMS code behaves as if the $include statement has been replaced by the content of the file to be included. This can be very handy when including data from a separate text file. For instance, when data for a table is actually coming from another environment, one could replace the TABLE statement by an include statement. A GAMS table is in fact very well suited for a human being to be read or written, but it is rather awkward for programs to generate (e.g., the numbers have to be approximately below the corresponding headers). Therefore, often parameters are used and long series of assignment statements are generated. For instance, consider the following fragment from model [TRNSPORT]:

Table d(i,j)  'distance in thousands of miles'
new-york       chicago      topeka
seattle          2.5           1.7          1.8
san-diego        2.5           1.8          1.4  ;


When the data for this table is coming from a program it is more convenient to say in the main program:

Parameter d(i,j) 'distance in thousands of miles';
$include data.inc display d;  and to have the include file data.inc contain the machine generated statements: d("seattle","new-york") = 2.5; d("san-diego","new-york") = 2.5; d("seattle","chicago") = 1.7; d("san-diego","chicago") = 1.8; d("seattle","topeka") = 1.8; d("san-diego","topeka") = 1.4;  In fact, GAMS can deal quite comfortably with a large number of such assignment statements. Note, that since the included file is considered as part of the GAMS input stream, it is also echoed in the listing file. When including large text files with data statements, echoing these files in the listing file can be undesired. To suppress echoing to the listing file, the $include statement can be surrounded by $offlisting and$onlisting instructions:

parameter d(i,j) 'distance in thousands of miles';
$offlisting$include data.inc
$onlisting display d;  In the listing file, line numbers are skipped where the$offlisting is in effect.

In some cases it may be more convenient to use the initialization syntax of parameters. That is, the main GAMS file could contain the fragment:

parameter d(i,j) 'distance in thousands of miles' /
$include data2.inc /; display d;  and the data file contains the following records: seattle .new-york 2.5 san-diego.new-york 2.5 seattle .chicago 1.7 san-diego.chicago 1.8 seattle .topeka 1.8 san-diego.topeka 1.4  This approach is preferable for large data sets as it is more efficient for GAMS. Note Tables and parameters are handled exactly the same way by GAMS internally. The only difference is in the specification of data. When using a table statement, data can also be specified in CSV (Comma-separated values) format if the$ondelim command has been issued. This format can, for instance, be generated by spreadsheet programs.

As example, consider again the following fragment from model [TRNSPORT]:

Table d(i,j)  'distance in thousands of miles'
new-york       chicago      topeka
seattle          2.5           1.7          1.8
san-diego        2.5           1.8          1.4  ;


A file data.csv that specifies the data of this table in CSV format would have the content

,new-york,chicago,topeka
seattle,2.5,1.7,1.8
san-diego,2.5,1.8,1.4


Notice the empty first element in the first line, which corresponds to the top-left blank in the above table.

This file can now be included directly into GAMS by using the $ondelim and$offdelim commands:

Table d(i,j) 'distance in thousands of miles' ;
$ondelim$include data.csv
$offdelim  Attention The$ondelim command only enables the use of commas as a separation symbol. The use of a different separation character is not supported by GAMS. This can become an issue if, for example, a CSV file has been generated with a language setting where commas are used as decimal point and semicolons are used for separating entries in the CSV file. In such a situation, data.csv would have the content
,new-york;chicago;topeka
seattle;2,5;1,7;1,8
san-diego;2,5;1,8;1,4

Therefore, such text files need to be preprocessed before read into GAMS. The following code uses the POSIX tool tr to (simultaneously) replace commas by dots and semicolons by commas:
$call "tr ,; ., < data.csv > data2.csv" Table d(i,j) 'distance in thousands of miles' ;$ondelim
$include data2.csv$offdelim


CSV files can also be used to input higher dimensional data into GAMS. For instance, consider the 3 dimensional table yieldtl from model [TURKEY]:

Table yieldtl(l,cl,ty)  'livestock yield time series (kg per head)'

1974      1975      1976      1977      1978      1979

sheep.meat             10.60     11.42     10.60      9.38      8.97      6.93
sheep.milk             23.7      24.1      24.2      24.2      24.0      23.9
sheep.wool              1.3       1.3       1.3       1.3       1.3       1.3
sheep.hide              0.5       0.6       0.6       0.5       0.6       0.4
goat.meat               6.39      7.31      8.68      7.31      6.39      6.85
goat.milk              37.7      38.1      38.2      38.2      38.3      37.8
goat.wool               0.6       0.6       0.6       0.6       0.6       0.6
goat.hide               0.2       0.3       0.3       0.3       0.2       0.3
angora.meat             1.77      1.77      2.66      2.21      1.77      1.77
angora.milk            14.9      15.2      14.8      15.2      14.8      15.0
angora.wool             1.6       1.6       1.6       1.6       1.6       1.4
angora.hide             0.1       0.1       0.1       0.1       0.1       0.1
cattle.meat            24.59     25.12     21.42     23.00     18.25     25.12
cattle.milk           210.0     208.1     219.8     213.8     214.8     217.5
cattle.hide             3.3       3.4       2.9       3.0       2.6       3.3
buffalo.meat           43.73     45.42     40.61     37.21     32.20     32.68
buffalo.milk          267.1     269.2     263.8     219.6     275.5     285.1
buffalo.hide            4.1       3.4       3.0       2.4       2.5       2.6
poultry.meat            2.24      2.24      2.24      2.24      2.24      2.24
poultry.egg            62.4      62.2      64.2      78.3      76.4      73.3
;


When the data for this parameter is prepared by another application (such as a relational database), it may be more convenient to write it out in a comma-separated value form, e.g., a file data.csv could have the content

"sheep","meat","1974",10.60
"sheep","meat","1975",11.42
"sheep","meat","1976",10.60
"sheep","meat","1977",9.38
"sheep","meat","1978",8.97
"sheep","meat","1979",6.93
"sheep","milk","1974",23.70
"sheep","milk","1975",24.10
"sheep","milk","1976",24.20
"sheep","milk","1977",24.20
...


Including such formatted data into a GAMS model is possible by using $ondelim for Parameter Data for Higher Dimensions. Thus, the GAMS code would be Parameter yieldtl(l,cl,ty) 'livestock yield time series (kg per head)' /$ondelim
$include data.csv$offdelim
/;


# Writing Text During Compilation

The commands $echo,$onecho, and $offecho send text to named files during compilation.$echo sends one line and is invoked using the syntax:

$echo 'text to be sent' > externalfile  or $echo 'text to be sent' >> externalfile


The use of ''>'' generates a new file, while ''>>'' appends to an existing file.

For multi-line messages, the commands $onecho and$offecho can be used, e.g.,

$onecho > externalfile line 1 of text to be sent line 2 of text to be sent ... last line of text to be sent$offecho


A typical example for the usage of these commands is the generation of a solver options file.

Additionally, the $log command can be used to send messages to the log file during compilation. # Writing Text Files During Execution The put writing facility allows customized text output. This is a fairly complex but powerful and flexible report writing facility. Assume that the following GAMS code is added to the end of model [TRNSPORT]. It instructs GAMS to write the model and the solve status together with levels of the decision variables to a file results.txt: File results / results.txt /; put results; put "Model status", transport.modelstat /; put "Solver status", transport.solvestat /; put "Objective", z.l /; put "Shipments" /; loop((i,j), put i.tl, j.tl, x.l(i,j) / ); putclose;  First, a file object results is declared by using the File statement. The data of the file statement specifies the name of the file (results.txt). Next, line put results; instructs GAMS that for the following put statements, the file results should be used. In the following, the model status and the solve status are written, together with some descriptive text. These model attributes are set by a solve statement. The character '/' instructs GAMS to add a linebreak (newline character) to the results file. The next thing to write out are some variable values. Here, first the level value of variable z is written, followed by a loop that writes for each element of sets i and j the name of the elements (accessed via the .tl attribute) and the level value of x(i,j). Finally, the putclose; statement instructs GAMS to close the current file. This will ensure that possibly cached data is flushed to the file. The output will look like: Model status 1.00 Solver status 1.00 Objective 153.67 Shipments seattle new-york 50.00 seattle chicago 300.00 seattle topeka 0.00 san-diego new-york 275.00 san-diego chicago 0.00 san-diego topeka 275.00  This form can be hard to read by other applications, e.g., because some space characters are to be considered as separators, while others are really part of a string (e.g. "Model status"). However, by using the print control option .pc of the put writing facility, comma-separated value files can be written. That is, by adding the line results.pc = 5;, i.e., File results / results.txt /; results.pc = 5; put results; put "Model status", transport.modelstat /; put "Solver status", transport.solvestat /; put "Objective", z.l /; put "Shipments" /; loop((i,j), put i.tl, j.tl, x.l(i,j) / ); putclose;  one obtains the following output: "Model status",1.00 "Solver status",1.00 "Objective",153.67 "Shipments" "seattle","new-york",50.00 "seattle","chicago",300.00 "seattle","topeka",0.00 "san-diego","new-york",275.00 "san-diego","chicago",0.00 "san-diego","topeka",275.00  If several parameters of the same dimension should be written to a file in a customized format, the put statements can become rather repetitive. For example, the level and marginal values of variable x and the parameters c and d from model [TRNSPORT] should be written. This can be coded easily as: file results / results.txt /; results.pc = 5; put results; loop((i,j), put "distance", i.tl, j.tl, d(i,j) / ); loop((i,j), put "cost", i.tl, j.tl, c(i,j) / ); loop((i,j), put "levels", i.tl, j.tl, x.l(i,j) / ); loop((i,j), put "marginals", i.tl, j.tl, x.m(i,j) / ); putclose;  A separation of the code that writes the data (loop and put statements) from the data that is written (descriptive text, set names, etc.) can be achieved by using$batinclude. This command works similar to the $include statement (see also Section Reading Text Files During Compilation above), but allows for additional arguments (separated by blanks). While including the file, markers %1, %2, etc., are replaced by the value of the 1st, 2nd, etc., argument. By using$batinclude, the above example could be simplified to

file results / results.txt /;
results.pc = 5;
put results;
$batinclude put.inc distance i j d$batinclude put.inc cost     i j c
$batinclude put.inc level i j x.l$batinclude put.inc marginal i j x.m
putclose;


where the file put.inc contain the actual loop and put statements:

loop((%2,%3), put "%1", %2.tl, %3.tl, %4(%2,%3) / );


Finally, the The Put_Utility Statement is referred, which is loosely connected to the put statement and allows the dynamic generation of file names, etc.