How can I convert an MDB (Access) file to MySQL (or plain SQL file)?

Question

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.

Sturtevant

Answer 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.

Audiphone

update: the've crippled down the free version. search&replace works for smaller migrations though.. – Audiphone

It works for the new .accdb Access format by the way – Midweek

Just 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. – Ninnetta

If you have 64 bit OS, you will need a 32 bit ODBC driver. dev.mysql.com/downloads/connector/odbc – Septima

@Septima I got around that by saving the Access file as a *.mdb (Access 2000-2003) file via the "Save and Publish" menu. – Turbo

FYI: This is being discussed on meta. – Bosco

Answer 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.

Plutocracy

Thanks a million for that script. Just two hints, I guess you can remove the AWK and GREP vars and I've added the options -b strip -H to mdb-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. – Inquest

I'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. – Nosewheel

Awesome! 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!! – Franke

Hi... 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... – Kaisership

{ echo ‘set autocommit=0;’; mdb-export ... ;echo ‘commit;’ } | mysql may be faster – Wakeless

Thanks 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. – Purlin

Answer 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

Blayze

Thanks, @Blayze and @Nicolay77! I created a Gist after copy-paste from here to CLI was a pain: gist.github.com/togume/83b4bf40e1528742374bbce338270f34 – Demean

Can you convert this script into batch script? – Kaifeng

Thank you for this, it pointed me in the right direction. I used the script below [1] to export to CSV files, although I ran into some issues with xargs on OSX and found this soution [2] [1] - gist.github.com/ahmedelq/194718e6df95402663cb31a2be1ac212 [2] - https://mcmap.net/q/242630/-how-do-i-work-around-macos-x-not-having-xargs-d – Torchwood

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

Patriapatriarch

Newer versions of macOS/Homebrew the command is brew install mdbtools – Factotum

mdbtools does not properly escape slashes unfortunately and does not appear to be maintained: github.com/brianb/mdbtools/issues/89 – Siphonophore

As @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. – Dolley