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.
sudo apt-get install mdbtools
The following command lines tools are available for migrating a MDB to a different database.
|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.
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
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
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.
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.