.mdb to mysql

---

Get the tool

sudo aptitude install mdbtools

List of tables

mdb-tables database.mdb

Get CSV file for each table

mdb-export database.mdb table_name

Create a database

mysql -u username --password=PASSWORD -e "create database database_name;"

Put Table Schema into Database

mdb-schema database.mdb mysql | sed "s/^-/#/" | grep -v ^DROP | mysql -u username --password=PASSWORD database_name 

Import each table

mdb-export -D "%F" -I database.mdb table_name | sed -e 's/)$/)\;/' | mysql -u username --password=PASSWORD database_name

Script to import all the tables at once

#!/bin/bash

mdb_filename='Parts-V4.mdb'
mysql_dbname='PV7'
mysql_username='DBSuperUser'
mysql_password='super_pass'

#Put table names into an array
array=(`mdb-tables ${mdb_filename}`)    

#Iterate over the array of table names
len=${#array[*]}
i=0
while [ $i -lt $len ]; do
echo Exporting ${array[$i]}...

# define date format, change backslashes to frontslashes and add semicolon before bringing into mysql
mdb-export -D "%F" -I ${mdb_filename} ${array[$i]} | sed -e 's/)$/)\;/' | sed -e 's/\\/\//g' | mysql -u ${mysql_username} --password=${mysql_password} ${mysql_dbname}

let i++
done

Script to do everything at once

#!/bin/bash
# import_mdb_to_mysql.sh

mdb_filename='Parts-V4.mdb'
mysql_dbname='PV7'
mysql_username='DBSuperUser'
mysql_password='super_pass'

#Delete the database if it exists
echo Deleting database ${mysql_dbname} if it exists
mysql -u ${mysql_username} --password=${mysql_password} -e "drop database ${mysql_dbname};"

#Create the database
echo Creating database ${mysql_dbname}
mysql -u ${mysql_username} --password=${mysql_password} -e "create database ${mysql_dbname};"

#Import the Schema
# changing all references to number 255 to 5000, because memo fields in Access can be much longer than 255, but mdb-tools makes them 255
# which can cause data truncation
echo Importing Schema from ${mdb_filename} into ${mysql_dbname}
mdb-schema ${mdb_filename} mysql | sed "s/^-/#/" | sed "s/255/5000/" | grep -v ^DROP |  mysql -u ${mysql_username} --password=${mysql_password} ${mysql_dbname}


#Put table names into an array
array=(`mdb-tables ${mdb_filename}`)    

#Import each Table
len=${#array[*]}
i=0
while [ $i -lt $len ]; do
echo Importing ${array[$i]} Table

# define date format, change backslashes to frontslashes and add semicolon before bringing into mysql
mdb-export -D "%F" -I ${mdb_filename} ${array[$i]} | sed -e 's/)$/)\;/' | sed -e 's/\\/\//g' | mysql -u ${mysql_username} --password=${mysql_password} ${mysql_dbname}

let i++
done

# Optimize the database.  May not be necessary
mysqlcheck -u ${mysql_username} --password=${mysql_password} --analyze --auto-repair --optimize ${mysql_dbname}

echo  ${mdb_filename} imported into mysql database ${mysql_dbname}
echo $i Tables imported into mysql database ${mysql_dbname}

#Create mysqldump of database to sql file and delete the database
#echo Dump to .sql file and delete database, since .sql file is all we wanted
#mysqldump -u ${mysql_username} --password=${mysql_password} ${mysql_dbname} > mysql_db.sql
#mysql -u ${mysql_username} --password=${mysql_password} -e "drop database ${mysql_dbname};"