Accessing an Access Database with mdbtools

Basically, what I wanted to do is accessing an MS Access database directly from R. This is probably possible and subject of another post.

Halfway from MS Access to R is mdbtools which can be added to Ubuntu in a terminal with:
sudo aptitude install mdbtools

MDB Tools comes with a set of command line utilties that allow mdb files to be used in shell scripting, extraction to another database, and similar functions. Each program is documented in its man page.

In order to extract the tables of the database into colon separated value (.csv) format create a file, say mdb-explode with the following content:

#! /bin/bash
mkdir $(echo $1|awk -F "." {'print $1'}).csvs
for i in $(mdb-tables $1); do
echo $i
mdb-export $1 $i > $(echo $1|awk -F "." {'print $1'}).csvs/$i.csv

Save the the file, open a terminal and make it executable:
chmod +x mdb-explode

If your MS Access database file (.mdb) is in the same directory you would
./mdb-explode YourDatabaseFile.mdb
and all tables of the database end up in a folder called YourDatabaseFile.csvs as .csv files which can be accessed by R via the read.table() function.

The idea is posted on Ubuntuforums by Skrynesaver but the script posted has some typos, which are corrected above. The script posted here worked with Ubuntu 9.04, mdbtools 0.5.99 and an Access 2003 database file.


3 thoughts on “Accessing an Access Database with mdbtools

  1. Thank you! That was very useful for me..
    As a little contribution, the script above can be modified so that instead of getting csv files, we get INSERT sql statement for each row followed by “;”. This is practical since you don’t need play with .csv files. Here is the modified script (I called it “mdb-explode-sql” ):
    #! /bin/bash
    mkdir $(echo $1|awk -F “.” {‘print $1′}).sql
    for i in $(mdb-tables $1); do
    echo $i
    mdb-export -I -R’;\n’ $1 $i > $(echo $1|awk -F “.” {‘print $1′}).sql/$i.sql

    Notice the addition of the arguments “-I -R’;\n’ “, which create the INSERT statements and add ‘;’ at the end of each statement.

    This trick I found it here:

  2. Thanks, this tip is an incredibly useful way to get everything out of .mdb format quickly and simply.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s