Wednesday, January 07, 2009

Convert MDB file in Linux

I have some old, standalone Java projects which used Access MDB file as database. As I have always wanted to use an installation-free database for my application, I was looking for ways to convert the data in the MDB file to HSQLDB. I choose HSQLDB because it's simple to use and cross platform.

I started off researching with unixODBC but ended up installing odbcinst. Eventually I stumbled upon mdbtools. That solves my problem. After installating mdbtools on my Ubuntu laptop, I exported the schema

mdb-schema data.mdb > schema.sql

I edited the schema to HSQLDB syntax and created the new database in HSQLDB.

Then, I only need to do

mdb-export -I data.mdb table | sed -e "s/)$/);/" > table.sql

for each of the tables in the MDB file. The sed command is to append a ';' as mdb-export doesn't end the insert statements with ';'.

I then edit the INSERT statements and run them in HSQLDB.

This is simpler than most of the other approaches I have read on the web.

No comments: