How can I convert an MDB (Access) file to MySQL (or plain SQL file)?
Solution 1
You want to convert mdb to mysql (direct transfer to mysql or mysql dump)?
Try a software called Access to MySQL.
Access to MySQL is a small program that will convert Microsoft Access Databases to MySQL.
- Wizard interface.
- Transfer data directly from one server to another.
- Create a dump file.
- Select tables to transfer.
- Select fields to transfer.
- Transfer password protected databases.
- Supports both shared security and user-level security.
- Optional transfer of indexes.
- Optional transfer of records.
- Optional transfer of default values in field definitions.
- Identifies and transfers auto number field types.
- Command line interface.
- Easy install, uninstall and upgrade.
See the aforementioned link for a step-by-step tutorial with screenshots.
Solution 2
If you have access to a linux box with mdbtools installed, you can use this Bash shell script (save as mdbconvert.sh):
#!/bin/bash
TABLES=$(mdb-tables -1 $1)
MUSER="root"
MPASS="yourpassword"
MDB="$2"
MYSQL=$(which mysql)
for t in $TABLES
do
$MYSQL -u $MUSER -p$MPASS $MDB -e "DROP TABLE IF EXISTS $t"
done
mdb-schema $1 mysql | $MYSQL -u $MUSER -p$MPASS $MDB
for t in $TABLES
do
mdb-export -D '%Y-%m-%d %H:%M:%S' -I mysql $1 $t | $MYSQL -u $MUSER -p$MPASS $MDB
done
To invoke it simply call it like this:
./mdbconvert.sh accessfile.mdb mysqldatabasename
It will import all tables and all data.
Solution 3
I modified the script by Nicolay77 to output the database to stdout (the usual way of unix scripts) so that I could output the data to text file or pipe it to any program I want. The resulting script is a bit simpler and works well.
Some examples:
./mdb_to_mysql.sh database.mdb > data.sql
./mdb_to_mysql.sh database.mdb | mysql destination-db -u user -p
Here is the modified script (save to mdb_to_mysql.sh)
#!/bin/bash
TABLES=$(mdb-tables -1 $1)
for t in $TABLES
do
echo "DROP TABLE IF EXISTS $t;"
done
mdb-schema $1 mysql
for t in $TABLES
do
mdb-export -D '%Y-%m-%d %H:%M:%S' -I mysql $1 $t
done
Solution 4
OSX users can follow by Nicolay77 or mikkom that uses the mdbtools utility. You can install it via Homebrew. Just have your homebrew installed and then go
$ brew install mdbtools
Then create one of the scripts described by the guys and use it. I've used mikkom's one, converted all my mdb files into sql.
$ ./to_mysql.sh myfile.mdb > myfile.sql
(which btw contains more than 1 table)
Related videos on Youtube
![700 Software](https://i.stack.imgur.com/s77Za.jpg?s=256&g=1)
700 Software
Join 700.social ! A happy medium between Facebook and Gab. :) The name is too long but the domain looks good. Also, Software Development / Consulting (423) 802-8971 700software.com old username: George Bailey (but now I use my real name) http://www.google.com/images?q=George+Bailey
Updated on July 05, 2022Comments
-
700 Software almost 2 years
Is it possible to create a Dump of SQL commands from a Microsoft Access database? I hope to convert this MDB file into a MySQL database for importing so I don't have to go through the CSV step.
I would expect even an MSSQL dump file to still contain workable SQL commands, but I know nothing of MSSQL, please let me know.
-
Teson about 12 yearsupdate: the've crippled down the free version. search&replace works for smaller migrations though..
-
golimar over 9 yearsIt works for the new .accdb Access format by the way
-
white_gecko over 9 yearsThanks a million for that script. Just two hints, I guess you can remove the
AWK
andGREP
vars and I've added the options-b strip -H
tomdb-export
because I had some strange OLE objects in mdb and because the headers are loaded before. I guess in some cases-b octal
would work as well but I had trouble with the binary data when loading it into mysql. -
Steve Almond over 8 yearsI'm importing tables with spaces in the name, and this didn't work for me. I had to add
IFS=$(echo -en "\n\b")
after the shebang. -
cool about 8 yearsJust add that, current version of the program do not operates properly (at least that is what i was able to conclude). It is not dumping file properly (causing a lot of errors when you want to import) and direct connect to the database (even on the localhost) is not working.
-
blackandorangecat over 7 yearsIf you have 64 bit OS, you will need a 32 bit ODBC driver. dev.mysql.com/downloads/connector/odbc
-
Toby Mellor about 7 yearsNewer versions of macOS/Homebrew the command is
brew install mdbtools
-
dthor over 6 years@blackandorangecat I got around that by saving the Access file as a
*.mdb
(Access 2000-2003) file via the "Save and Publish" menu. -
togume over 6 yearsThanks, @mikkom and @Nicolay77! I created a Gist after copy-paste from here to CLI was a pain: gist.github.com/togume/83b4bf40e1528742374bbce338270f34
-
muneeb_ahmed over 6 yearsCan you convert this script into batch script?
-
Gerardo Camacho about 6 yearsAwesome! managed to export MDB to MySQL on Mac. the only thing I stumbled was Table names with spaces, but fixed by putting: $MYSQL -u $MUSER -p$MPASS $MDB -e "DROP TABLE IF EXISTS `$t`" and mdb-export -D '%Y-%m-%d %H:%M:%S' -I mysql $1 "$t" | $MYSQL -u $MUSER -p$MPASS $MDB And, unfortunately, having to create an array with table names since 'for t in $TABLES' tokenizes by space. Thanks!!
-
cjolley almost 6 yearsHi... I'm trying to get this to work with a database that has % symbols in the names. I'm new to MySQL, but it looks like those are a wildcard for string matching. Any ideas on how to escape them?
\%
and\\%
aren't doing it for me... -
John Mellor over 5 yearsmdbtools does not properly escape slashes unfortunately and does not appear to be maintained: github.com/brianb/mdbtools/issues/89
-
Martin Wang over 5 years
{ echo ‘set autocommit=0;’; mdb-export ... ;echo ‘commit;’ } | mysql
may be faster -
Pathros over 3 yearsAs @JohnMellor mentions, I also get the following error:
ERROR at line xxxx: Unknown command '\"'.
I could solve it by replacing all the\""
occurrences for\"
and that did the trick. -
Ajeet Shah about 3 years
-
Adam Burke about 2 yearsThanks for this. For those on windows, I was able to get something going based on your script and the work by the champions at the mdbtools-win project.