Data Exchange with Excel

This section gives a brief overview on how to use the GDX facilities in GAMS to read data from Excel and to write data to Excel. For more detailed information please consult the document on GDX Facilities and Tools.

• A sample model to write an Excel file: gms2xls.gms
• A sample model to read from an Excel file: xls2gms.gms

System Requirements: A GAMS system Disitrbution 21.0 or later is required.

GAMS communicates with Excel via GDX (GAMS Data Exchange) files. A GDX file is a file that stores the values of one or more GAMS symbols such as sets, parameters variables and equations. GDX files can be used to prepare data for a GAMS model, present results of a GAMS model, store results of the same model using different parameters etc. A GDX file does not store a model formulation or executable statements.

GDX files are binary files that are portable between different platforms. They are written using the byte ordering native to the hardware platform they are created on, but can be read on a platform using a different byte ordering. In order to write data from GAMS to Excel, the user writes a GDX file and then writes the Excel file from the GDX file: GAMS → GDX → Excel.

This is practically seamless for the user and requires few commands. The process to import data from an Excel file to GAMS is similar: Excel → GDX → GAMS

We will build on the simple transportation model from the GAMS Model library and write the solution x and the marginals of x to an Excel file.

After the solve statement, we unload the data (x.L and x.M) to a GDX file using the execute_unload command:

  execute_unload "results.gdx" x.L x.M

Note that the execute_unload command is executed during the actual execution phase (not during compilation time as $ control options) and creates a GDX file called results.gdx.

Now let us write the data from the GDX file to an Excel file called results.xls. We do this using the GDXXRW utility

  execute 'gdxxrw.exe results.gdx var=x.L'
  execute 'gdxxrw.exe results.gdx var=x.M rng=NewSheet!f1:i4'

For the first call for x.L, there is no range specified and the data is written in cell A1 and beyond in the first available sheet. For the marginals x.M data will be written to cells F1:I4 in the sheet NewSheet. Note that we specified var=x.L and var=x.M. If the user wishes to write parameters to the Excel file, the relevant command is par.

Again, we will use the transportation model and make use of the results.xls file created by the previous model. First we will create the GDX file from the Excel file. We will make use of the GDXXRW utility:

  $CALL GDXXRW.EXE results.xls par=Level rng=A1:D3

Note that since we are using the $CALL command, this occurs during the compilation phase and not during execution time. We specify that the data in the range A1:D3 is read in as a parameter called Level. The resulting GDX file will be called results.gdx

Before we can read in the data, we must define a parameter called Level over the appropriate sets:

  Parameter Level(i,j);
  $GDXIN results.gdx
  $LOAD Level
  $GDXIN

The GDXIN results.gdx command specifies that data will be read in from the appropriate GDX file. We then import data structures values using the $LOAD command. When we are finished, we terminate with $GDXIN.

In the example below, we then fix the level values of the variable x to the parameter Level so that solving results in a trivial fixed model.

GDXXRW is a utility to read and write Excel spreadsheet data. GDXXRW can read multiple ranges in a spreadsheet and write the data to a 'GDX' file, or read from a 'GDX' file, and write the data to different ranges in a spreadsheet. For further information and the documentation, please consult the documentation about the wtools.

In some cases it is convenient to consider tabular data in an Excel spreadsheet as a database table and to import it using the sql2gms tool. Consider the spreadsheet:

This table can be read using an SQL query:

  SELECT year,loc,prod,'sales',sales FROM [profitdata$] UNION SELECT year,loc,prod,'profit',profit FROM [profitdata$]

The table name is equal to the sheet name(profitdata). We can pass the query to the Excel ODBC driver using the tool SQL2GMS as follows:

$ontext

  Test MS EXCEL access through ODBC

$offtext

set y 'years'   /1997,1998/;
set c 'city'    /la,nyc,sfo,was/;
set p 'product' /hardware,software/;
set k 'key'     /sales,profit/;

$onecho > excelcmd.txt
c=DRIVER=Microsoft Excel Driver (*.xls);dbq=%system.fp%profit.xls;
q=SELECT year,loc,prod,'sales',sales FROM [profitdata$] UNION SELECT year,loc,prod,'profit',profit FROM [profitdata$]
x=fromexcel.gdx
$offecho
$call =sql2gms @excelcmd.txt
parameter d(y,c,p,k) 
$gdxin excel.gdx
$load d=p
display d;

and the DISPLAY results will be:

---     21 PARAMETER d  FROM SQL2GMS

INDEX 1 = 1997

                   sales      profit

la .hardware      80.000       5.000
la .software      60.000      10.000
nyc.hardware     100.000      15.000
nyc.software     130.000      25.000
sfo.hardware      50.000       9.000
sfo.software      60.000       6.000
was.hardware      80.000       7.000
was.software      90.000       8.000

INDEX 1 = 1998

                   sales      profit

la .hardware      88.000       5.250
la .software      66.000      10.500
nyc.hardware     110.000      15.750
nyc.software     143.000      26.250
sfo.hardware      55.000       9.450
sfo.software      66.000       6.300
was.hardware      88.000       7.350
was.software      99.000       8.400

Here are the input files, for more information about sql2gms click here.

The CSV (Comma-separated values) file format can be easily imported into GAMS at compilation time. This format is for instance easily generated by Excel, using its Save As CSV functionality. Consider the GAMS table:

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
;

In Excel that table can be easily entered as follows:

Notice that we added a 'dummy' string in cell A1. This is necessary as we need a placeholder there (the underlying problem is a bug in GAMS: the comma should be enough to signal the end of a field). Now we save this worksheet as a CSV file - which will look like:

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

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

Sets
       i   canning plants   / seattle, san-diego /
       j   markets          / new-york, chicago, topeka / ;
table d(i,j) 'distance in thousands of miles'
$ondelim
$include data.csv
$offdelim
display d;

Notice we have left out a ';' between the TABLE statement and the display statement. Usually GAMS is quite tolerant regarding this, and indeed the listing file shows:

   1  Sets
   2         i   canning plants   / seattle, san-diego /
   3         j   markets          / new-york, chicago, topeka / ;
   4  table d(i,j) 'distance in thousands of miles'
INCLUDE    C:\temp\data.csv
   7  dummy ,new-york,chicago,topeka
   8  seattle,2.5,1.7,1.8
   9  san-diego,2.5,1.8,1.4
  11  display d;

...

----     11 PARAMETER d  distance in thousands of miles

             new-york     chicago      topeka

seattle         2.500       1.700       1.800
san-diego       2.500       1.800       1.400

A well known problem is internationalization. In non-US countries often different conventions are used for formatting numbers. An example is the use of a decimal comma instead of a decimal point. This has a ripple through effect as the comma is then no longer available to be used as a separator symbol. This is illustrated when we change in Windows the Regional Settings to German (Standard) and save our spreadsheet table

as a CSV file (input.csv), we get:

dummy;new-york;chicago;topeka
seattle;2,5;1,7;1,8
san-diego;2,5;1,8;1,4

The decimal points have become decimal comma's and the comma separator symbols are now semi-colons. This file can not be processed by GAMS as GAMS does not consult the Windows settings but sticks to US standardization.

To change the comma into a dot and the semicolon into a comma we just use one of the POSIX tools, which are part of any GAMS system and write a few lines of GAMS code:

* translate , to . using tr
$call "tr , . <input.csv >temp.csv"
* translate ; to , using tr
$call "tr ; , <temp.csv >output.csv"
table data(*,*)
$ondelim
$include output.csv
$offdelim
display data;

or

* translate , to . and ; to , using sed
$onecho > sedscript
s/,/./g
s/;/,/g
$offecho
$call sed -f sedscript input.csv > output.csv
table data(*,*)
$ondelim
$include output.csv
$offdelim
display data;

and get

----     14 PARAMETER data  

             new-york     chicago      topeka

seattle         2.500       1.700       1.800
san-diego       2.500       1.800       1.400

Note: When importing and exporting data using the GDX-tools or Wtools , the regional settings are not an issue.

XLS2GMS is a simple utility that allows you to extract data from an Excel spreadsheet and convert it into a GAMS include file. When running xls2gms.exe without extra command line parameters, the utility will run in interactive mode. It will come up with the following form:

The input file consists of an .XLS file. By default the used part of the first sheet is exported, but this can be changed by setting an appropriate range. An example of a range that can be specified is Sheet2!A1:G8. An output file is created that can be used as a GAMS include file. The browse buttons open a File Open or a Save As dialog box that can help you in navigating around on your hard disk. It is advised to use absolute paths, as for windows applications it is not always obvious what the current directory is.

The philosophy of the utility is that you write pure GAMS syntax in the spreadsheet. I.e. you can write GAMS statements such as TABLE, SET, PARAMETER or parts of them. It is your responsibility to write correct GAMS syntax. The only “smart” thing the utility does it to align cells, such that GAMS TABLE data are correctly handled.

In the above example we have a simple spreadsheet:

and call xl2gms with:

$onecho > commands.txt
I=C:\temp\exa.xls
O=exa.inc
R=Sheet1!A1:C3
$offecho
$call ="c:\gams\xls2gms.exe" @commands.txt
<code>

the file exa.inc will look like:
<code>
* -----------------------------------------------------
* XLS2GMS 2.8   Dec 24, 2007 WIN.FR.NA 22.6 280.000.000.vis Delphi           
* Erwin Kalvelagen, GAMS Development Corp.
* -----------------------------------------------------
* Application: Microsoft Excel
* Version:     12.0
* Workbook:    C:\temp\exa.xls
* Sheet:       Sheet1
* Range:       $A$1:$C$3
* -----------------------------------------------------
     j1   j2  
i1   1    3   
i2   2    4   
* -----------------------------------------------------

Note: The complete documentation is available here.

GDXXRW is a utility to read and write Excel spreadsheet data. GDXXRW can read multiple ranges in a spreadsheet and write the data to a 'GDX' file, or read from a 'GDX' file, and write the data to different ranges in a spreadsheet. For further information and the documentation, please consult the documentation about the wtools.

GDXVIEWER is a tool to view and convert data contained in GDX files. It can also export to csv, xls, xml-files and pivot tables. For further information and the documentation, please consult the documentation about the gdxviewer.

When a CSV file is saved with an extension .csv Excel will read it directly. Either click on it in the explorer or use in Excel: File|Open and then select Files of Type: Text Files (*.prn; *.txt; *.csv). Now the file will be read directly into Excel without further questions.

Below is a GAMS fragment to create a .csv file:

file results /results.csv/;
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;

Note: The GDXVIEWER can also export to CSV files. It is possible to spawn Excel automatically from GAMS to view the result by using the ShellExecute utility:

execute '=shellexecute results.csv';

The gdx2xls tool dumps the contents of a gdx file into a spreadsheet or an xml-file. Please visit the gdx-Tools section for more details.

Sometimes a translation step is needed between the labels used in the model and the ones used in the spreadsheet. One way to do this is to use a mapping set in GAMS. Consider the following case. The spreadsheet looks like:

This can be imported easily using the GAMS code:

set ssi /
  'new york', 'washington dc', 'los angeles', 'san francisco'
/;
parameter ssdata(ssi) /
$call =d:\util\xls2gms I="c:\my documents\test2.xls" B O=d:\tmp\x.inc
$include d:\tmp\x.inc
/;
display ssdata;

Notice the B parameter, which is needed as there are embedded blanks in the labels. Now suppose the rest of the model is defined in terms of the set I which is defined as:

    set i /NY,DC,LA,SF/;

To calculate a parameter data defined over this set, the following simple GAMS fragment can be used:

set map(i,ssi) mapping set /
   ny.'new york'
   dc.'washington dc'
   la.'los angeles'
   sf.'san francisco'
/;
display map;

parameter data(i);
data(i) = sum(map(i,ssi), ssdata(ssi));
display data;

Translation of index labels can be done inside GAMS (see the previous paragraph), but also very conveniently inside the database. Consider again the following data to import:

The way to handle the index conversion inside GAMS is:

set ssi /
  'new york', 'washington dc', 'los angeles', 'san francisco'
/;

parameter ssdata(ssi) /
$call =d:\util\mdb2gms I="c:\my documents\test.mdb" Q="select city,value from [example table]" B O=d:\tmp\x.inc
$include x.inc
/;
display ssdata;


set i /NY,DC,LA,SF/;

set map(i,ssi) /
   ny.'new york'
   dc.'washington dc'
   la.'los angeles'
   sf.'san francisco'
/;
display map;

parameter data(i);
data(i) = sum(map(i,ssi), ssdata(ssi));
display data;

Notice that the B parameter is used to protect spaces inside label names. Square brackets are used inside the SQL statement to protect spaces embedded in the table name.

Another way would be to add a table to the Access database:

Now we can have the simpler GAMS fragment:

set i /NY,DC,LA,SF/;
parameter data(i) /
$call =d:\util\mdb2gms I="c:\my documents\test.mdb" Q="select [GAMS City], value from [example table],CityMapper where [Access City]=city" O=d:\tmp\x.inc
$include d:\tmp\x.inc
/;
display data;

Note that this is a very long command line. It is better to move the command Line parameters to a separate file and use the @ notation to instruct mdb2gms to read command line parameters from that file.

Please also visit our notes and examples about executing GAMS from Excel.