87

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.

Fionnuala
  • 90,370
  • 7
  • 114
  • 152
700 Software
  • 85,281
  • 83
  • 234
  • 341

4 Answers4

72

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.

majom
  • 7,863
  • 7
  • 55
  • 88
Teson
  • 6,644
  • 8
  • 46
  • 69
  • 3
    update: the've crippled down the free version. search&replace works for smaller migrations though.. – Teson May 27 '12 at 03:00
  • 7
    It works for the new .accdb Access format by the way – golimar Oct 21 '14 at 09:26
  • 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. – cool Jun 23 '16 at 12:51
  • 3
    If you have 64 bit OS, you will need a 32 bit ODBC driver. https://dev.mysql.com/downloads/connector/odbc/ – blackandorangecat Mar 06 '17 at 16:42
  • @blackandorangecat I got around that by saving the Access file as a `*.mdb` (Access 2000-2003) file via the "Save and Publish" menu. – dthor Oct 06 '17 at 15:54
  • 1
    FYI: [This is being discussed on meta.](https://meta.stackoverflow.com/q/407659/2873538) – Ajeet Shah May 14 '21 at 16:47
55

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.

Nicolay77
  • 2,085
  • 25
  • 20
  • 1
    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. – white_gecko Nov 13 '14 at 20:11
  • 4
    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. – Steve Almond Nov 18 '15 at 21:44
  • 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!! – Gerardo Camacho Jun 20 '18 at 17:02
  • 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... – cjolley Jul 03 '18 at 20:36
  • `{ echo ‘set autocommit=0;’; mdb-export ... ;echo ‘commit;’ } | mysql ` may be faster – Martin Wang Mar 22 '19 at 15:00
  • 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](https://github.com/lsgunth/mdbtools-win) project. – Adam Burke Jun 19 '22 at 07:17
44

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
mikkom
  • 3,521
  • 5
  • 25
  • 39
  • Thanks, @mikkom and @Nicolay77! I created a Gist after copy-paste from here to CLI was a pain: https://gist.github.com/togume/83b4bf40e1528742374bbce338270f34 – togume Nov 05 '17 at 02:57
  • Can you convert this script into batch script? – muneeb_ahmed Feb 16 '18 at 04:03
  • 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] - https://gist.github.com/ahmedelq/194718e6df95402663cb31a2be1ac212 [2] - https://stackoverflow.com/a/74457357/1398096 – Shane McCarthy May 11 '23 at 21:43
13

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)

KetZoomer
  • 2,701
  • 3
  • 15
  • 43
Egor Guriyanov
  • 163
  • 2
  • 7
  • 4
    Newer versions of macOS/Homebrew the command is `brew install mdbtools` – Toby Mellor Jun 08 '17 at 17:40
  • mdbtools does not properly escape slashes unfortunately and does not appear to be maintained: https://github.com/brianb/mdbtools/issues/89 – John Mellor Mar 07 '19 at 15:12
  • 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. – Pathros Feb 23 '21 at 18:54