Simple conversion utility for Microsoft Access databases (.mdb) to SQLite3.

export_to_csv.sh

#!/bin/bash

# usage: export_to_csv.sh <database.sqlite>

sqlite3 $1 "SELECT tbl_name FROM sqlite_master WHERE type='table' and tbl_name not like 'sqlite_%';" | while read table; do

echo $table

sqlite3 $1 <<!
.headers on
.mode csv
.output "$table.csv"
select * from "$table";
!

done

mdb2sqlite.py

#!/usr/bin/env python
# -*- coding: utf-8 -*-

import pyodbc
import sqlite3
from collections import namedtuple
import re
import sys
import os

usage = '''
Usage: mdb2sqlite.py <input.mdb> <output.sqlite>
'''

filename_in = os.path.abspath(sys.argv[-2])
filename_out = sys.argv[-1]

cnxn = pyodbc.connect('Driver={{Microsoft Access Driver (*.mdb, *.accdb)}};Dbq={};'.format(filename_in))

cursor = cnxn.cursor()

conn = sqlite3.connect(filename_out)
c = conn.cursor()

Table = namedtuple('Table', ['cat', 'schem', 'name', 'type'])

# get a list of tables
tables = []
for row in cursor.tables():
    if row.table_type == 'TABLE':
        t = Table(row.table_cat, row.table_schem, row.table_name, row.table_type)
        tables.append(t)

for t in tables:
    print t.name
    
    # SQLite tables must being with a character or _
    t_name = t.name
    if not re.match('[a-zA-Z]', t.name):
        t_name = '_' + t_name

    # get table definition
    columns = []
    for row in cursor.columns(table=t.name):
        print '    {} [{}({})]'.format(row.column_name, row.type_name, row.column_size)
        col_name = re.sub('[^a-zA-Z0-9]', '_', row.column_name)
        columns.append('{} {}({})'.format(col_name, row.type_name, row.column_size))
    cols = ', '.join(columns)
        
    # create the table in SQLite
    c.execute('DROP TABLE IF EXISTS "{}"'.format(t_name))
    c.execute('CREATE TABLE "{}" ({})'.format(t_name, cols))
    
    # copy the data from MDB to SQLite
    cursor.execute('SELECT * FROM "{}"'.format(t.name))
    for row in cursor:
        values = []
        for value in row:
            if value is None:
                values.append(u'NULL')
            else:
                if isinstance(value, bytearray):
                    value = sqlite3.Binary(value)
                else:
                    value = u'{}'.format(value)
                values.append(value)
        v = ', '.join(['?']*len(values))
        sql = 'INSERT INTO "{}" VALUES(' + v + ')'
        c.execute(sql.format(t_name), values)

conn.commit()
conn.close()

Thanks for the great script! I had to change line 60 though to values.append(None) so the proper nulls could be inserted instead of strings "NULL".

Love it! A few changes I made:

1. I have a field called ID in a few places, which is the key, long integer, increments new values, and no duplicates.
2. I have a few Yes/No columns (essentially booleans) that were getting turned into text in sqlite
3. Where I had the ID field, I wanted my data sorted by that field ascending

I changed lines 44 to 47:

    has_id = False
    for row in cursor.columns(table=t.name):
        col_name = re.sub('[^a-zA-Z0-9]', '_', row.column_name)
        if row.type_name == 'COUNTER':
            columns.append('{} INTEGER PRIMARY KEY AUTOINCREMENT'.format(col_name))
            print('    {} [INTEGER PRIMARY KEY AUTOINCREMENT]'.format(col_name))
            has_id = True
        elif row.type_name == 'BIT':
            columns.append('{} INTEGER'.format(col_name))
            print('    {} [INTEGER]'.format(col_name))
        else:
            columns.append('{} {}({})'.format(col_name, row.type_name, row.column_size))
            print('    {} [{}({})]'.format(col_name, row.type_name, row.column_size))

Changed Line 55 to:

    if has_id:
        cursor.execute('SELECT * FROM "{}" ORDER BY ID ASC'.format(t.name))
    else:
        cursor.execute('SELECT * FROM "{}"'.format(t.name))

And added this after Line 63:

                elif isinstance(value, bool):
                    if value:
                        value = '1'
                    else:
                        value = '0'