I’m in the process of migrating an old website I made almost 8 years ago from it’s current unbearable platform of Windows, ASP and MS Access to some sort of *nix, PHP and MySQL. Tonight I decided to combat that MS Access database and transform it into a real database. To help me accomplish this I used the excellent tools provided by the mdbtools project.
First I had to export the schema of the database to a format readable by MySQL:
mdb-schema -S mydb.mdb mysql > mydb.sql
This creates an SQL-file containing the schema in plain readable SQL. Very nice.
Next I had to export the actual data from the tables in the MS Access database, like this:
TABLES=`mdb-tables mydb.mdb` for T in $TABLES; do mdb-export -I -R';\n' mydb.mdb $T >> mydb.sql done
The first line stores the names of all the tables in the variable TABLES. Then I simply loop through all of them and execute the mdb-export command on all of them, writing the output to the end of the schema-file.
In case I need to do this again in the future I put it all together in a script like this:
#!/bin/bash DBFILE=$1 OUTFILE=$2 #Check for correct number of arguments if [ ! $# -eq 2 ]; then echo "Usage: access2mysql.sh DBFILE OUTPUTFILE" echo "Example: access2mysql.sh msaccess.mdb mysql.sql" exit 1 fi #Check that DBFILE really exists if [ ! -f $DBFILE ]; then echo "$DBFILE does not exist." exit 1 fi #All is good, here we go! #Create schema mdb-schema -S $DBFILE mysql > $OUTFILE #Export table data TABLES=`mdb-tables $DBFILE` for T in $TABLES; do mdb-export -I -R';\n' $DBFILE $T >> $OUTFILE done #Clean up some Windows-character stuff dos2unix $OUTFILE exit 0
Now all I have to do is create a MySQL-database and write my newly created SQL-script into it and I’m good to go!
Before I could get the SQL-script to work I had to fire off a couple of sed-statements to fix the fact that I had used some illegal words as names for some of the table-columns, “type” and “condition” to be specific. No biggie though. Also, mdb-schema didn’t add “AUTO_INCREMENT” to the fields that were auto incrementing in the MS Access database so I had to do that manually.