Converting Microsoft Access MDB Into CSV Or MySQL In Linux

| 13 Comments

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 :)

13 Comments

Thanks, great stuff!

Ed,

Glad you found it useful.

i really don't get it, everytime i try to export a db i get this
mdb-schema Northwind.mdb | mysql NorthWind
ERROR 1051 (42S02) at line 9: Unknown table 'Categories'
which is the first table
i've also tried using the annoying gui tools to export as mysql but it is always the same

Hi nic,

I think I see what the problem might be. Before the CREATE TABLE statement there is a DROP TABLE which causes the error. To get stop the error, change the command line to:

mdb-schema Northwind.mdb | grep -v ^DROP | mysql NorthWind

thanx Niall
this is now what i'm getting:
mdb-schema Northwind.mdb | grep -v ^DROP | mysql NorthWind
ERROR 1064 (42000) at line 9: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Integer,
CategoryName Text (30),
Description Memo/Hyperlink (255),
Pic' at line 3

also i tried using this download:
http://www.flash-remoting.com/examples/frdg/northwindmysql.zip
and it actually works, but when i go to view the tables w/ select i get a bunch of binary garbage and when i exit out of the mysql shell my bash is still using binary garbage... interesting lol

i have a lab test due on sunday and one of the questions is:
"How many Orders meet the following requirements?
a. EmployeeID = 4
b. And ShipCountry ='Germany'

i can view the tables using mdbviewer and see that there will be alot to sift thru using my eyes... can't run any sql in it... maybe i'll just take a wild guess
most of this lab test is just ask me what sql command should i execute to find x, which is real easy, but running them eh...
but thanx again, i'll try to play around with your suggested command

found a workaround for my situation
using the mysql version i downloaded that was producing garbage output, i tried opening the DB with MYSQL Querry Browser
(a gui mysql client), and it works like a charm
hope this helps anyone experiencing simular problems, providing they can find a mysql version of the DB
also i'm gonna try to convert them on dapper (much more stable than what i'm running now)

Nic,

I somehow managed to miss your previous reply. Glad to hear that you got it up and running :)

For a bog standard install of MySQL5 on Ubuntu mdb-export generates incompatible DDL for Integer columns - it outputs only "int" when you actually need e.g. "int(10)" or "int(11)".

Possibly there is some clever grep/sed workaround or a setting that can be changed on the MySQL server, I don't know.

Will,

If you can give me an example of the access DB in question, I will quite happily have a look.

Niall.

This may vary with versions of mdbtools, but I've found that to generate proper DDL output from the mdb-schema command you should specify the target dialect on the command line, in this case it would be:

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

Other dialects supported are access, sybase, oracle and postgres.

can someone explain how to use mdbtools? i installed, then from terminal command line, the command for example: mdb-tables database.mdb [replacing database.mdb with my access db, even tried the pathname included] only returns with "Can't allocate filename; Can't open database." how am i supposed to use mdbtools? thank you.

What worked for me to get the schema into mysql:

mysqladmin create newdatabase

mdb-schema database.mdb postgres \
| sed "s/Int8/int/" \
| sed "s/Char /varchar/" \
| sed "s/^-/#/" \
| grep -v "^DROP" \
| mysql newdatabase

exporting as progres (since mysql is not an option);
the first sed turns Int8 into int (which becomes int(11);
the second sed turns Char into varchar;
the third sed turns postgres comments into mysql comments;
the grep drops the DROP statement as discussed earlier in this topic;

It seems to work quite well. Of course, if you have other data types then Int8 and Char, you might want to add some sed magic of your own.

About this Entry

This page contains a single entry by Niall Donegan published on March 10, 2007 3:55 PM.

GAA Supporting Local Business? was the previous entry in this blog.

Graphing Rbldnsd Stats With MRTG is the next entry in this blog.

Find recent content on the main index or look in the archives to find all content.

Pages

OpenID accepted here Learn more about OpenID
Creative Commons License
This blog is licensed under a Creative Commons License.
Powered by Movable Type 4.34-en