Skip to content

Instantly share code, notes, and snippets.

Last active March 8, 2024 17:13
Show Gist options
  • Save snorfalorpagus/8578272 to your computer and use it in GitHub Desktop.
Save snorfalorpagus/8578272 to your computer and use it in GitHub Desktop.
Simple conversion utility for Microsoft Access databases (.mdb) to SQLite3.
# usage: <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";
#!/usr/bin/env python
# -*- coding: utf-8 -*-
import pyodbc
import sqlite3
from collections import namedtuple
import re
import sys
import os
usage = '''
Usage: <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)
for t in tables:
# SQLite tables must being with a character or _
t_name =
if not re.match('[a-zA-Z]',
t_name = '_' + t_name
# get table definition
columns = []
for row in cursor.columns(
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(
for row in cursor:
values = []
for value in row:
if value is None:
if isinstance(value, bytearray):
value = sqlite3.Binary(value)
value = u'{}'.format(value)
v = ', '.join(['?']*len(values))
sql = 'INSERT INTO "{}" VALUES(' + v + ')'
c.execute(sql.format(t_name), values)
Copy link

kizernis commented Nov 4, 2018

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".

Copy link

nick2893 commented Mar 8, 2024

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(
        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))
            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(
        cursor.execute('SELECT * FROM "{}"'.format(

And added this after Line 63:

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment