Tag Archives: php

Import .dbf File Contents into MySql

I had a bunch of .dbf (source was Lotus Approach) files, at which I wanted to shoot SQL-like queries to generate some meaningful reports. So there were 2 tasks to be accomplished:

  1. Read the data into a tool or database and
  2. formulate and perform the queries to generate results to base your reports on.

As of opening and saving, I first tried MS Access. But some of the tables could not be imported (reasons unknown). Then I tried to open those files with MS Excel. It worked, but I had more than 65536 records per table, so I could not actually use Excel. After some investigation I came across dbftools which promised to help me converting the data into a text-based format.

I simply unpacked the C source and typed ‘make’ from the console and I had a working dbf2txt binary. I created a directory with all the dbf-files in it and hacked together the following simple shell script: convert.sh

#!/bin/bash
for i in data/*.dbf
do
dbf/src/dbf2txt -v "$i" > "$i.txt";
done

It supplied me with all txt-files each containing a table with fieldnames in line 1 and the data in tab-separated format. Nice!

To get this data into MySql, I hacked this little tab2sql script which converts the txt-files into SQL statements. It is called like this:

$ php tab2sql.php data/my_table.dbf.txt > sql/my_table.dbf.txt.sql;

You can then dump all sql-files in the sql-dir into MySql via:

$ for dir in data/*.sql; do mysql dbf_import -h localhost -u root < “${dir}”; done;

Thanks Frank Koormann for the dbftools!

Sidenote: If you have big tables and you plan to work with joins… Do not forget to set field-types and indices properly after the import! This could otherwise stress your machine.