How to convert MDB files to SQLite

Using mdbtools

Introduction

In this article I'll show you how to use some scripts to convert all of your mdb files at once to SQLite. We need a recent Linux distro (I'm using Fedora) and we need BASH and perl and mdbtools if it's not installed type: su -c 'yum install mdbtools' or sudo apt-get install mdbtools

Creating the database (Importing the Schema)

mdbtools contains a tool called mdb-schema which generates SQL-like commands to create the database (tables and cols ..etc.) just run it on your MDB file and pipe it to less to see the SQL-like commands. usually you need to process that file a bit to suit SQLite for example the type Memo corresponds to TEXT, and all small or long integer corresponds to integer, VARCHAR or CHAR with some size between parentheses () also corresponds to TEXT and we don't specify size, dates can be considered strings ..etc, as SQLite types are flexible.
SQLite typeMDB types
INTEGERBoolean, Byte, Integer, Long Integer, Replication ID, Numeric
REALCurrency, Float, Single, Double
BLOBBINARY, OLE, Unknown 0x00, Unknown 0x09, Unknown 0x0d, Unknown 0x0e
TEXTTEXT,MEMO/Hyperlink, any DateTime (Short)
The tool drops the table before creating it, so sqlite will warn you that the table does not exists so I make it "DROP TABLE IF EXISTS" to get rid of the warning.

I would love to see mdbtools exports to SQLite schema directly (maybe a simple patch to src/libmdb/backend.c would do that) but till that happen here is a script that just do it. The Following command will create the database that corresponds to the file x.mdb and it will call it x.db

bash$ mdb-schema x.mdb | perl -wpe 's%^DROP TABLE %DROP TABLE IF EXISTS %i;
  s%(Memo/Hyperlink|DateTime( \(Short\))?)%TEXT%i;
  s%(Boolean|Byte|Byte|Numeric|Replication ID|(\w+ )?Integer)%INTEGER%i;
  s%(BINARY|OLE|Unknown ([0-9a-fx]+)?)%BLOB%i;
  s%\s*\(\d+\)\s*(,?[ \t]*)$%${1}%;' | sqlite3 x.db
But I suggest you save the schema to a file then edit it manually to make sure it suits you.
bash$ mdb-schema x.mdb | perl -wpe 's%^DROP TABLE %DROP TABLE IF EXISTS %i;
  s%(Memo/Hyperlink|DateTime( \(Short\))?)%TEXT%i;
  s%(Boolean|Byte|Byte|Numeric|Replication ID|(\w+ )?Integer)%INTEGER%i;
  s%(BINARY|OLE|Unknown ([0-9a-fx]+)?)%BLOB%i;
  s%\s*\(\d+\)\s*(,?[ \t]*)$%${1}%;' > x.sql.txt
 (...) Edit the file with any text editor (...)
bash$ sqlite x.db < x.sql.txt

Importing data into our database

If you are importing from MDB_JET3 (which is the engine of MS Access 97) you need to specify the encoding with something like MDB_JET3_CHARSET=cp1256 (that's for Arabic, if you want latin then cp1251 ..etc.). and you need to terminate each insert command with semicolon and that is done by passing -R ";\n" and to make that faster we will import each table in a transaction. The command is:

bash$ for i in $(mdb-tables x.mdb); do echo $i; (
  echo "BEGIN TRANSACTION;";
  MDB_JET3_CHARSET=cp1256 mdb-export -R ";\n" -I x.mdb $i;
  echo "END TRANSACTION;" ) | sqlite3 x.db; done
I hope that was useful for you

Home
Copyrights & Copylefts
What is Linux?
How to install Linux?
Halloween Documents

ArabEyes
Wikipedia
OpenSource
GNU
FSF's FSD
OSI's OSD
Linux.org
Linux.com
LinuxToday
SlashDot
FreshMeat
LWN.net

About Islam
What is Islam ?
We love Jesus; honored and not crucified.
Do you love Jesus or Paul ?
Who is Muhammad [PBUH] ?
Report
Unwanted Advertisements
Bugs and misspellings
Dead links:

 

Best viewed with free web browsers

proud to be 100% Microsoft FREE GNU FDL
Generously Hosted by www.JadMadi.net

Copyright © 2007, Muayyad Saleh AlSadi