One of the hardest parts of any migration to Drupal is getting hold of the data in the first place. And once you do get hold of it, there’s every chance that it might not be a format that’s easy for you to work with.

One recent migration was from an unsupported application built upon SQL Server 2000. Policy meant that I couldn’t even try connecting to it directly. I was given a flat file export, but found I couldn’t install an old enough version of SQL Server to import it.

We were considering an export to CSV, but decided to give Access a try. This was a good decision, as it turns out there are command-line tools[1] you can use to access Access (sic) without even having to boot Windows.

Install mdbtools

mdbtools is a suite of tools which can be used to export data from Access databases. It can be found on github.

On OS X it can be installed via brew:

brew install mdbtools

The tools we’ll be using are mdb-schema, mdb-tables and mdb-export.

Export the schema

First, we’ll need to export a schema from our Access database, which in these examples is called migration-export.mdb. here I tell it to export a schema for MySQL:

mdb-schema migration-export.mdb mysql > schema.sql

Set up a new database

You could, theoretically, use the same database that the Drupal site is using, but in most cases it will make more sense to keep the import data on it’s own, so you can easily drop it or replace it with less worry.

In the following examples, I’ll use a database called migration_database, with username drupal and password drupal.

When writing your migration classes, you can easily connect to the database once you’ve included it’s details in settings.php. See How to connect to multiple databases within Drupal.[2]

Import the schema

You may want to give the schema a once-over. When you’re happy, import it into the database you set up for the migration:

mysql -udrupal -pdrupal migration_database < schema.sql

Export the tables as SQL

Here’s the handy one-liner I (eventually) worked out to export each table as an SQL file:

for i in $( mdb-tables migration-export.mdb ); do echo $i ; mdb-export -D "%Y-%m-%d %H:%M:%S" -H -I mysql migration-export.mdb $i > sql/$i.sql; done

Let me explain it piece by piece.

for i in $( mdb-tables migration-export.mdb ); do echo $i ;

This first part loops through each of the tables, which are listed using mdb-tables. We store the name of each table as we go through the list in the variable $i so we can use it later.

The second part looks complicated but is essentially one command, mdb-export, which exports the table into the file we specify:

mdb-export -D "%Y-%m-%d %H:%M:%S" -H -I mysql migration-export.mdb $i > sql/$i.sql;

Starting from the end, I’m specifying that the table is saved in a directory called sql, has the same name as the table itself (that’s where we use $i) and is suffixed with .sql.

The -D option sets the date format. Which date format? The format that’s used in our MySQL database, not the database we’re importing from. This wasn’t obvious to me initially so I ended up with back-to-front dates. The string "%Y-%m-%d %H:%M:%S" is in strftime format.

The -H option suppresses the header row, which may seem a strange option but mdb-export exports CSV by default.

The -I mysql option exports INSERT statements as opposed to CSV. We need to specify that it’s MySQL we’re using.

Import the SQL

On this particular project I actually only imported a few of the source tables, as and when I needed them (most of the tables contained data that wasn’t needed in the new site). As I had added the details of my migration database to settings.php, and given it an alias of ‘migration’, I could do this easily with drush:

drush sqlc --database=migration < ../path/to/sql/some_table_or_other.sql

If you wanted to import all the tables at once, you could use a for loop as we did with the export. I’ll leave that as an exercise for the reader.

1. There is an app for OS X called MDB Tool which can be used to view and export Access databases but I found it to be both buggy (it would crash when trying to display certain tables) and limited in it’s export capability.  ↩

2. You will see examples suggesting that you use db_set_active() to switch between databases but I would recommend that you explicitly state the migration database as and when you need to use it (using Database::getConnection) as shown on Cross-database migrations. Less room for silly errors when you forget to set the database connection back to the default.  ↩

Related Service Areas: ConsultancyDevelopmentTeaser: One recent migration was from an unsupported application built upon SQL Server 2000. Policy meant that I couldn't even try connecting to it directly. I was given a flat file export, but found I couldn't install an old enough version of SQL Server to import it. mdbtools to the rescue!Categories: ConsultancyDevelopmentDrupal PlanetPrimary Category: DevelopmentTags: Migration