Overview

Besides inspecting a GDX file, gdxviewer allows you to export to a large number of data formats, including ASCII text, CSV, HTML, XML, database, and spreadsheet formats.

This tool is designed as an interactive Windows program, but it can also be operated through command line parameters.

Requirements

GDXVIEWER runs only on PC's running Windows (95/98/NT/XP).The DLL GDXIO.DLL needs to be in the same location as GDXVIEWER.EXE. If XLS files are saved, MS Excel needs to be present. If MDB database files are saved, MS Access needs to be present.

If GDXIO.DLL is not found in the same directory as the executable gdxviewer.exe, the following window will be shown:

A simple way to make sure that GDXVIEWER has access to the GDXIO.DLL dynamic load library is to place gdxviewer.exe in the GAMS system directory, e.g. c:\program files\GAMS21.3.

Creating GDX files

GDX files are binary data files. They can contain sets, parameters (including scalars), equations and variables. These files can be generated by a number of tools: by GAMS itself, by utilities such as MDB2GMS, SQL2GMS, GDXXRW.

To save all data from a GAMS model into a GDX file you can use the GDX=fln command line parameter:

C:\> gams trnsport.gms GDX=trnsport.gdx

From the IDE you can specify the command line parameter GDX=trnsport.gdx in the parameter edit box:

To selectively place identifiers in a GDX file you can use the execute_unload statement:

In this example the sets i and j and the variable x are saved to the GDX file results.gdx.

Other ways to create GDX files include:

The MDB2GMS tool can be used to convert data stored in MS Access tables to GDX files

The SQL2GMS tool can read data from virtually any SQL database (including any ODBC accessible database) and can create GDX files.

The tool GDXXRW allows data from an Excel spreadsheet to be stored in a GDX file.

$GDXOUT allows you to write data to a GDX file during GAMS compile time. This is not as useful as execute_unload but may have its use in special cases.

You can write your own program to write a GDX file. There is an API and bindings for different languages such as Delphi, Kylix, VB6, VBA, VB.NET, C/C++, C#, Java, Fortran.

Viewing GDX files

After loading a GDX file in gdxviewer the content of the file is displayed in list view. The left-hand side of the window shows the index of the GDX file organized in a tree structure. When clicking on an identifier, the right-hand-side will display the actual data for the identifier.

When variables are shown, more information is available, such as bounds (lower and upper bounds) and marginals.

The GDX file can be loaded interactively using the File|Open menu, or it can be launched from the command line:

C:\> gdxviewer e:\models\trnsport.gdx

The command line specification can also be used to launch gdxviewer from within a GAMS model as in:

Exporting to an Access Tables

GDXVIEWER can export data directly to a table in an Access database using File|Export|Access (MDB or ACCDB) File. The name of the table will be the name of the parameter. If the table already exists, GDXVIEWER will try to create a new table with a slightly different name (e.g. d2, d3,…).

An option Options|Configuration|Access allows you to set the length of the text fields where the GAMS indices are stored. This length is used when creating the table.

A feature added in version 2.9 is the possibility to use intermediate CSV (comma separated value) files instead of using direct SQL INSERT statements. The CSV files can be read into Access using a bulk operation and is therefore faster for large datasets. When using CSV files make sure double quotes are used (if single quotes are used they will become part of the data). The temporary CSV files will be written to the Windows TEMP directory (e.g. C:\WINDOWS\TEMP). When the import is done, these scratch files will be removed automatically. If you want to look at the CSV files that are being fed into Access, export the data to a CSV file.

Exporting to an SQL Table

It is possible to export data to SQL databases through ADO which includes all databases accessible through ODBC. The configuration information can be specified in Options|Configuration|SQL Database.

The Test Connection button will allow you to check the configuration and see if the database can be connected to.

The SQL data for double precision number is no always the same for each database. E.g. for MS Access you can use double while for MS SQL server you can use float.

When exporting data a new table is created with the name of the identifier. If such a table already exists, names like name2, name3, are tried.

Exporting to MS SQL Server

We can export to Microsoft SQL Server through the standard SQL export facility. However a special facility called BULK INSERT is only available through the specialized SQL Server export tool. BULK INSERT writes a TAB delimited text file to the Windows TEMP directory and subsequently calls BULK INSERT to load that file. This way is often much faster that using individual INSERT statements for each record.

can be generated with File|Export|SQL Insert script. The following settings in Options|Configuration|SQL Insert were used:

Exporting to SQL Update script

An SQL script with UPDATE statements like:

UPDATE dist SET distance=2.5 WHERE city1='seattle' AND city2='new-york';
UPDATE dist SET distance=1.7 WHERE city1='seattle' AND city2='chicago';
UPDATE dist SET distance=1.8 WHERE city1='seattle' AND city2='topeka';
UPDATE dist SET distance=2.5 WHERE city1='san-diego' AND city2='new-york';
UPDATE dist SET distance=1.8 WHERE city1='san-diego' AND city2='chicago';
UPDATE dist SET distance=1.4 WHERE city1='san-diego' AND city2='topeka';

can be generated with File|Export|SQL Update script. The following settings in Options|Configuration|SQL Update were used:

Exporting HTML

GDXVIEWER can write an identifier to an HTML file using File|Export|HTML File.

The options relevant to this format are specified in Options|Configuration|HTML.

Exporting XML

GDXVIEWER can write an identifier to an XML file using File|Export|XML File.

The XML tags can be specified in Options|Configuration|XML.

Exporting fields

The menu Options|Configuration|Export allows you to set which fields are exported.

The following table gives the possibilities for exports:

set

scalar

parameter

variable

equation

Indices

+

+

+

+

Lower bound

+

+

Level/Value

+

+

+

+

Upper bound

+

+

Marginal

+

+

Special Values

GAMS data can assume socalled special values: -INF, +INF, EPS, NA, and UNDF. The meaning of these special values is as follows:

Value

Description

-INF

Minus infinity. Mostly used for non-binding lowerbounds.

+INF

Plus infinity. Mostly used for non-binding upperbounds.

EPS

Mostly used for marginals where it can indicate non-basic but numerically zero.

NA

Not available. Not often used.

UNDF

Undefined. Not often used.

When exporting GAMS identifiers we need to map such values to strings that the receiving program can understand. E.g. we could map –INF to –1.0e10 and +INF to +1.0e10. A good choice for EPS would be 0.0.

The mapping can be specified in Options|Configuration|Special Values:

When we export to a GAMS include file all special values are understood, so the mapping is not used. The defaults button will reset the mapping to their default values.

Plotting Data

GDXVIEWER has a built-in facility to quickly plot data. It includes LINE, BAR and PIE charts, examples are shown below. The plots can be made through the menu File|Plot.

For multi-dimensional data it may be needed to take a “slice” of the data to make meaningful graphs. In the example above we plotted a two dimensional quantity vf which looks like:

In this case we want to plot a pie graph of vf(*,’mexico-df’) which can be specified in the index-selection tab:

Cube View

GDXVIEWER has a Cube View which allows to select rows and columns in a flexible way. In the example below we show a six dimensional variable where three dimensions are fixed, one dimension is chosen for the rows and two dimensions are chosen for the columns.

Below are some of the possibilities using parameter d(i,j) from the trnsport.gms model:

Exporting cubes

After creating a cube view, we can export that configuration by a right mouse click:

Exporting a cube will only export the selected slice (if certain dimensions are held fixed) and depending on the target format it will preserve the layout, e.g. an exported aligned text file can look like:

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

Simarly, the XLS file can look like:

Commandline operation

The GDXViewer utility from version 2.3 accepts several command line parameters, so it can be used in a batch environment. When running in batch mode, the same configuration and option settings are used as for the interactive system and they can be changed by running GDXviewer interactively using the Options menu (the settings are saved in an INI file). It is advised to first run the program interactively until the results are as intented.

Single parameter A single parameter is the filename of the GDX file. GDXViewer will load this file, and will continue to run interactively. Example: .

Gdxviewer.exe test.gdx

XLS writing To write an XLS file, one can use the syntax i=inputfile.gdx xls=outputfile.xls id=x. If a path or filename contains blanks, the name can be surrounded by quotes ("). The 'id' parameter indicates the variable or parameter to export from the GDX file. A complete example is:

Text file writing To write a text file, one can use the syntax i=inputfile.gdx txt=outputfile.txt id=x. If a path or filename contains blanks, the name can be surrounded by quotes ("). The 'id' parameter indicates the variable or parameter to export from the GDX file. A complete example is:

CSV file writing To write a CSV file, one can use the syntax i=inputfile.gdx csv=outputfile.csv id=x. If a path or filename contains blanks, the name can be surrounded by quotes ("). The 'id' parameter indicates the variable or parameter to export from the GDX file. A complete example is:

HTML file writing To write an HTML file, one can use the syntax i=inputfile.gdx html=outputfile.html id=x. If a path or filename contains blanks, the name can be surrounded by quotes ("). The 'id' parameter indicates the variable or parameter to export from the GDX file. A complete example is:

XML file writing To write an XML file, one can use the syntax i=inputfile.gdx xml=outputfile.xml id=x. If a path or filename contains blanks, the name can be surrounded by quotes ("). The 'id' parameter indicates the variable or parameter to export from the GDX file. A complete example is:

GAMS include file writing To write a GAMS include file, one can use the syntax i=inputfile.gdx inc=outputfile.inc id=x. If a path or filename contains blanks, the name can be surrounded by quotes ("). The 'id' parameter indicates the variable or parameter to export from the GDX file. A complete example is:

Access MDB file writing To write a Access MDB file, one can use the syntax i=inputfile.gdx mdb=outputfile.mdb id=x. If a path or filename contains blanks, the name can be surrounded by quotes ("). The 'id' parameter indicates the variable or parameter to export from the GDX file. A complete example is:

Excel Pivot Table writing To write a file containing a pivot table, one can use the syntax i=inputfile.gdx pivot=outputfile.xls id=x. If a path or filename contains blanks, the name can be surrounded by quotes ("). The 'id' parameter indicates the variable or parameter to export from the GDX file. A complete example is:

SQL Database Table writing To write a table to an SQL database, first interactively configure the connection to the database. The Export SQL Database option allows you to see if a connection succeeded and if the correct database was accessed. The configuration information is written to the SQLVIEWER.INI configuration file. The information in this file is used also when performing a batch command-line operation. The syntax is: i=inputfile.gdx sql id=x. A complete example is:

If you need to access several different databases, you can copy the file SQLVIEWER.INI (located in the directory where SQLVIEWER.EXE is placed). To tell GDXVIEWER to read a different INI file, you can say:

GDXVIEWER uses the MS Access and MS Excel applications as COM Object to write files in XLS (both XLS and PIVOT commands) or MDB format. Those applications may write to C:\My Documents in case no full path is specified. Other formats use the default GAMS working directory. In case when running under the IDE this is the location of the project file (*.GPR).

If a path or file name contains a blank, then it is possible to surround the name by double quotes as in: