Small blog about system administration.

MDB to MySQL conversion in Linux.

Install package mdbtools that has mdb-schema and mdb-export,

The mdb-schema can be use to see schema.
The mdb-tables  can be used to see tables in it

/usr/bin/mdb-array
/usr/bin/mdb-export
/usr/bin/mdb-header
/usr/bin/mdb-hexdump
/usr/bin/mdb-parsecsv
/usr/bin/mdb-prop
/usr/bin/mdb-schema
/usr/bin/mdb-sql
/usr/bin/mdb-tables
/usr/bin/mdb-ver

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


Script to convert .mdb file to MySQL:
===========================


$ cat ~/bin/mdb2mysql.sh
#!/bin/sh


if [ $# -ne 4 ]; then
echo "usage: $0 msaccess.mdb mysqldb mysqluser mysqlpass"
exit
fi

MDB_FILE=$1
MYSQL_DBNAME=$2
MYSQL_USER=$3
MYSQL_PASS=$4

MYSQL_IMPORT=/tmp/`basename $MDB_FILE .mdb`.sql

>$MYSQL_IMPORT

# create database
echo "DROP DATABASE IF EXISTS $MYSQL_DBNAME;" >> $MYSQL_IMPORT
echo "CREATE DATABASE $MYSQL_DBNAME; " >> $MYSQL_IMPORT
echo "USE $MYSQL_DBNAME; " >> $MYSQL_IMPORT

# import table structures with mysql data types
mdb-schema -S $MDB_FILE mysql >> $MYSQL_IMPORT

perl -p -i -e 's/-----*/--/g' $MYSQL_IMPORT
perl -p -i -e 's/DROP TABLE (.*)/DROP TABLE IF EXISTS $1/gi' $MYSQL_IMPORT

# Fix the Variables
#perl -p -i -e 's/Text/VARCHAR/g' $MYSQL_IMPORT
#perl -p -i -e 's/Long Integer/INT\(11\)/g' $MYSQL_IMPORT

# import data
for TABLE in `mdb-tables $MDB_FILE`
do
mdb-export -R';' -I $MDB_FILE  $TABLE >> $MYSQL_IMPORT

done

mysql -u$MYSQL_USER -p$MYSQL_PASS < $MYSQL_IMPORT

if [ $? -ne 0 ]; then
echo ""
echo "Fix the script at $MYSQL_IMPORT"
echo ""
echo "Run it using following command"
echo "mysql -u$MYSQL_USER -p$MYSQL_PASS < $MYSQL_IMPORT"
else
echo ""
echo "DONE. Script used is: $MYSQL_IMPORT"
echo ""
echo "Remove it if you no longer need it"
fi
$

Example:
=======


$ mdb2mysql.sh
usage: mdb2mysql.sh msaccess.mdb mysqldb mysqluser mysqlpass
$


$ mdb2mysql.sh PayRoll.mdb payroll root root

DONE. Script used is: /tmp/PayRoll.sql

Remove it if you no longer need it
$

ACCESS ISSUES:
============

MS Access allows +, - and % also in the column name. Such things can cause confusion.... the tool replaces + and - with just underscore...

If you had fields like Adj+ and Adj-, both will be named as Adj_ , this is a problem.

The mdb-schema will replace Discount% with Discount_ but INSERT INTO queries may still have DISCOUNT%.

Be careful.

No comments :

Post a Comment