Table of Contents
This tutorial describes how to exchange data between GAMS and text files (usually in ASCII format). GAMS itself offers various commands that can be utilized to read and write text files. As the reading and writing capabilities provided by these commands are limited, the GAMS Connect CSVReader and CSVWriter agents allow to exchange data with more complex text files. In addition, the GAMS Connect facility offers various agents to manipulate data after reading or before writing text files allowing the user to clean up, filter, etc. data directly in GAMS.
GAMS Commands
This section shows how to read and write text files using simple GAMS commands, e.g. $include, $ondelim/$offdelim, $echo, and the put writing facility.
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 an example, 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 ;
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. For files with other separators the Connect CSVReader agent should be used to import data. Refer to the GAMS Connect tutorial for reading text files.
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. For example, 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
/;
For more complex data that can not be inserted into the GAMS compiler input stream, the CSVReader can be used. Checkout the GAMS Connect tutorial for reading text files.
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.
GAMS Connect
This section shows how to read and write text files using Connect agents CSVReader and CSVWriter.
Reading text files
Consider a code snippet from model connect03 (a modified version of the trnsport model) from the GAMS Data Utilities Library:
$onEcho > distance.csv
i,new-york,chicago,topeka
seattle,2.5,1.7,1.8
san-diego,2.5,1.8,1.4
$offEcho
$onEcho > capacity.csv
i,capacity
seattle,350
san-diego,600
$offEcho
$onEcho > demand.csv
j,demand
new-york,325
chicago,300
topeka,275
$offEcho
Set i 'canning plants', j 'markets';
Parameter d(i<,j<) 'distance in thousands of miles'
a(i) 'capacity of plant i in cases'
b(j) 'demand at market j in cases';
$onEmbeddedCode Connect:
- CSVReader:
file: distance.csv
name: d
indexColumns: 1
valueColumns: "2:lastCol"
- CSVReader:
file: capacity.csv
name: a
indexColumns: 1
valueColumns: 2
- CSVReader:
file: demand.csv
name: b
indexColumns: 1
valueColumns: 2
- GAMSWriter:
symbols: all
$offEmbeddedCode
[...]
The example uses compile-time embedded Connect code to read data for parameters from CSV files using the CSVReader agent. The agent creates the parameters d, a and b in the Connect database. All parameters can be directly written to the GAMS database using the GAMSWriter agent. The GAMSWriter makes the parameters available outside of the embedded Connect code. Note that the sets i and j are defined implicitly through parameter d. Embedded Connect code is also available at execution time, see the example in the following section Writing text files. The CSVReader offers many options, such as decimalSeparator and fieldSeparator, that allow to read various CSV file formats. The agent also allows to manipulate data while reading using indexSubstitutions and valueSubstitutions.
Writing text files
Consider a code snippet from model connect03 (a modified version of the trnsport model) from the GAMS Data Utilities Library:
[...]
Model transport / all /;
solve transport using lp minimizing z;
EmbeddedCode Connect:
- GAMSReader:
symbols:
- name: x
- Projection:
name: x.l(i,j)
newName: x_level(i,j)
- CSVWriter:
file: shipment_quantities.csv
name: x_level
unstack: True
endEmbeddedCode
Here, execution-time embedded Connect code is used to export model results to a CSV file. The Connect agent GAMSReader imports variable x from the GAMS database into the Connect database. The Projection agent is used to create a new parameter that contains only the level values of variable x. The final parameter x_level is then written to a CSV file using the CSVWriter agent. The file shipment_quantities.csv has the following content:
i,new-york,chicago,topeka seattle,50.0,300.0,0.0 san-diego,275.0,0.0,275.0
The CSVWriter provides many options, such as decimalSeparator and fieldSeparator, that allow to write tailored CSV files. The example above utilizes the unstack option to use the last dimension of x_level as the header row.
GAMS Tools
This section shows how to read and write text files using GAMS tool CSVRead and executable tool GDXDUMP, respectively.
Reading text files
Consider a code snippet from model csv2gdx2 (a modified version of the trnsport model) from the GAMS Data Utilities Library:
$onEcho > distance.csv
,new-york,chicago,topeka
seattle,2.5,1.7,1.8
san-diego,2.5,1.8,1.4
$offEcho
$onEcho > capacity.csv
,capacity
seattle,350
san-diego,600
$offEcho
$onEcho > demand.csv
,demand
new-york,325
chicago,300
topeka,275
$offEcho
Set i 'canning plants', j 'markets';
Parameter d(i,j) 'distance in thousands of miles';
$callTool csvread distance.csv id=d dimids=i,j index=1 values=2..lastCol useHeader=y trace=0
$ifE errorLevel<>0 $abort Problems reading distance.csv!
Parameter
a(i) 'capacity of plant i in cases'
b(j) 'demand at market j in cases';
$callTool csvread capacity.csv id=a index=1 values=2 useHeader=y trace=0
$ifE errorLevel<>0 $abort Problems reading capacity.csv!
$callTool csvread demand.csv id=b index=1 values=2 useHeader=y trace=0
$ifE errorLevel<>0 $abort Problems reading demand.csv!
[...]
The example utilizes the tool CSVRead during compile-time to read data for parameters from CSV files. The tool directly writes the CSV data to the parameters d, a and b in the GAMS database. Note that the sets i and j are defined through parameter d using the option dimids. CSVRead is also available at execution-time through the use of ExecuteTool.
Since the tool uses CSVReader in the background to read the data, it offers many options such as decimalSep and fieldSep, that allow to read various CSV file formats. Refer to the usage section of the tool for more details on different options. For more complex data manipulation, use the GAMS Connect agent CSVReader as it allows index substitutions and value substitutions while reading the CSV data.
Writing text files
Consider a code snippet from model GDXDUMP example 1 from the GAMS Data Utilities Library:
$call gams trnsport gdx=trnsport lo=%GAMS.lo%
* Write the header to a CSV file
$echo '"canning plants","markets","shipment quantities in cases"' > quotedHeader.csv
* Append the data to the already existing file. One must prevent GDXDump to
* add the default header by enabling the option noHeader.
$call gdxdump trnsport.gdx symb=x format=csv noHeader >> quotedHeader.csv
$ifE errorLevel<>0 $abort Error writing to quoteHeader.csv using GDXDump!
After solving the trnsport model and producing the gdx file trnsport, a CSV file with headers gets created. The tool gdxdump is then used with option format=csv to append the data to the already existing CSV file that has headers in it. The tool has other options which can be utilized to control the content of the resulting CSV file. The file quotedHeader.csv has the following content:
"canning plants","markets","shipment quantities in cases" "seattle","new-york",50 "seattle","chicago",300 "seattle","topeka",0 "san-diego","new-york",275 "san-diego","chicago",0 "san-diego","topeka",275
For writing more complex CSV files, the GAMS Connect agents CSVWriter and Projection can be used. Checkout the example on writing text files using CSVWriter.