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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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    homebrew 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  gt  myfile sql    which btw contains more than 1 table

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

Examples related to mysql

Examples related to sql

Examples related to ms-access

Examples related to import

Examples related to dump