MySQL & Microsoft Access Databases (mdb)

Update 12/17: There's a Perl script controller here that will export as an SQL file. It handles converting between each databases datatypes.

After a quick search for a way to import an MDB into MySQL didn't return anything I liked. I remembered I have a Ubuntu computer now & started looking for the pieces instead.

What I ended up doing boils down to this.

1. Install mdbtools
2. Determine table names
3. Export table to CSV
4. Determine column names & required data types
5. Create tables in MySQL
6. LOAD DATA INFILE
7. PROCEDURE ANALYSE()
8. Tweak data types

The first thing I found was a package called mdbtools. The package includes some MDB command line utilities. So far this has been usefull for extracting database tables from MDB files in CSV format.

As usual the package was easy to install from the command line.
sudo apt-get install mdbtools

I needed to use two of the utilities to extract my tables. The first one is mdb-tables which lists the names of the tables in the MDB.

me@box:/mdb$ mdb-tables database.mdb
table_one table_two

Now that I knew the exact table names I was able to use the second utility, mdb-export, to export the tables.

me@box:/mdb$ mdb-export
Usage: mdb-export [options] <file> <table>
where options are:
  -H             supress header row
  -Q             don't wrap text-like fields in quotes
  -d <delimiter> specify a column delimiter
  -R <delimiter> specify a row delimiter
  -I             INSERT statements (instead of CSV)
  -D <format>    set the date format (see strftime(3) for details)
  -S             Sanitize names (replace spaces etc. with underscore)
  -Q <char>      Use <char> to wrap text-like fields. Default is ".
  -X <char>      Use <char> to escape quoted characters within a field. Default is doubling.
me@box:/mdb$ mdb-export -X \ -H database.mdb table_one > table_one.csv

Now it's worth noteing the first time I didn't think to tell the output where to go and I ended up with hundreds of table rows scrolling across my screen until I pressed CTRL+C to hault it.

From there I opened the CSV file in Notepad2 and found out the column names by looking at the first line in the file.
I created a new database table with the largest possible data types in MySQL using phpMyAdmin.

Once I had my database table created, I used a LOAD DATA INFILE to import the CSV file into the table.

LOAD DATA
INFILE '/mdb/table_one.csv'
INTO TABLE database.table_one
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\\'
IGNORE 1 LINES

After a few seconds I had a table full of data. I used phpMyAdmins' Propose table structure, which runs the following query.

SELECT * 
FROM `table_one` 
PROCEDURE ANALYSE()

Using the info provided by that, and a little planning for what will happen with the database later, I was able to tweak the table a little so that it doesn't need to use the largest possible data types for columns. I just wanted to make sure I didn't truncate anything during the import.

It might be worth rigging this to work with the phpMyAdmin import controll panel, but I'll save that for another day.