Converting a Microsoft Access MDB file to SQLite

There are probably a few MSAccess MDB files left in the world, let’s convert them to something open and sustainable!

I came across a Microsoft Access MDB file that I couldn’t do much with with the tools I wanted to use (which is mostly Not Windows and Not Access) so I converted it to a SQLite database so I could operate on it, but without needing a whole database server.

I did this on a Mac, but you can almost certainly adapt these instructions to Linux or Windows or Plan9 if you need to.

On the Mac, I used Homebrew to install mdbtools (which can read Access MDB files) and SQLite3.

First, I extracted the database schema from the MDB file using the mdb-schema command like:

mdb-schema \
 /path/to/database.mdb | \
 sed -E 's/(\]|\[)//g' | \
 sed '/CREATE TABLE MSysCompactError/,+6d' | \
 sed 's$Memo\/Hyperlink$Text$' \
 > schema.txt

The three sed steps do the following things:

sed -E 's/(\]|\[)//g'

removes the brackets ([ and ]) that Access puts around table names

sed '/CREATE TABLE MSysCompactError/,+6d'

removes the MSysCompactError table if it exists; that table stores error messages from database compaction operations; this is immaterial to SQLite, so we don’t need it

sed 's$Memo\/Hyperlink$Text$'

replaces the Access schema type Memo/Hyperlink with Text, since SQLite doesn’t have a Memo/Hyperlink datatype

In the end, the schema is written to the file schema.txt. You should inspect this file and ensure it contains everything you expect and nothing you don’t.

You can then create the database with this schema with the command:

sqlite3 database.db < schema.txt

where the name of the database is database.db, but you can name it whatever you want.

You can then inspect the database by opening it with SQLite like:

sqlite3 database.db

You can use the SQLite commands .tables and .schema to see how the database is structured (tip: use .exit to leave the SQLite command-line interface (CLI)).

Once you are happy with the schema, you can start importing tables from the MDB file. The steps to do this are:

1. Get a list of the tables in the database using the mdb-tables command
2. Export the table to a CSV file using the mdb-export command like mdb-export <MDBFILE> <TABLE> > <TABLE>.csv, replacing <MDBFILE> with the path to the MSAccess MDB file and replacing <TABLE> with the name of the table you are exporting.
3. Open SQLite and delete any existing data using the SQL command delete from <TABLE> where <TABLE> is the name of the table you are about to import
4. Set the import mode to CSV with the SQLite command .mode csv
5. Import the exported CSV file, skipping the (first) header row with the SQLite command .import --skip 1 <TABLE>.csv <TABLE>

One way of doing this is to get a list of all of the tables, export each one to a CSV file, write the import commands to a file, and then have SQLite read those commands. A bash script that demonstrates this is:

#!/bin/bash
CMDFILE=sqlitecmds.txt
MDBFILE=/path/to/file.mdb
/bin/rm $CMDFILE
echo ".mode csv" >> $CMDFILE
for table in `mdb-tables  $MDBFILE | sed 's/MSysCompactError //'`; do
    echo "exporting table: $table"
    mdb-export  $MDBFILE $table > ${table}.csv
    echo "delete from $table;" >> $CMDFILE
    echo .import --skip 1 ${table}.csv $table >> $CMDFILE
    echo "exported table: $table"
done
echo "importing tables into SQLite"
sqlite3 database.db < $CMDFILE

At this point, if all went well, you should have a SQLite version of the MSAccess database, and you can use SQL commands to query and update it.