Converting Microsoft Access MDB Into CSV Or MySQL In Linux

I have recently had reason to convert an Access MDB file to CSV for use in a mysql database. I don't like the idea of an Access database on a production server and Microsoft has been agreeing since 1999.

As it turns out it is actually very easy, there is GPL software available for the job at http://mdbtools.sourceforge.net/. If you are using Ubuntu or Debian you can use apt-get install the mdbtools package.

To get the list of tables, you run the following command:

mdb-tables database.mdb

You can then get a CSV version for each table using:

mdb-export database.mdb table_name

You can also convert the mdb into a format required by MySQL. First you must get the put the table schema into the database using the following command:

mdb-schema database.mdb | mysql -u username -p database_name

You then import each table by running:

mdb-export -I database.mdb table_name | sed -e 's/)$/)\;/' | mysql -u username -p database_name

Sed is required as mdb-export doesn't put a semi-colon at the end of each insert statement,  which MySQL definately doesn't like.

After running this, you can now be rid of the horror that are Access MDB files :)