Data Exchange with Microsoft Excel

This tutorial gives an overview on how to exchange data between GAMS and Microsoft Excel.

GAMS can communicate with Microsoft Excel via GDX (GAMS Data Exchange) files. In order to write data from GAMS and to Excel, selected GAMS data can be written into a GDX file and then to an Excel file: GAMS -> GDX -> Excel. Similarly selected Excel data can be written to a GDX file and then read into GAMS: Excel -> GDX -> GAMS.

Some of GAMS/Excel data exchange tools that provide functionality to exchange data between GAMS and Excel are also discussed in the section Data Exchange Tools. The data exchange between GAMS and a CSV (Comma-separated values) file format and GAMS is covered in Data Exchange with Text Files.

From GAMS to Excel

Consider the following modication of the [TRNSPORT] model from the gams model library.

Sets
     i   'canning plants'   / seattle, san-diego /
     j   'markets'          / new-york, chicago, topeka / ;

Parameters

     a(i)  'capacity of plant i in cases'
       /    seattle     350
            san-diego   600  /

     b(j)  'demand at market j in cases'
       /    new-york    325
            chicago     300
            topeka      275  / ;

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  ;

Scalar f  'freight in dollars per case per thousand miles'  /90/ ;

Parameter c(i,j)  'transport cost in thousands of dollars per case' ;

          c(i,j) = f * d(i,j) / 1000 ;

Variables
     x(i,j)  'shipment quantities in cases'
     z       'total transportation costs in thousands of dollars' ;

Positive Variable x ;

Equations
     cost        'define objective function'
     supply(i)   'observe supply limit at plant i'
     demand(j)   'satisfy demand at market j' ;

cost ..        z  =e=  sum((i,j), c(i,j)*x(i,j)) ;

supply(i) ..   sum(j, x(i,j))  =l=  a(i) ;

demand(j) ..   sum(i, x(i,j))  =g=  b(j) ;

Model transport /all/ ;

Solve transport using lp minimizing z ;

Display x.l, x.m ;


*=== Export to Excel using GDX utilities

*=== First unload to GDX file (occurs during execution phase)
execute_unload "results.gdx" x.L x.M

*=== Now write to variable levels to Excel file from GDX 
*=== Since we do not specify a sheet, data is placed in first sheet
execute 'gdxxrw.exe results.gdx o=results.xls var=x.L'

*=== Write marginals to a different sheet with a specific range
execute 'gdxxrw.exe  results.gdx o=results.xls var=x.M rng=NewSheet!f1:i4'

After the solve statement, the data (x.L and x.M) from variable x can be written into a GDX file during the execution time using the command execute_unload:

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

The execute_unload command above is executed during the actual execution phase to create a GDX file called results.gdx. The solution x and the marginals of x in the GDX file can be written to the Excel file results.xls using GDXXRW tool:

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 second call for marginals x.M, data will be written to cells F1:I4 in the sheet named NewSheet.

Note that GAMS can also write data into a GDX file during compile time. It is also possible to convert data stored in a GDX file into an Excel file spreadsheets using GDX2XLS tool and to write GAMS data to standard output formatted as a GAMS program with data statements using GDXDUMP tool.

From Excel to GAMS

Consider the following modifciation of the [TRNSPORT] model from the gams model library and the file results.xls file created from the previous example.

Sets
     i   'canning plants'   / seattle, san-diego /
     j   'markets'          / new-york, chicago, topeka / ;

Parameters

     a(i)  'capacity of plant i in cases'
       /    seattle     350
            san-diego   600  /

     b(j)  'demand at market j in cases'
       /    new-york    325
            chicago     300
            topeka      275  / ;

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  ;

Scalar f  'freight in dollars per case per thousand miles'  /90/ ;

Parameter c(i,j)  'transport cost in thousands of dollars per case' ;

          c(i,j) = f * d(i,j) / 1000 ;

Variables
     x(i,j)  'shipment quantities in cases'
     z       'total transportation costs in thousands of dollars' ;

Positive Variable x ;

Equations
     cost        'define objective function'
     supply(i)   'observe supply limit at plant i'
     demand(j)   'satisfy demand at market j' ;

cost ..        z  =e=  sum((i,j), c(i,j)*x(i,j)) ;

supply(i) ..   sum(j, x(i,j))  =l=  a(i) ;

demand(j) ..   sum(i, x(i,j))  =g=  b(j) ;

Model transport /all/ ;


*=== Import from Excel using GDX utilities

*=== First unload to GDX file (occurs during compilation phase)
$call gdxxrw.exe results.xls par=Level rng=sheet1!A1:D3

*=== Now import data from GDX
Parameter Level(i,j);
$gdxin results.gdx
$load Level
$gdxin

*=== Fix variables to values from Excel file
x.FX(i,j) = Level(i,j);
display Level, x.L;


Solve transport using lp minimizing z ;

Display x.l, x.m ;

The data in the Excel file can be loaded into a GDX file using the $call command and GDXXRW tool:

$call gdxxrw.exe results.xls par=Level rng=A1:D3

The command $call above executes a program called GDXXRW during compile time. The GDXXRW reads data from the range A1:D3 in results.xls into a GAMS parameter called Level in the GDX file results.gdx. As an output GDX file is not specified when calling GDXXRW, the output file will be derived from the input file by changing the file extension of the input file and removing any path information.

To import data from a GDX file into a parameter, the parameter must be defined over appropriate sets before read. The data from a GDX file can be read during the compile time using the commands $gdxin and $load:

Parameter Level(i,j);
$gdxin results.gdx
$load Level
$gdxin

The first command $gdxin specifies the name of the GDX file results.gdx to be read. The command $load reads parameter Level from the GDX file. The second command $gdxin closed the GDX file.

GAMS can read from a GDX file either during compile time or during execution time. See Example 4 - Reading a GDX File when reading data with domain information and Example 5 - Reading a GDX File when reading from a GDX file during execution time.

Note that it is also possible to write all worksheets of an Excel workbook into a GDX file using XLSDUMP tool.

Data Exchange Tools

There are a number of tools that provide functionality to exchange data between GAMS and an Excel file. This section discusses some of the data exchange tools with some examples. The complete list of the tools can be found at GAMS/Excel Data Exchange tools.

GDXRW

GDXXRW is a tool 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.

How to use GDXXRW to exchange data between GAMS and Excel is covered in the section From GAMS to Excel and the section From Excel to GAMS. More details on usage and examples of GDXXRW tool is covered in GDXXRW.

XLS22GMS

XLS2GMS is a simple utility that allows you to extract data from an Excel spreadsheet and convert it into a GAMS include file. XLS2GMS can be run interactively or in batch mode.

Consider the Excel data from the following spreadsheet:

data_exchange_excel6.png

The data can be imported from the Excel file into a GAMS include file by calling XLS2GMS tool and inserted an include file as parameter data elements using the command $include:

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.

Sometimes a translation between the labels used in the model and the ones used in the is needed. One way to do this is to use a mapping set in GAMS. 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 map 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;

SQL2GMS

In some cases it is convenient to consider tabular data in an Excel spreadsheet as a database table and to import it via GDX file using the SQL2GMS tool.

Consider the following spreadsheet:

data_exchange_excel1.png

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 tool as follows:

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

GDXVIEWER

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. The usage and examples are covered in GDXVIEWER.

GDX2XLS

GDX2XLS tool o conver the contents of a GDX file into an Excel file or an xml-file. The usage and examples are covered in GDX2XLS.