A Tool to convert GDX data to MS Excel spreadsheets.

Author

Erwin Kalvelagen

Date

June 10, 2005; December 6, 2006

This document describes the GDX2XLS utility which allows to convert data stored in a GDX file into Excel spreadsheets.

Overview

Attention

GDX2XLS is deprecated (see GAMS 42 GDX2XLS release notes). Please use Connect agent ExcelWriter instead.

GDX2XLS is a tool to dump the complete contents of a GDX file to an MS Excel spreadsheet file (.xlsx or .xls file). Every identifier gets its own sheet in the .XLSX file. Excel 2007 or more recent versions will default to an xlsx file; versions before that default to the .xls file format. For instance when we save the results of the trnsport model from the model library:

C:\tmp>gamslib trnsport
Model trnsport.gms retrieved

C:\tmp>gams trnsport gdx=trnsport lo=2

C:\tmp>gdxdump trnsport.gdx symbols
   Symbol Dim Type Records  Explanatory text
 1 a        1  Par       2  capacity of plant i in cases
 2 b        1  Par       3  demand at market j in cases
 3 c        2  Par       6  transport cost in thousands of dollars per case
 4 cost     0  Equ       1  define objective function
 5 d        2  Par       6  distance in thousands of miles
 6 demand   1  Equ       3  satisfy demand at market j
 7 f        0  Par       1  freight in dollars per case per thousand miles
 8 i        1  Set       2  canning plants
 9 j        1  Set       3  markets
10 supply   1  Equ       2  observe supply limit at plant i
11 x        2  Var       6  shipment quantities in cases
12 z        0  Var       1  total transportation costs in thousands of dollars

C:\tmp>

The example shows how we copy the trnsport.gms model from the model library, and then solve it. The option gdx=filename will save the complete symbol table to a GDX file. The option lo=2 tells GAMS to save the log to a file (in this case trnsport.log) instead of writing it to the screen. The gdxdump will display the contents of the GDX file (the option symbols will only display the table of contents, rather than all data)

Once we have a GDX file we can use GDX2XLS to create an .XLSX file:

C:\tmp>gdx2xls trnsport.gdx
GDX2XLS          34.1.0 r644dbd9 Released Jan 29, 2021 WEI x86 64bit/MS Window
Output file: C:\tmp\trnsport.xlsx
C:\tmp>

Figure 1. Table of Contents sheet

The resulting XLSX file, opened with MS Excel is shown in Figure 1. The first page is the Table of Contents page with all identifiers sorted alphabetically. When clicking on variable x, the sheet shown in Figure 2 is displayed.

Figure 2. A variable from the trnsport.gms model

The table of contents can be reached again by clicking on the TOC link in the top left corner. The complete process shown here can be automated as is shown in section Model gdx2xls1: import trnsport.gdx. As can be seen, every identifier is stored in its own sheet. Index positions get a column with labels showing their domains, or dim1, dim2, etc. if domain information is not available. By default scalar quantities are collected in a single sheet called scalar.

AutoFilter

By default the exported tables are organized in AutoFilter tables. This will allow you to easily make selections and sort the results.

Figure 4. Drop down list in AutoFilter

It is possible to set filters for different columns. Only the rows that meet the criteria will be shown. The columns used in the filter can be recognized by having a blue arrow instead of a black one in the drop down menu header.

Figure 5. Top 10 list configuration in AutoFilter

Figure 6. Custom filter in AutoFilter

Sorting can also be performed on multiple columns: e.g. first sort on one column, then sort on a second column.

The autofilter generation can be turned off using an option in the .ini file.

Options

Default ini file

Options are specified in an .INI file. By default, the file gdx2xls.ini located in the same directory as gdx2xls.exe is consulted. If this file is not available, the program will continue using default settings.

Custom ini file

It is also possible to tell the program to use a different .ini file. This is done by using an extra argument of the form @inifile. An example would be:

C:\TMP> gdx2xls myfile.gdx @myinifile.ini

In this case the program will not read gdx2xls.ini located in the same directory as gdx2xls.exe but rather myinifile.ini in the current directory.

The ini file can contain two sections: [settings] and [colors]. A complete ini file with all possible settings looks like:

[settings]
inf=INF
mininf=-INF
eps=EPS
na=NA
undf=INDF
scalarsheet=1
tableformatting=1
toc=1
sorttoc=1
autofilter=1
freezeheader=1
indexformat=
valueformat=

[colors]
header=17
body=19
italics=48

[xmlcolors]
link=#0000FF
header=#9999FF
body=#FFFFCC
italics=#969696

Settings section

A complete description for the [settings] section is:

An example of setting special values can be found in section Model gdx2xls4: special value mapping.

Colors section

A complete description for the [colors] section is:

The [xmlcolors] section is used to specify colors in the XML file to be generated.

Custom formats

The format strings consists of four pieces:

[format for $x>0$];[format for $x<0$];[format for $x=0$];[format for strings]

An example given in the Excel help is:

#,###.00_);[Red](#,###.00);0.00;"sales "@

The codes used here have the following meaning:

Additional formatting characters include:

Figure 7. Custom format valueformat=#.????

A useful format is:

[settings]
valueformat=#.????

which aligns numbers on the decimal point and depicts zero's as dots just as the listing file is doing.

Examples

Model gdx2xls1: import trnsport.gdx

This example will solve the trnsport.gms model from the model library and generate a GDX file containing the complete symbol table. This GDX file is exported to Excel and MS Excel is launched to inspect the results. This is a small example that should run very quickly.

$onText
Test of GDX2XLS. Dumps all symbols of
trnsport.gms to trnsport.xlsx.
$offText

execute '=gamslib trnsport';
execute '=gams trnsport lo=3 gdx=trnsport';
execute '=gdx2xls trnsport.gdx';
executeTool 'win32.shellExecute trnsport.xlsx';

Notes: the equal signs in from of the external programs indicate we don't go through a shell (e.g. command.com orcmd.exe). This will improve reliability in case the external program is not found. In such a case a proper error will be triggered. Without the '=' such errors go undetected and the GAMS model will continue.

The command ‘executeTool 'win32.shellExecute trnsport.xlsx’;` will launch Excel to view the .XLSX file.

Model gdx2xls2: import indus89.gdx

This example will solve the indus89.gms model from the model library and generate a GDX file containing the complete symbol table. This GDX file is exported to Excel and MS Excel is launched to inspect the results. This is a fairly large GDX file, with many identifiers, resulting in many sheets in the workbook.

$onText
Test of GDX2XLS. Dumps all symbols of
indus89.gms to indus89.xlsx. This takes
longer as there is a large number of symbols.
$offText

execute '=gamslib indus89';
execute '=gams indus89 lo=3 gdx=indus89';
execute '=gdx2xls indus89.gdx';
executeTool 'win32.shellExecute indus89.xlsx';

Model gdx2xls3: a large table

This is an artificial example where we generate a large identifier in GAMS: a parameter with as many elements as the number of rows that Excel can handle.

$onText
Test of GDX2XLS. Single symbol with 65536-3=65533 records.
Maximum rows that XLS can handle is 65536; an XLSX file allows for slightly more than a million rows.
$offText

Set i / i1*i65533 /;

Parameter p(i);
p(i) = uniform(-100,100);
execute_unload 'test.gdx', p;

execute '=gdx2xls test.gdx';
executeTool 'win32.shellExecute test.xlsx';

If you create a spreadsheet with too many rows, the XLSX file writer will return OLE error 800A03EC. When generating an XML file, an error will occur when Excel loads the file.

Model gdx2xls4: special value mapping

To store special values like INF, EPS, NA in a numeric field in the database, GDX2XLS uses a mapping. This mapping can be changed using an INI file.

$onText
Test of GDX2XLS.
Check special value mapping.
$offText

$onEcho > m.ini
[settings]
inf=1.0e100
mininf=-1.0e100
eps=0.0
na=#NA!
undf=#UNDF!
$offEcho

Parameter p(*) / i1 inf, i2 -inf, i3 eps, i4 na /;
p('i5') = 1/0;
display p;


* save parameter p in p.xlsx
* special values are translated to default values:
execute_unload "p.gdx", p;
execute '=gdx2xls p.gdx';
executeTool 'win32.shellExecute p.xlsx';

* save parameter p in q.xls using new mapping
* INF -> 1.0e100  (numeric)
* -INF -> -1.0e100  (numeric)
* EPS -> 0.0  (numeric)
* NA -> #NA! (string)
* UNDF -> #UNDF! (string)
*
execute_unload "q.gdx", p;
execute '=gdx2xls q.gdx @m.ini';
executeTool 'win32.shellExecute q.xlsx';

Numeric values are important if you want Excel being able to operate on these numbers.

Model gdx2xls8: custom format

We use a custom value format to color the different values x<0, x=0, x>0 differently. Also align on the decimal point.

$onText
GDX2XLS example: use of custom format
$offText

$onEcho > mexls.ini
[settings]
valueformat=[Blue]#.????;[Red]-#.????;[Green]0.????;[Magenta]
$offEcho

execute '=gamslib mexls';
execute '=gams mexls lo=3 gdx=mexls';
execute '=gdx2xls mexls.gdx @mexls.ini';
executeTool 'win32.shellExecute mexls.xlsx';

Figure 8. Custom format

Model gdx2xls9: custom format 2

This uses the more useful custom format valueformat=#.???? (see Figure 7).

$onText
GDX2XLS example: use of custom format
$offText

$onEcho > align.ini
[settings]
valueformat=#.????
$offEcho

execute '=gamslib mexls';
execute '=gams mexls lo=3 gdx=mexls';
execute '=gdx2xls mexls.gdx @align.ini';
executeTool 'win32.shellExecute align.xlsx';