Migrating Access MDB to MySQL


A recent task needed me to import a large amount of data from a Access MDB database to MySQL. My first choice for the job was the mdb-tools set of utilities. mdb-tools provides a set of tools and applications to export MDB data and schema to other databases such as MySQL, Oracle, Sybase, PostgreSQL, and others.

mdb-tools is available for Linux systems, and as I use Ubuntu, installation was a breeze using the package manager. To install is from the shell use the following.

Installing mdbtools

sudo apt-get install mdbtools

The following command lines tools are available for migrating a MDB to a different database.

Name Description
mdb-tables list tables in the specified file
mdb-schema generate schema DDL for the specified file
mdb-export generate CSV style output for a table

Just to check if the correct tables are in the Access database, we will use the ‘mdb-tables’ command which will list the table names in the database.

mdb-tables database.mdb

Exporting schema

Once we are satisfied that the appropriate tables are there we can generate a schema to be used with MySQL. For this we will use the ‘mdb-schema’ command. mdb-schema produces data definition language output for the given database. This can be further passed to another database engine (MySQL, Oracle, Sybase, PostgreSQL etc) to create a replica of the original access table format.

mdb-schema database.mdb  > schema.sql

The above will generate the schema for all the tables in the ‘database.mdb’ database and save it to the schema.sql file. If you want to output the schema for a particular table rather than all the tables, you will need to use the ‘-T’ flag. The ‘-T’ flag will restrict the schema for a particular table.

mdb-schema -T table_name database.mdb > schema.sql

Rather than saving the schema to a file we can directly import it into MySQL. The following will directly create a MySQL table using the schema from the MDB database by piping the output of mdb-schema to MySQL.

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

Exporting data

mdb-tools lets you export the data in two formats – CSV or SQL. To export the data in a CSV format use the following command. Note that you need to specify the table name from which the data will be exported.

mdb-export database.mdb table_name > export.csv

You can export the data as SQL ‘INSERT’ statements you need to use the ‘-I’ flag.

mdb-export -I database.mdb table_name > export.sql

But there is one problem here. All the export statements end with a newline and not a semi-colon as MySQL expects, so we need to add one. The following line shows how with the help of the ‘-R’ flag. This flag lets you specify a row delimiter.

mdb-export -I -R ';' database.mdb table_name > export.sql

This will now end all INSERT statements with a semi-colon instead of a newline. This is fine for small data, but for a huge number of INSERT statements this can be a problem as all the statements are now on one single line ( the newlines are replaced by the semi-colon). To solve this problem we will need to use ‘sed’ to insert the semi-color before the ending newline and the closing parenthesis of the INSERT statement. This is shown below.

mdb-export -I database.mdb table_name | sed -e 's/)$/)\;/' > export.sql

Using Toad for MySQL to import CSV

For my data import job I needed to import a select few columns from a particular table to MySQL. mdb-export does not allow me to select columns from a table, so I needed to find another way to import the data to MySQL. As I already had Toad MySQL installed I decided to use it.

First I exported the data from the MDB table to a CSV file and then used the Toad MySQL importer to import particular columns from the CSV file to my new MySQL database. The Toad importer lets you map columns from the CSV file to the MySQL schema as shown below.


Click to enlarge

For large files the Toad importer can take a while to complete. If you have a Toad version prior to 6.0 then upgrade it to the latest version. On my previous version of 5.x the importer used to crash before writing the imported data to the disk; the bug seems to have been resolved in the new version.

This site is a digital habitat of Sameer Borate, a freelance web developer working in PHP, MySQL and WordPress. I also provide web scraping services, website design and development and integration of various Open Source API's. Contact me at metapix[at]gmail.com for any new project requirements and price quotes.

3 Responses

1

wpintent

November 22nd, 2012 at 8:27 am

nice tutorial. but how can i do this if server is shared where i can not install MDB tools!

Thanks

2

-jha-

February 23rd, 2013 at 5:10 pm

is there a way to tell mdb-export when generating insert-statements to quote the field names with “‘” in case there some with “-” charcters in the name?

3

Richard Graham

June 12th, 2014 at 12:08 pm

There’s an update to the MDB Tools (I assume it’s an update anyway) – in both the mdb-schema and mdb-export tools you need to put ‘mysql’ in there so it knows to output in MySQL format, so the codes will be:

mdb-schema database.mdb mysql > schema.sql

and:

mdb-export -I mysql database.mdb table_name > export.sql

Your thoughts

Sign up for fresh content in your email