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