Thursday, June 3, 2010

GDX2DBF: Convert Gdx files to dbase .DBF files

This tool is designed to convert GDX files into DBF files. DBF files are used in dBase, xBase, FoxPro and other programs. Also some programs use DBF files to import and export data. A good example is the GIS program ArcView. DBF files are also used in many other GIS applications to store data in conjunction with shape files.

Usage

d:\gdx2dbf>gdx2dbf
GDX2DBF Version 1.1
Usage:
> GDX2DBF xxx.gdx [xxx.ref] [@inifile] [d=outputdir]

Dumps a GDX file to .DBF files.

Ini file format (default: GDX2DBF.INI):
[settings] (section name is required)
inf=value for +INF (default 9999999.9999)
mininf=value for -INF (default -9999999.9999)
eps=value for EPS (default 0)
na=value for NA (default 0)
undf=value for UNDF (default 0)
tablelevel=n (3=dbaseIII+,4=dbaseIV,7=dbaseVII,25=foxpro, default=3)
numerictype=N,F,O (default=N)
numericsize=value (default=18)
numericprecision=value (default=8)
scalartable=yes/no (combine scalars in one table, default=yes)
scalarparameter=value (name of scalartable, default="scalarparameter.dbf")
scalarvariable=value (name of scalartable, default="scalarvariable.dbf")
scalarequation=value (name of scalartable, default="scalarequation.dbf")

d:\gdx2dbf>


Notes:



  • The tool generates a file for each symbol (set, parameter, variable, equation) in the GDX file.
  • It can export large amounts of data quickly but it does not have a pretty GUI.
  • By default scalar parameters, variables, equations are collected into one table. Otherwise a scalar would generate a file with a single number.
  • By default we write numeric data as 'N' data. This is actually a string representation using numericsize and numericprecision. If you want to keep full precision you can store numbers as 8 byte floating point numbers (binary representation) using numerictype=O but this needs tableversion=7.
  • To view a DBF file Excel can be used: in Excel 2003: Data|Import External Data|Import Data.
  • Don't know if 'F' has an advantage over 'N'.
  • I have seen funny things when trying to move large numbers like 1e100 into a small numeric 'N' field. I probably should generate an exception when it does not fit.
  • By default the .DBF files are written in the current directory. If you want to write to a different directory you can use the d=xxx parameter.

Detailed description of the options


Command line options


  • Name of the GDX file to convert (required)
  • Name of the ref file to add domain names (optional). Ref files can be generated using the rf=xxx flag on the GAMS call.
  • Name of ini file to read (optional). The format for this flag is @inifilename.
  • Examples of the d=xxx parameter:
    • $call '=gdx2dbf trnsport.gdx d=c:\tmp\'
    • $call '=gdx2dbf indus89.gdx indus89.ref d="t m p"'

Ini file settings


  • inf can be used to map GAMS INF values to a number in the DBF file. A large positive value that fits in the field width would be appropriate. The default is 9999999.9999.
  • mininf can be used to map GAMS -INF values to a number in the DBF file. A large negative value that fits in the field width would be appropriate. The default is -9999999.9999.
  • eps can be used to map GAMS EPS values to a number in the DBF file. Often the default of 0 is acceptable.
  • na can be used to map GAMS NA values to a number in the DBF file. Often the default of 0 is acceptable. NA's are not often found in GAMS data.
  • undf can be used to map GAMS UNDF values to a number in the DBF file. Often the default of 0 is acceptable. UNDF's are not often found in GAMS data.
  • tablelevel indicates the DBF version to be used
    • tablelevel=3 (default) generates DBASE III files
    • tablelevel=4 generates DBASE IV files
    • tablelevel=7 generates DBASE VII files. These files can export numeric data in full precision using numerictype=O
    • tablelevel=25 generates FoxPro files
  • numerictype indicates the type to be used for numeric values
    • numerictype=N (default) is the standard dbase numeric type. From datatypes we conclude with this type we actually should require numericwidth<18.
    • numerictype=F. This should have numericwidth=20 (from dbase IV).
    • numerictype=O. This will store a numeric value in binary. It has length of 8 bytes.
  • numericsize gives the field width for numeric data.
  • numericprecision
  • scalartable indicates whether scalar data should be collected in separate tables or that they should be written as usual. In that case tables with a single row will be generated.
  • scalarparameter is only used if scalartable=yes. In that case it contains the name of the table that will contain all scalar parameters.
  • scalarvariable is only used if scalartable=yes. In that case it contains the name of the table that will contain all scalar variables.
  • scalarequation is only used if scalartable=yes. In that case it contains the name of the table that will contain all scalar equations.

Test GAMS model



* no params gives help
$call '=gdx2dbf';

* small gdx file
$call '=gamslib trnsport';
$call '=gams trnsport lo=2 gdx=trnsport';
$call '=gdx2dbf trnsport.gdx';

* large gdx + ref file
$call '=gamslib indus89';
$call '=gams indus89 rf=indus89 lo=2 gdx=indus89';
$call '=gdx2dbf indus89.gdx indus89.ref';


This model should show something like:



--- Job testgdx2dbf.gms Start 08/24/07 15:44:30
GAMS Rev 148 Copyright (C) 1987-2007 GAMS Development. All rights reserved
Licensee: Erwin Kalvelagen G070509/0001CE-WIN
GAMS Development Corporation DC4572
--- Starting compilation
--- testgdx2dbf.gms(3) 2 Mb
--- call =gdx2dbf
GDX2DBF Version 1.0
Usage:
> GDX2DBF xxx.gdx [xxx.ref] [@inifile]

Dumps a GDX file to .DBF files.

Ini file format (default: GDX2DBF.INI):
[settings] (section name is required)
inf=value for +INF (default 9999999.9999)
mininf=value for -INF (default -9999999.9999)
eps=value for EPS (default 0)
na=value for NA (default 0)
undf=value for UNDF (default 0)
tablelevel=n (3=dbaseIII+,4=dbaseIV,7=dbaseVII,25=foxpro, default=3)
numerictype=N,F,O (default=N)
numericsize=value (default=20)
numericprecision=value (default=8)
scalartable=yes/no (combine scalars in one table, default=yes)
scalarparameter=value (name of scalartable, default="scalarparameter.dbf")
scalarvariable=value (name of scalartable, default="scalarvariable.dbf")
scalarequation=value (name of scalartable, default="scalarequation.dbf")
--- testgdx2dbf.gms(6) 2 Mb
--- call =gamslib trnsport
Model trnsport.gms retrieved
--- testgdx2dbf.gms(7) 2 Mb
--- call =gams trnsport lo=2 gdx=trnsport
--- testgdx2dbf.gms(8) 2 Mb
--- call =gdx2dbf trnsport.gdx
GDX2DBF Version 1.0
i. Write i.dbf 0.00 seconds
j. Write j.dbf 0.00 seconds
a. Write a.dbf 0.00 seconds
b. Write b.dbf 0.00 seconds
d. Write d.dbf 0.00 seconds
f. Added to table ScalarParameter
c. Write c.dbf 0.00 seconds
x. Write x.dbf 0.00 seconds
z. Added to table ScalarVariable
cost. Added to table ScalarEquation
supply. Write supply.dbf 0.00 seconds
demand. Write demand.dbf 0.00 seconds
Total elapsed time: 0.02 seconds
--- testgdx2dbf.gms(11) 2 Mb
--- call =gamslib indus89
Model indus89.gms retrieved
--- testgdx2dbf.gms(12) 2 Mb
--- call =gams indus89 rf=indus89 lo=2 gdx=indus89
--- testgdx2dbf.gms(13) 2 Mb
--- call =gdx2dbf indus89.gdx indus89.ref
GDX2DBF Version 1.0
Reading 281 symbols, sorting: 0.00 seconds
Reading indus89.ref: 0.00 seconds
z. Write z.dbf 0.00 seconds
pv. Write pv.dbf 0.00 seconds
pv1. Write pv1.dbf 0.00 seconds
pv2. Write pv2.dbf 0.00 seconds
pvz. Write pvz.dbf 0.00 seconds
cq. Write cq.dbf 0.00 seconds
cc. Write cc.dbf 0.00 seconds
c. Write c.dbf 0.00 seconds
cf. Write cf.dbf 0.00 seconds
cnf. Write cnf.dbf 0.00 seconds
t. Write t.dbf 0.00 seconds
s. Write s.dbf 0.00 seconds
w. Write w.dbf 0.00 seconds
g. Write g.dbf 0.00 seconds
gf. Write gf.dbf 0.00 seconds
gs. Write gs.dbf 0.00 seconds
t1. Write t1.dbf 0.00 seconds
r1. Write r1.dbf 0.00 seconds
dc. Write dc.dbf 0.00 seconds
sa. Write sa.dbf 0.00 seconds
wce. Write wce.dbf 0.00 seconds
m1. Write m1.dbf 0.00 seconds
m. Write m.dbf 0.00 seconds
wcem. Write wcem.dbf 0.00 seconds
sea. Write sea.dbf 0.00 seconds
seam. Write seam.dbf 0.00 seconds
sea1. Write sea1.dbf 0.00 seconds
sea1m. Write sea1m.dbf 0.00 seconds
ci. Write ci.dbf 0.00 seconds
p2. Write p2.dbf 0.00 seconds
a. Write a.dbf 0.00 seconds
ai. Write ai.dbf 0.00 seconds
q. Write q.dbf 0.00 seconds
nt. Write nt.dbf 0.00 seconds
is. Write is.dbf 0.00 seconds
ps. Write ps.dbf 0.00 seconds
isr. Write isr.dbf 0.00 seconds
baseyear. Added to table ScalarParameter
land. Write land.dbf 0.16 seconds
tech. Write tech.dbf 0.02 seconds
bullock. Write bullock.dbf 0.06 seconds
labor. Write labor.dbf 0.14 seconds
water. Write water.dbf 0.11 seconds
tractor. Write tractor.dbf 0.05 seconds
sylds. Write sylds.dbf 0.03 seconds
fert. Write fert.dbf 0.02 seconds
fertgr. Write fertgr.dbf 0.00 seconds
natyield. Write natyield.dbf 0.00 seconds
yldprpv. Write yldprpv.dbf 0.00 seconds
yldprzs. Write yldprzs.dbf 0.02 seconds
yldprzo. Write yldprzo.dbf 0.00 seconds
growthcy. Write growthcy.dbf 0.00 seconds
weedy. Write weedy.dbf 0.00 seconds
graz. Write graz.dbf 0.00 seconds
yield. Write yield.dbf 0.03 seconds
growthcyf. Write growthcyf.dbf 0.00 seconds
iolive. Write iolive.dbf 0.02 seconds
sconv. Write sconv.dbf 0.00 seconds
repco. Added to table ScalarParameter
gr. Added to table ScalarParameter
growthq. Added to table ScalarParameter
bp. Write bp.dbf 0.00 seconds
cnl. Write cnl.dbf 0.00 seconds
pvcnl. Write pvcnl.dbf 0.00 seconds
gwfg. Write gwfg.dbf 0.02 seconds
comdef. Write comdef.dbf 0.03 seconds
subdef. Write subdef.dbf 0.00 seconds
zsa. Write zsa.dbf 0.00 seconds
gwf. Write gwf.dbf 0.00 seconds
carea. Write carea.dbf 0.02 seconds
evap. Write evap.dbf 0.02 seconds
rain. Write rain.dbf 0.03 seconds
divpost. Write divpost.dbf 0.02 seconds
gwt. Write gwt.dbf 0.02 seconds
dep1. Write dep1.dbf 0.02 seconds
dep2. Write dep2.dbf 0.00 seconds
depth. Write depth.dbf 0.02 seconds
efr. Write efr.dbf 0.03 seconds
eqevap. Write eqevap.dbf 0.02 seconds
subirr. Write subirr.dbf 0.03 seconds
subirrfac. Write subirrfac.dbf 0.00 seconds
drc. Added to table ScalarParameter
the1. Added to table ScalarParameter
n. Write n.dbf 0.00 seconds
i. Write i.dbf 0.00 seconds
nc. Write nc.dbf 0.00 seconds
n1. Write n1.dbf 0.00 seconds
nn. Write nn.dbf 0.02 seconds
ni. Write ni.dbf 0.00 seconds
nb. Write nb.dbf 0.00 seconds
ncap. Write ncap.dbf 0.00 seconds
lloss. Write lloss.dbf 0.00 seconds
lceff. Write lceff.dbf 0.00 seconds
cd. Write cd.dbf 0.00 seconds
rivercd. Write rivercd.dbf 0.00 seconds
riverb. Write riverb.dbf 0.05 seconds
s58. Write s58.dbf 0.00 seconds
infl5080. Write infl5080.dbf 0.02 seconds
tri. Write tri.dbf 0.02 seconds
inflow. Write inflow.dbf 0.00 seconds
trib. Write trib.dbf 0.00 seconds
rrcap. Write rrcap.dbf 0.00 seconds
rulelo. Write rulelo.dbf 0.00 seconds
ruleup. Write ruleup.dbf 0.00 seconds
revapl. Write revapl.dbf 0.00 seconds
pow. Write pow.dbf 0.00 seconds
pn. Write pn.dbf 0.00 seconds
v. Write v.dbf 0.00 seconds
powerchar. Write powerchar.dbf 0.02 seconds
rcap. Write rcap.dbf 0.00 seconds
rep7. Write rep7.dbf 0.00 seconds
rep8. Write rep8.dbf 0.00 seconds
p3. Write p3.dbf 0.00 seconds
prices. Write prices.dbf 0.00 seconds
finsdwtpr. Write finsdwtpr.dbf 0.02 seconds
ecnsdwtpr. Write ecnsdwtpr.dbf 0.00 seconds
p1. Write p1.dbf 0.00 seconds
p11. Write p11.dbf 0.00 seconds
pri1. Write pri1.dbf 0.00 seconds
wageps. Write wageps.dbf 0.00 seconds
lstd. Added to table ScalarParameter
trcap. Added to table ScalarParameter
twcap. Added to table ScalarParameter
ntwucap. Added to table ScalarParameter
twefac. Added to table ScalarParameter
labfac. Added to table ScalarParameter
twutil. Write twutil.dbf 0.00 seconds
totprod. Write totprod.dbf 0.00 seconds
farmcons. Write farmcons.dbf 0.00 seconds
demand. Write demand.dbf 0.00 seconds
cowf. Added to table ScalarParameter
buff. Added to table ScalarParameter
elast. Write elast.dbf 0.02 seconds
growthrd. Write growthrd.dbf 0.00 seconds
consratio. Write consratio.dbf 0.00 seconds
natexp. Write natexp.dbf 0.00 seconds
explimit. Write explimit.dbf 0.00 seconds
explimitgr. Added to table ScalarParameter
exppv. Write exppv.dbf 0.00 seconds
expzo. Write expzo.dbf 0.00 seconds
sr1. Write sr1.dbf 0.00 seconds
g1. Write g1.dbf 0.00 seconds
zwt. Write zwt.dbf 0.00 seconds
eqevapz. Write eqevapz.dbf 0.00 seconds
subirrz. Write subirrz.dbf 0.00 seconds
efrz. Write efrz.dbf 0.02 seconds
resource. Write resource.dbf 0.02 seconds
cneff. Write cneff.dbf 0.00 seconds
wceff. Write wceff.dbf 0.02 seconds
tweff. Write tweff.dbf 0.03 seconds
cneffz. Write cneffz.dbf 0.00 seconds
tweffz. Write tweffz.dbf 0.00 seconds
wceffz. Write wceffz.dbf 0.02 seconds
fleffz. Write fleffz.dbf 0.00 seconds
canalwz. Write canalwz.dbf 0.00 seconds
canalwrtz. Write canalwrtz.dbf 0.02 seconds
gwtsa. Write gwtsa.dbf 0.02 seconds
gwt1. Write gwt1.dbf 0.00 seconds
ratiofs. Write ratiofs.dbf 0.00 seconds
ftt. Write ftt.dbf 0.00 seconds
res88. Write res88.dbf 0.02 seconds
croparea. Write croparea.dbf 0.00 seconds
growthres. Write growthres.dbf 0.00 seconds
orcharea. Write orcharea.dbf 0.00 seconds
orchgrowth. Write orchgrowth.dbf 0.00 seconds
scmillcap. Write scmillcap.dbf 0.00 seconds
cnl1. Write cnl1.dbf 0.02 seconds
postt. Write postt.dbf 0.00 seconds
protarb. Write protarb.dbf 0.00 seconds
psr. Write psr.dbf 0.00 seconds
psr1. Write psr1.dbf 0.00 seconds
z1. Write z1.dbf 0.00 seconds
cn. Write cn.dbf 0.00 seconds
ccn. Write ccn.dbf 0.02 seconds
qn. Write qn.dbf 0.00 seconds
ncn. Write ncn.dbf 0.00 seconds
ce. Write ce.dbf 0.00 seconds
cm. Write cm.dbf 0.00 seconds
ex. Write ex.dbf 0.00 seconds
techc. Write techc.dbf 0.00 seconds
tec. Write tec.dbf 0.00 seconds
big. Added to table ScalarParameter
pawat. Added to table ScalarParameter
pafod. Added to table ScalarParameter
divnwfp. Write divnwfp.dbf 0.00 seconds
rval. Write rval.dbf 0.00 seconds
fsalep. Write fsalep.dbf 0.00 seconds
pp. Added to table ScalarParameter
misc. Write misc.dbf 0.00 seconds
seedp. Write seedp.dbf 0.00 seconds
wage. Write wage.dbf 0.00 seconds
miscct. Write miscct.dbf 0.02 seconds
esalep. Write esalep.dbf 0.00 seconds
epp. Added to table ScalarParameter
emisc. Write emisc.dbf 0.00 seconds
eseedp. Write eseedp.dbf 0.00 seconds
ewage. Write ewage.dbf 0.00 seconds
emiscct. Write emiscct.dbf 0.00 seconds
importp. Write importp.dbf 0.00 seconds
exportp. Write exportp.dbf 0.00 seconds
wnr. Write wnr.dbf 0.11 seconds
tolcnl. Added to table ScalarParameter
tolpr. Added to table ScalarParameter
tolnwfp. Added to table ScalarParameter
beta. Write beta.dbf 0.02 seconds
alpha. Write alpha.dbf 0.00 seconds
betaf. Added to table ScalarParameter
p. Write p.dbf 0.00 seconds
pmax. Write pmax.dbf 0.02 seconds
pmin. Write pmin.dbf 0.00 seconds
qmax. Write qmax.dbf 0.00 seconds
qmin. Write qmin.dbf 0.02 seconds
incr. Write incr.dbf 0.00 seconds
ws. Write ws.dbf 0.08 seconds
rs. Write rs.dbf 0.08 seconds
qs. Write qs.dbf 0.08 seconds
endpr. Write endpr.dbf 0.09 seconds
cps. Added to table ScalarVariable
acost. Write acost.dbf 0.00 seconds
ppc. Write ppc.dbf 0.00 seconds
x. Write x.dbf 0.06 seconds
animal. Write animal.dbf 0.00 seconds
prodt. Write prodt.dbf 0.02 seconds
proda. Write proda.dbf 0.00 seconds
import. Write import.dbf 0.00 seconds
export. Write export.dbf 0.00 seconds
consump. Write consump.dbf 0.02 seconds
familyl. Write familyl.dbf 0.02 seconds
hiredl. Write hiredl.dbf 0.00 seconds
itw. Write itw.dbf 0.00 seconds
tw. Write tw.dbf 0.02 seconds
itr. Write itr.dbf 0.00 seconds
ts. Write ts.dbf 0.00 seconds
f. Write f.dbf 0.03 seconds
rcont. Write rcont.dbf 0.03 seconds
canaldiv. Write canaldiv.dbf 0.02 seconds
cnldivsea. Write cnldivsea.dbf 0.02 seconds
prsea. Write prsea.dbf 0.00 seconds
tcdivsea. Write tcdivsea.dbf 0.00 seconds
wdivrz. Write wdivrz.dbf 0.00 seconds
slkland. Write slkland.dbf 0.00 seconds
slkwater. Write slkwater.dbf 0.02 seconds
artfod. Write artfod.dbf 0.00 seconds
artwater. Write artwater.dbf 0.00 seconds
artwaternd. Write artwaternd.dbf 0.02 seconds
nat. Write nat.dbf 0.09 seconds
natn. No data.
objz. Added to table ScalarEquation
objzn. Added to table ScalarEquation
objn. Added to table ScalarEquation
objnn. Added to table ScalarEquation
cost. Write cost.dbf 0.00 seconds
conv. Write conv.dbf 0.02 seconds
demnat. Write demnat.dbf 0.00 seconds
demnatn. No data.
ccombal. Write ccombal.dbf 0.02 seconds
qcombal. Write qcombal.dbf 0.00 seconds
consbal. Write consbal.dbf 0.02 seconds
laborc. Write laborc.dbf 0.00 seconds
fodder. Write fodder.dbf 0.00 seconds
protein. Write protein.dbf 0.00 seconds
grnfdr. Write grnfdr.dbf 0.00 seconds
bdraft. Write bdraft.dbf 0.00 seconds
brepco. Write brepco.dbf 0.00 seconds
bullockc. No data.
tdraft. Write tdraft.dbf 0.00 seconds
trcapc. Write trcapc.dbf 0.02 seconds
twcapc. Write twcapc.dbf 0.00 seconds
landc. Write landc.dbf 0.02 seconds
orchareac. Write orchareac.dbf 0.00 seconds
scmillc. No data.
waterbaln. Write waterbaln.dbf 0.00 seconds
watalcz. Write watalcz.dbf 0.02 seconds
subirrc. Write subirrc.dbf 0.00 seconds
nbal. Write nbal.dbf 0.03 seconds
watalcsea. Write watalcsea.dbf 0.00 seconds
divsea. Write divsea.dbf 0.00 seconds
divcnlsea. Write divcnlsea.dbf 0.02 seconds
watalcpro. Write watalcpro.dbf 0.00 seconds
prseaw. Write prseaw.dbf 0.00 seconds
nwfpalc. Write nwfpalc.dbf 0.00 seconds
Total elapsed time: 2.75 seconds
--- testgdx2dbf.gms(13) 2 Mb
--- Starting execution - empty program
*** Status: Normal completion
--- Job testgdx2dbf.gms Stop 08/24/07 15:44:34 elapsed 0:00:04.282



A large single symbol can be generated as follows:




$ontext

Test of GDX2DBF. Dumps a large symbol (a million elements)
to a DBF file.

$offtext

set i /i1*i1000/;
alias (i,j);
parameter p(i,j);
p(i,j) = uniform(-100,100);
execute_unload 'test.gdx',p;

execute '=gdx2dbf test.gdx';


For this example, gdx2dbf is a little bit slower than we would like:



--- Job gdx2dbf.gms Start 08/26/07 22:45:18
GAMS Rev 148 Copyright (C) 1987-2007 GAMS Development. All rights reserved
Licensee: Erwin Kalvelagen G070509/0001CE-WIN
GAMS Development Corporation DC4572
--- Starting compilation
--- gdx2dbf.gms(14) 3 Mb
--- Starting execution
--- gdx2dbf.gms(14) 28 Mb
GDX2DBF Version 1.0
p. Write p.dbf 41.78 seconds
Total elapsed time: 41.81 seconds
*** Status: Normal completion
--- Job gdx2dbf.gms Stop 08/26/07 22:46:00 elapsed 0:00:42.468