Recreate MS Access table in SQLite (Python recipe) by K. Killebrew

ActiveState Code (http://code.activestate.com/recipes/572165/)

"""
jetout.py - Reconstructs a given table from a Jet (MS Access) file in SQLite,
storing created table on disk; uses win32com to get to DAO 3.6 (Office 2000),
and uses sqlite3.  To ensure index names are unique to the entire database,
names of recreated indexes are prefixed with the table name.

Date/Time typed columns are reformatted to ISO's yyyy-mm-dd, and may be recalled
as datetime.date types using the built-in DATE converter in pysqlite's
converters dictionary, if the database is opened with the argument
'detect_types=sqlite3.PARSE_DECLTYPES' in the 'connect' statement.

To modify this module to import Date/Time type as datetime.datetime instead
of datetime.date:  change 'DATE' to 'TIMESTAMP' for CREATE TABLE, and modify
last line of sepdate function to include 'timepart'.

Usage:
sqlitefromjet("input.mdb", "input table name", "output.db"
[, # of rows to fetch/commit at a time])
"""
def sqlitefromjet(mdb, tbl, sqlite_db, fetchsize=1000):
        import win32com
        from win32com import client
        import sqlite3
        from itertools import imap, izip
        engine = win32com.client.Dispatch("DAO.DBEngine.36") # Office 2000
        db = engine.OpenDatabase(mdb)
        table = db.OpenRecordset(tbl)
        connection = sqlite3.connect(sqlite_db)
        cursor = connection.cursor()
        if tbl.find(' ') != -1 and tbl[:1] != '[':
                tbl = '[' + tbl + ']' # bracket table names with spaces
        # build SQL statement to create table
        createStr = 'CREATE TABLE ' + tbl + ' (' 
        fieldrange = range(table.Fields.Count)
        isType = [] # check field types (use to make ISO date from Date/Time)
        pkf = False # look for AutoNumber PK field to make INTEGER PRIMARY KEY
        havePK = False
        for tdef in db.TableDefs:
                if tdef.Name == table.Name:
                        tblDef = tdef # grab TableDef object for its indexes
                        break
        for idx in tblDef.Indexes:
                if idx.Primary:
                        for idxf in idx.Fields:
                                if pkf:
                                        pkf = False
                                        break
                                pkf = idxf.Name # grab PK field, if only one
                        break # found the PK; all done
        for field in fieldrange:
                createStr = createStr + '[' + table.Fields(field).Name + ']'
                ftype = table.Fields(field).Type # get int representing type
                if ftype == 4:
                        if pkf:
                                if table.Fields(field).Name == pkf:
                # if field was AutoNumber PK, will still autoincrement
                                        createStr += ' INTEGER PRIMARY KEY, '
                                        havePK = True
                        else:
                                createStr += ' INTEGER, '
                        isType.append(1)
                elif ftype in (2,3):
                        isType.append(1) # number
                        createStr += ' INTEGER, '
                elif ftype in (5,6,7,20):
                        if ftype == 5:
                                isType.append(3) # currency type
                        else:
                                isType.append(1) # number
                        createStr += ' NUMERIC, '
                elif ftype == 8:
                        isType.append(2) # date
                        createStr += ' DATE, ' # (or TIMESTAMP)
                else:
                        isType.append(0) # text
                        createStr += ' TEXT, '
                if table.Fields(field).Required:
                        createStr = createStr[:-2] + ' NOT NULL, '
        createStr = createStr[:-2] + ');'
        cursor.execute(createStr) # create table
        stmt = "INSERT INTO " + tbl + " VALUES(" # build INSERT for executemany
        for fieldnum in fieldrange:
                stmt += "?, "
        stmt = stmt[:-2] + ")"
        df = [x for x in fieldrange if isType[x] == 2] # date column offsets
        cf = [x for x in fieldrange if isType[x] == 3] # currency column offsets
        lastSet = False
        while not lastSet:
                # fetch 'fetchsize' records at a time (default of 1,ooo)
                fetched = map(list, table.GetRows(fetchsize)) # in mutable form
                fetchnum = len(fetched[0]) # get actual number of rows fetched
                print "\tFetched " + "%s" % fetchnum + " rows"
                # check if all 'fetchsize' rows fetched...
                if fetchnum < fetchsize:    
                        lastSet = True # ...last set (or read error) if not
                for x in df:
                        fetched[x] = imap(sepdate, fetched[x]) # yyyy-mm-dd
                for x in cf:
                        fetched[x] = imap(conv_curr, fetched[x]) # get currency
                rows = izip(*fetched) # put [fields][rows] into [rows][fields]
                cursor.executemany(stmt, rows) # INSERT one fetch
                connection.commit() # commit one transaction per fetch
        print "INSERTs complete - creating indexes..." 
        for idx in tblDef.Indexes:
                newName = table.Name + idx.Name 
                if idx.Unique:
                        if havePK and idx.Primary:
                                continue # already have unique index if int PK
                        else:
                                createStr = "CREATE UNIQUE INDEX [" + \
                                            newName + "] ON " + tbl + "("
                else:
                        createStr = "CREATE INDEX [" + newName + "] ON " + \
                                    tbl + "("
                for idxf in idx.Fields:
                        createStr += "[" + idxf.Name + "], "
                createStr = createStr[:-2] + ");"
                cursor.execute(createStr) # add an index
        print "ok - all done"
        cursor.close()
        connection.close()
        table.Close()
        db.Close()

def sepdate(dt):
        """sepdate('%m/%d/%y %H:%M:%S') -> '%Y-%m-%d'

Return ISO-format yyyy-mm-dd date from Access-formatted Date/Time."""
        dt = "%s" % dt
        if dt.find(" ") != -1:
                datepart, timepart =  dt.split(" ")
        else:
                return None
        month, day, year = datepart.split("/")
        if int(year) <= 29:
                year = '20' + year # make same assumptions about 2-digit
        elif int(year) <= 99:
                year = '19' + year # years as Access' Short Date format
        return year + "-" + month + "-" + day  # + " " + timepart # TIMESTAMP

def conv_curr(curr):
        """conv_curr(Access currency-typed field) -> float

Return a float from MS Access currency datatype, which is a fixed-point integer
scaled by 10,000"""
        return float(curr[1])/10000 # convert fixed-point int to float