Back

DbasePsql

Convert .dbf files into PostgreSQL

This document provides a step by step instruction how to convert DBase II, DBase III+, DBase IV and (to some extent) FoxProV2 files to PostgreSQL.

Update! New version extended and modified by Sebastian Baumhekel is described below.

This solution is based on excellent Simple DBase SQL by Michael Lecuyer. I used version 2.03 - check there for upgrades. In case it's not available in its original location, I put it here.

I added just two classes and package them along with Michael's tool into single JAR file for simplicity.

How to use it

This is Java based solution so you need Java JDK installed. Dowload it from http://java.sun.com/downloads. Installation is simple, after installing add some environment variables:
JAVA_HOME - should point directory where JDK is installed
Add entry to PATH:
Linux: $JAVA_HOME/bin
Windows: %JAVA_HOME%\bin

Download zipped files and unpack them somewhere, e.g.:
C:\Java\dbasepsql - under Windows
/home/java/dbasepsql - undex Linux

Go to this directory and execute dbasepsql.jar:

java -jar dbasepsql.jar

Check psql subdirectory. Two files were created there:
FishFP2_cr.sql - CREATE TABLE statement
FishFP2_in.sql - data import in form of COPY

You may modify parameters in dbasepsql.properties, you may also pass another property file as a parameter:

java -jar dbasepsql.jar mydbasepsql

where mydbasepsql.properties contains your parameters.

Under Windows you may run it simply like that:

C:\Java\dbasepsql> dbasepsql.jar

or even double click on dbasepsql.jar. However .jar files are associated with javaw.exe instead of java.exe. There is no associated console window with javaw.exe. This implies you can't see any output messages indicating progress.

Some details

Contents of .zip file:

File/Dir Description
changed_files I've modified DBase.java to treat space as a false value in logical column and added a constructor with specified Locale to handle decimal separator properly. DBase.java is a new version, DBase_old.java is the original file.
docs DBasePsql API
example Example of .dbf file
psql Empty directory for exported files
src Sources
dbasepsql.jar JAR file: original dbase.jar + DbasePsql
dbasepsql.properties Resource bundle with parameters for DbasePsql

dbasepsql.properties

Name Description
dbasepsqlInputDir Directory where .dbf files are located
dbasepsqlOutputDir Directory for .sql files
dbasepsqlTable If defined then this table only will be converted
otherwise all tables from input directory
dbasepsqlMemoHandler Memo file handler
possible values: FOXPRO2MemoHandler, DBASEIIIMemoHandler, DBASEIVMemoHandler
dbasepsqlDbEncoding Database encoding (if undefined then iso8859-1)
dbasepsqlEuropeanDec Used for determining decimal separator
yes - ','
no - '.'
dbasepsqlFileEncoding Output file encoding (if undefined then iso8859-1)
old_table_name=new_table_name Table rename in form: old_table_name=new_table_name (case sensitive!)
old_table.old_field_name=new_field_name Field rename in form: old_table.old_field_name=new_field_name (case sensitive!)

Codepage conversion

In many cases .dbf files use some old encoding, eg. Mazovia, which is not supported by Java. I'm not sure what's happening when .dbf file is opened by DBase.java using default system encoding. I made conversion from Mazovia to windows-1250 in DbasePsql.convertCodePage() and it is working under Windows with default codepage windows-1250.

If you have problems with conversion then you might modify DbasePsql.convertCodePage() or avoid conversion in DbasePsql and do it afterwards on *_in.sql files. To make sure that files are not converted:

License

DBasePsql package converts dBase to PostgreSQL
Copyright (C) 2006 Tomasz Judycki, www.tv.com.pl

This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation; either version 2 of the License, or (at your option) any later version.

This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.

Enjoy!

Tomasz Judycki



Extended and Modified by Sebastian Baumhekel (C) 2007

+runs without properties file (with default values)
+uses inserts in sql script (works for me on postgres 8.1.5)
configurable via properties file: dbasepsqlUseInsert
+generates more verbose output about parameters and converted tables
+no errors are written into sql files, they go to standard out
+conversion is done via java-api (support for all standard encodings)
+table filenames are mytable_table.sql
+data filenames are mytable_data.sql

Download