Table of Contents
GDX2ACCESS is a tool to dump the contents of a GDX file to an
MS Access file (
.accdb file). Every identifier gets its own table in the database. For instance when we save the results of the trnsport model from the model library:
C:\GAMS support\settext>gamslib trnsport Copy ASCII: trnsport.gms C:\GAMS support\settext>gams trnsport gdx=trnsport lo=2 C:\GAMS support\settext>gdxdump trnsport.gdx symbols * 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
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
GDX2ACCESS to create an
.MDB or a
.ACCDB file. Versions of MicroSoft Office prior to version 2007 use the file extension
.mdb; version 2007 use the file extension
C:\GAMS support\settext>gdx2access trnsport.gdx 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 MDB, opened with MS Access is shown in Figure 1. The complete process shown here can be automated as is shown in section 3.1. As can be seen, every identifier is stored in its own table. If no domain information is available for a symbol, index positions get a column with labels dim1, dim2, etc. If domain information is available, the columns will use that information but keeping the names unique. The small example below shows how the index positions are made unique using the (relaxed) domain information.
set i /i1*i5/; parameter A(i, i, i); Alias(i, j, k); A(i, j, k) = uniform(0,1); execute_unload 'ex1.gdx', A; execute '=gdx2access ex1.gdx';
For parameters, the value is stored in a column named value, while variables and equations have columns level, marginal, lowerbound, 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 before writing the GDX file.
Options are specified in an
.INI file. 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.
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:
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:
[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 [debug] method=5 thresholdcount=5 keepfiles=1
Description for the settings and debug sections:
|scrdir||<windowstemp>||Directory for temporary scratch files.|
|inf||1.0e100||The value used for GAMS |
|mininf||-1.0e100||The value used for GAMS |
|eps||0.0||The value used for GAMS |
|na||0.0||The value used for GAMS |
|undf||0.0||The value used for GAMS |
|scalartable||0||Value 0/1; when set to 1, combine scalars of the same type in a single table. The names for these tables are fixed: |
|etflag||0||Value 0/1; When set to 1, include the text strings for sets containing element texts.|
|dbversion||0||Create database using current default format|
|9: Create a database in the Microsoft Access 2000 (.mdb) file format.|
|10: Create a database in the Microsoft Access 2002-2003 (.mdb) file format.|
|12: Create a database in the Microsoft Office Access 2007 (.accdb) file format.|
|method||5||Select algorthm to insert data ino the Access database|
|1: Write a CSV file and use the TransferText action to read this into Access. This is fast, but does not always work when non-US language settings are used.|
|2: Use recordset.add to add records. This is slow, but does not use intermediate files.|
|3: Write a tab delimited file with a complete file specification (schema.ini) and use the ISAM Text driver to import the data. This is fast and should work in international settings.|
|4: For small data use method=2 and for larger data items use method=1.|
|5: For small data use method=2 and for larger data items use method=3.|
|thresholdcount||5||When to change between algorithms when using method=4 or method=5. The default is 5 records.|
|keepfiles||0||Value 0/1; when set to 1, the program will not delete intermediate scratch files.|
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 Access and MS Access is launched to inspect the results. This is a small example that should run very quickly.
$ontext Test of GDX2ACCESS. Dumps all symbols of trnsport.gms to trnsport.mdb $offtext execute ’=gamslib trnsport’; execute ’=gams trnsport lo=3 gdx=trnsport’; execute ’=gdx2access trnsport.gdx’; execute ’=ShellExecute trnsport.mdb’;
Notes: the equal signs in from 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.
ShellExecute will launch Access to view the
.MDB file, see . This assumes that the version of of Access installed is a version prior to version 2007. Later versions will generate a database with the extension .accdb and the ShellExecute call needs to be changed as follows:
* view generated file create execute ’=ShellExecute trnsport.accdb’;
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 an option (etflag) to get this text saved in the Access database. 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 an option (dbversion) to save the database in the .mdb format.
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 > task2.ini [settings] etflag=1 dbversion=10 $offecho $call =gdx2access example2.gdx @task2.ini
When we open the database and inspect the table create from the set
ij we see the explanatory texts stored:
This is an artificial example where we generate a large identifier in GAMS: a parameter with a million elements.
$ontext Test of GDX2ACCESS. Dumps a large symbol (a million elements) to an Access Database. $offtext set i /i1*i1000/; alias (i,j); parameter p(i,j); p(i,j) = uniform(-100,100); execute_unload ’test.gdx’,p; execute ’=gdx2access test.gdx’;
To store special values like
NA in a numeric field in the database, GDX2Access uses a mapping. This mapping can be changed using an INI file.
$ontext Test of GDX2ACCESS. Check special value mapping. $offtext $onecho > m.ini [settings] inf=1 mininf=2 eps=3 na=4 undf=5 $offecho parameter p(*) / i1 inf i2 -inf i3 eps i4 na /; p(’i5’) = 1/0; display p; * * save parameter p in p.mdb, as table p * special values are translated to default values: * INF -> 1.0e100 * -INF -> -1.0e100 * EPS,NA,UNDF -> 0 * execute_unload "p.gdx",p; execute ’=gdx2access p.gdx @m.ini’;
We cannot enter a value of
Undef directly so we use a division by zero to get the
When we view the generated GDX file in the GAMS IDE, the special values are shown:
Viewing the resulting table in Access shows how the mapping for special values was applied:
GDX2Access will use names like
Value etc. 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[3, 2] can handle this task. The script will rename the columns
j, and Value in table
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 ; * * 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 * execute "=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