In the work is really common to get databases from different authorities, this databases are used sometimes to do data mining against our own databases, others we integrate the data with our internal systems, and the leasts we integrate the data into the police units that run our systems.
This databases come in many many flavors, and the last one was the infamous MSAccess, here comes “How to import a MSAccess database into MySQL under Linuxâ€
First you need to install mdb-tools:
“The MDB Tools project is a effort to document the MDB file format used in Microsoft’s Access database package, and to provide a set of tools and applications to make that data available on other platforms.â€
In our flavor of Linux(Ubuntu) we do this by executing the next code in the shell:
sudo apt-get install mdbtools
You might want to install mdbtools-gmdb, a graphical user interface to mdb-tools, in our case this wasn’t really useful because the databases where around 500Mb and gmdb2 stalled at the first move.
First you’ll need to create a MySQL database where to import the data, go to your phpMyAdmin or MySQL Administrator
and create it.
We will export the schema to the database using Linux pipes with the next command:
mdb-schema database.mdb mysql | grep -v ^DROP | mysql -u user_name -p new_mysql_database
//note(I recently reused this line, and apparently mdb-schema added some comments to it's output that mysql doesn't compile, so get the output to a file an clean the comments before trowing it into mysql).
Pipes work by redirecting the output of one command directly to the next one, allowing us to make some interesting things in the middle, in this case mdb-schema creates the CREATE statement in MySQL format, mdb-schema adds a DROP statement for every table, causing an ugly #1051 - Unknown table 'table_name' error when the database is new, to remove the DROP statement we use grep, grep works by searching every line in the input, returning only those lines that match the regular expression (of course grep do much more, but those are the basics) in this case the regular expression “~DROP†effectively return every line that doesn’t have a ‘DROP’ on it.
After removing the offensive “DROP†from mdb-schema output, we redirect it to the mysql client, in this case I assume you are working in localhost, other wise add a -h hostname to the line.
If something goes wrong you can always redirect the output to a file, changing the pipe symbol ‘|’ by the ‘>’ redirect input(ex: ‘> mysql_file.sql’ ) and check the file by hand.
You will need to import table by table, so lets take a look at the tables in your database file before importing.
mdb-tables database.mdb
This command will list all the tables in the current .mdb file.
Now we do the export using the following line for every table on the database:
mdb-export -I database.mdb table_name | sed -e 's/;/\;/g'| sed -e 's/)$/);/' | mysql -u user_name -p new_mysql_database
The mdb-export command creates the INSERT statements that we’ll run trough the MySQL client( -I option), but this output isn’t really fitted to MySQL, first you’ll need to escape “;†in the original output, to escape the semi-colon we use sed, sed is a powerful search replace bash tool(please refer to the sed manpage for more details, and check sed one liners
for some examples,) we need to add a semi-colon at the end of every INSERT, we use sed for that to (I told you it was powerful), finally we
can redirect the output into our MySQL database.
Note: mdb-export wrap text-fields with ‘â€â€˜ and escapes by repeating(CSV style), I never really checked that MySQL takes this without grunting, if that case happens to you, add -q "'" -X '\' to mdb-export it should handle character escaping in a MySQL friendly way.
Repeat the last step for every table, and you’ll get a pretty database in a friendly format.
That’s basically all, please share your results and happy hacking!!.
References:
Initially published on my work blog NotiOCRA Occidente.
