Overview

GDX2ACCESS is a tool to dump the contents of a GDX file to an MS Access file (MDB or ACCDB format). Every identifier gets its own table in the database.

Attention

GDX2ACCESS is deprecated (see GAMS 40 GDX2ACCESS release notes). Please use Connect agent SQLWriter instead.

Usage

gdx2access inputFile {options} 

The .gdx file extension of the inputFile can be omitted. Files without a full path name are assumed to be in the current directory when using a command prompt. When using the GAMS IDE, these files are assumed to be in the current project directory.

Options

Options are specified in an [INI] (https://en.wikipedia.org/wiki/INI_file) file and not directly on the command line. By default, the file gdx2access.ini located in the same directory as gdx2access.exe is consulted. If this file is not available, the program will continue using default settings (listed in the tables below).

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. If you want to dump the contents of myFile.gdx to an MS Access file according to the options specified in a file called myIniFile.ini, run the following code:

gdx2access myFile.gdx @myIniFile.ini

In this case, the program will not read gdx2access.ini located in the same directory as gdx2access.exe, but rather myIniFile.ini in the current directory.

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

[settings]
scrdir=c:\tmp
inf=1.0e100
mininf=-1.0e100
eps=0.0
na=0.0
undf=0.0
scalarTable=1
etFlag=1
dbVersion=9
[debug]
method=5
thresholdCount=5
keepFiles=1

Note that the values above are not the default values for some options!

Section [settings]

Below some short descriptions for the options in the [settings] section:

Section [debug]

Below some short descriptions for the options in the [debug] section:

Examples

Intro

Suppose we want to write the data of the [trnsport] model from the GAMS model library after solving to an MS Access data file. First of all, we solve the model by running the following command. Note that we will save the complete symbol table to a GDX file called trnsport.gdx by adding the command line option gdx=trnsport.

gams trnsport gdx=trnsport lo=2

The option lo=2 causes GAMS to save the log to an external file (in this case trnsport.log), instead of writing it to the screen. In order to get an idea about the data at this point, we use the GDXDUMP tool to display the contents of the GDX file by running the following command:

gdxdump trnsport.gdx symbols

By adding the GDXDUMP option symbols, we will only display the table of contents (shown below) rather than all data stored in trnsport.gdx.

*  GDX dump of trnsport.gdx
*  Library in use : C:\GAMS23.3
*  Library version: GDX Library      Nov  1, 2009 23.3.3 WIN 14596.15043 VIS x86/MS Windows
*  File version   : GDX Library      Nov  1, 2009 23.3.3 WIN 14596.15043 VIS x86/MS Windows
*  Producer       : GAMS Base Module Nov  1, 2009 23.3.3 WIN 14929.15043 VIS x86/MS Windows
*  File format    :    7
*  Compression    :    0
*  Symbols        :   12
*  Unique Elements:    5
   Symbol Dim Type  Explanatory text
 1 a        1  Par  capacity of plant i in cases
 2 b        1  Par  demand at market j in cases
 3 c        2  Par  transport cost in thousands of dollars per case
 4 cost     0  Equ  define objective function
 5 d        2  Par  distance in thousands of miles
 6 demand   1  Equ  satisfy demand at market j
 7 f        0  Par  freight in dollars per case per thousand miles
 8 i        1  Set  canning plants
 9 j        1  Set  markets
10 supply   1  Equ  observe supply limit at plant i
11 x        2  Var  shipment quantities in cases
12 z        0  Var  total transportation costs in thousands of dollars

Once we have a GDX file, we can use GDX2ACCESS to create a MDB or an ACCDB file. Versions of MicroSoft Office prior to version 2007 use the file extension .mdb, while version 2007 and later versions use the file extension .accdb. We write all the data stored in the GDX file trnsport.gdx to trnsport.mdb resp. trnsport.accdb by running the following code:

gdx2access trnsport.gdx

Note that we do not referenced an INI file in the previous command in order to specify the options, i.e. GDX2ACCESS will consult the file gdx2access.ini located in the same directory as gdx2access.exe or, if the file does not exist, the tool will continue using the default settings (see Options). The information written to the log is shown below, including the identifiers of the symbols which are written to trnsport.accdb and the elapsed time per symbol and in total.

GDX Access       ALFA 23Mar10 23.4.0 WIN 16693.16738 VS8 x86/MS Windows
Creating C:\GAMS support\settext\temp1.accdb with Access: 0.48 seconds
Using temp directory C:\Users\Paul\AppData\Local\Temp\
           i.   Insert:  0.00 seconds
           j.   Insert:  0.00 seconds
           a.   Insert:  0.00 seconds
           b.   Insert:  0.00 seconds
           d.   Dump:  0.00 seconds  Load:  0.05 seconds
           f.   Insert:  0.00 seconds
           c.   Dump:  0.00 seconds  Load:  0.05 seconds
           x.   Dump:  0.00 seconds  Load:  0.03 seconds
           z.   Insert:  0.00 seconds
        cost.   Insert:  0.00 seconds
      supply.   Insert:  0.02 seconds
      demand.   Insert:  0.02 seconds
Renaming  C:\GAMS support\settext\temp1.accdb -> trnsport.accdb
Total elapsed time: 0.92 seconds

The resulting ACCBD, opened with MS Access, is shown in Figure 1 (limited to the parameter c on the left and the variable x on the right).

Figure 1: Contents of the file transport.gdx converted to an Access database

As can be seen, every identifier is stored in its own table. For parameters like c, the value is stored in a column named value, while variables like x and equations have the columns level, marginal, lowerbound and upperbound. A possible additional field (scale for NLP's, priority for MIP’s, stage for stochastic problems) is not exported. If needed, you can assign such a quantity to a parameter in GAMS before writing the GDX file. The complete process shown here can be automated as demonstrated in Example 1.

If no domain information is available for a symbol (like B in the code below), each index position gets a column and will be labeled automatically with dim1, dim2, etc. If domain information is available, the columns will use that information but keeping the names unique (like A in the code below). The small example below shows how the index positions are made unique using the (relaxed) domain information by adding an ascending number to the identifier.

Set i / i1*i5 /;
Alias (i,j,k);

Parameter A(i,i,i) 'domain informations, but the column name would not be unique';
A(i,j,k) = uniform(0,1);

Parameter B 'no domain information' / i1.i1 1, i1.i2 2, i2.i1 3, i2.i2 4 /;

execute_unload 'AB.gdx', A, B;
execute '=gdx2access AB.gdx';

The resulting database file, opened with MicroSoft Access, is shown in Figure 2 (parameter A on the left, parameter B on the right).

Figure 2: File AB.gdx converted to an Access database

Example 1 - Dumping the Contents of trnsport.gdx

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

execute '=gamslib trnsport';
execute '=gams trnsport lo=3 gdx=trnsport';
execute '=gdx2access trnsport.gdx';
executeTool 'win32.shellExecute trnsport.accdb';

Note: the equal signs in front of the external programs indicate we don’t go through a shell (e.g. command.com or cmd.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.accdb’;` will launch Access to view the ACCDB file. This assumes that the version of Access installed is a version as recent as version 2007. Earlier versions will generate a database with the extension .mdb and the shellExecute command needs to be changed as follows:

* view generated file create
executeTool 'win32.shellExecute trnsport.mdb';

The complete example is also part of the GAMS Data Utilities Library, see model [GDX2ACCESSExample1] for reference.

Example 2 - Writing Explanatory Text to Database

In this example, we write a few sets to a GDX file; two of the sets written have explanatory text for set elements. We use the option etFlag to get this text saved in the Access database file along with the corresponding set elements. Without using the option, only the set tuples are saved in the database.

Running this example on a machine with Access 2007 or a later version installed will create a database with the .accdb file extension that cannot be read by an older version of Access. We use the dbVersion option to save the database in the MDB format. Both options must be specified in an user defined INI file within the settings section, see howToWrite.ini in the code below.

Set
   i       / i1 'one', i2 'two', i3 'three', i4 'four' /
   j       / j1*j4 /
   ij(i,j) / i1.j1 'red', i2.(j2,j3) 'green', i3.(j1,j2) 'blue' /;

$gdxOut example2.gdx
$unload i j ij
$gdxOut

$onEcho > howToWrite.ini
[settings]
etFlag=1
dbVersion=10
$offEcho

$call =gdx2access example2.gdx @howToWrite.ini%

When we open the database and inspect the table created from the set ij, we see the explanatory texts stored along with the corresponding set elements. The column containing the explanatory text will be labeled with SetText automatically. The column headers can be renamed as demonstrated in Example 5 - Renaming Fields.

Figure 3: Explanatory text written to database

The complete example is also part of the GAMS Data Utilities Library, see model [GDX2ACCESSExample2] for reference.

Example 3 - Dumping a large Table to Database

This is an artificial example where we generate a large identifier in GAMS: a parameter with a million elements. This parameter will be dumped to an MS Access Database afterwards. Note that the GDX2ACCESS execution will last several seconds. The resulting database file has approximately 36MB of size.

Set i / i1*i1000 /;
Alias (i,j);
Parameter p(i,j);
p(i,j) = uniform(-100,100);
execute_unload 'example3.gdx', p;
execute '=gdx2access example3.gdx';

The complete example is also part of the GAMS Data Utilities Library, see model [GDX2ACCESSExample3] for reference.

Example 4 - Special Value Mapping

To store special values like +INF, -INF, EPS, NA and UNDF in a numeric field in the database, GDX2ACCESS uses a mapping. This mapping can be changed by using an INI file (for the default values, see Options resp. the comments in the second code below). We will define a scalar for each special value in GAMS in order to demonstrate the scalarTable option in addition. By default, every scalar will be written to a new table. By activating the scalarTable option in the INI file, all scalars will be stored together in a single table.

$onEcho > howToWrite.ini
[settings]
inf=1
mininf=2
eps=3
na=4
undf=5
scalarTable=1
$offEcho

The usage of our previously customized INI file howToWrite.ini is indicated by the argument @howToWrite.ini within the execute statement.

$onUndf
Scalar
   pInf      /  inf /
   mInf      / -inf /
   epsilon   /  eps /
   notAvail  /   na /
   undefined / undf /;

* save scalars in scalars.mdb in a single table named ScalarParameter using
* the scalarTable option
* special values are translated to default values:
*  INF ->  1.0e100
* -INF -> -1.0e100
* EPS,NA,UNDF -> 0

execute_unload 'scalars.gdx', pInf, mInf, epsilon, notAvail, undefined;
execute 'gdx2access scalars.gdx @howToWrite.ini';

Note the $onUndf command in order to enter Undf values directly within the definition of the scalars. When we view the generated GDX file in the GAMS IDE or GAMS Studio, the special values are shown (note that the figure shows a parameter actually instead of five single scalars for a more compact presentation):

Figure 4: Scalar values viewed in GAMS Studio

Viewing the resulting table in Access shows how the mapping for special values was applied (i.e. the GAMS special values have been substituted by our customized values defined in the INI file):

Figure 5: Mapped scalar values viewed in Microsoft Access

Note that the table will be named ScalarParameter automatically.

The complete example is also part of the GAMS Data Utilities Library, see model [GDX2ACCESSExample4] for reference.

Example 5 - Renaming Fields

GDX2ACCES will use names like i, j, dim1, dim2, value etc. for the column headers in the resulting database file. In some cases, this may not be convenient, e.g. when more descriptive field names are required. In the following model, we will show how a small script in VBscript[1] can handle this task. The script will rename the columns i, j, and Value in table c to ifrom, jto, and transportcost. At first, the data is defined and dumped to an MicroSoft Access database. The VBscript named access.vbs is written at compile time and later executed at execution time in order to rename the column headers.

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

Parameter
   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;

* export to gdx file.
execute_unload 'c.gdx', c;

* move to access database
* column names are i and j
execute '=gdx2access c.gdx';

* rename columns
execute '=cscript access.vbs';

* view results
executeTool 'win32.shellExecute c.accdb';
 
$onEcho > access.vbs
'this is a VBscript script
WScript.Echo "Running script: access.vbs"
set oa = CreateObject("Access.Application")
set oDAO = oa.DBEngine
Wscript.Echo "DAO Version: " & oDAO.version
Set oDB = oDAO.openDatabase("%system.fp%c.accdb")
Wscript.Echo "Opened : " & oDB.name
Set oTable = oDB.TableDefs.Item("c")
Wscript.Echo "Table : " & oTable.name
' rename fields
oTable.Fields.Item("i").name = "ifrom"
oTable.Fields.Item("j").name = "jto"
oTable.Fields.Item("Value").name = "transportcost"
Wscript.Echo "Renamed fields"
oDB.Close
Wscript.Echo "Done"
$offEcho

The resulting ACCBD, opened with MS Access, is shown in Figure 6 (parameter c before executing the VBScript on the left, parameter c after renaming the column headers on the right).

Figure 6: File c.gdx converted to an Access database

The complete example is also part of the GAMS Data Utilities Library, see model [GDX2ACCESSExample5] for reference.

References

1. VBScript Language Reference, https://www.vbsedit.com/html/ddfa5183-d458-41bc-a489-070296ced968.asp 2023