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.mdbYou can then get a CSV version for each table using:
mdb-export database.mdb table_nameYou 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_nameYou then import each table by running:
mdb-export -I database.mdb table_name | sed -e 's/)$/)\;/' | mysql -u username -p database_nameSed 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 :)
0 TrackBacks
Listed below are links to blogs that reference this entry: Converting Microsoft Access MDB Into CSV Or MySQL In Linux.
TrackBack URL for this entry: http://blog.moybella.net/cgi-bin/mt-tb.cgi/24
Handy :)
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.