batch mdb (MS access) to sqlite

mdb2sqlite.sh

#!/bin/bash
# Inspired by 
# https://www.codeenigma.com/community/blog/using-mdbtools-nix-convert-microsoft-access-mysql

# USAGE
# Rename your MDB file to migration-export.mdb 
# run ./mdb2sqlite.sh migration-export.mdb
# wait and wait a bit longer...


FileName=$1
Output=$2

mdb-schema $FileName sqlite > schema.sql
mkdir sqlite
mkdir sql
for i in $( mdb-tables $FileName ); do echo $i ; mdb-export -D "%Y-%m-%d %H:%M:%S" -H -I sqlite $FileName $i > sql/$i.sql; done

mv schema.sql sqlite
mv sql sqlite
cd sqlite

cat schema.sql | sqlite3 $Output

# for f in sql/* ; do echo $f && cat $f | sqlite3 db.sqlite3; done
for f in sql/* ; do echo $f && (echo 'BEGIN;'; cat $f; echo 'COMMIT;') | sqlite3 $Output; done


echo input: $1
echo  output: $2

mdb2sqlite_batch.py

import subprocess as sp
import os
import re

# find all mdb files in the current directory and subdirectories
def find_mdb_files(path= '.'):
    mdb_files = sp.check_output(['find', path, '-name', '*.mdb']).decode('utf-8').split('\n')
    return mdb_files

# run mdb2sqlite.sh and get the output
def convert_mdb_to_sqlite(mdb_file):
    # get current working directory
    cwd = os.getcwd()
    
    mdb_number = re.findall('/(\d+)/', mdb_file)[0] 
    sqlite_name = '{}.sqlite3'.format(mdb_number)
    parent_folder = os.path.dirname(mdb_file)
    file_name = os.path.basename(mdb_file)
    
    os.chdir(parent_folder)
    output = sp.check_output(['bash', cwd+'/mdb2sqlite.sh',file_name,cwd +'/sqlites/'+ sqlite_name])
    print(os.getcwd())
    os.chdir(cwd)

    return output.decode()
# 

if __name__ == '__main__':
    mdb_files = find_mdb_files()
    for mdb in mdb_files:
        if mdb:
            tmp = convert_mdb_to_sqlite(mdb)
            print(tmp)