Created
April 29, 2022 17:36
-
-
Save martinbowling/bda1441e12f79a327fdf05cea253443b to your computer and use it in GitHub Desktop.
mdb to mysql
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
.mdb to mysql (from https://notes.tomcarlson.com/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};" |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment