Transforms MS Access MDB file to sqlite database using mdbtools

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

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

mv schema.sql sqlite
mv sql sqlite
cd sqlite

cat schema.sql | sqlite3 db.sqlite3

for f in sql/* ; do echo $f && cat $f | sqlite3 db.sqlite3; done

For a large speed increase, replace the last line with

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

thanks!

Thank you for that. I've improved the script, now it accepts parameters and removes files after running:

test -f $2 && rm $2

mdb-schema $1 sqlite > schema.sql
mkdir sql
for i in $( mdb-tables $1 ); do mdb-export -D "%Y-%m-%d %H:%M:%S" -H -I sqlite $1 $i > sql/$i.sql; done
cat schema.sql | sqlite3 $2
for f in sql/* ; do (echo 'BEGIN;'; cat $f; echo 'COMMIT;') | sqlite3 $2; done

rm -R sql
rm schema.sql
rm $1

To run:

mdb2sqlite.sh <input_file.mdb> <output_file.sqlite3>

Here's a cleanup I did.

• You can pass the command line argument for the db as you probably thought you ought to
• it creates a unique working directory using timestamps and logical naming
• you can swap out the sqlite executable name with the sqlite variable at the beginning
• it has the optimizations by @phiresky
• it's side-effect free from multiple executions
• everything is localized to the file name so it's parallelizable as well

#!/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...

now=$(date +%s)
sqlite=sqlite3
fname=$1
sql=${fname/mdb/sqlite}
schema=${fname/mdb/schema}
dir=${fname/.mdb/}-$now

mkdir $dir

mdb-schema $fname sqlite > $dir/$schema

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

< $dir/$schema $sqlite $sql

for f in $dir/*.sql ; do 
  echo $f 
  (echo 'BEGIN;'; cat $f; echo 'COMMIT;') | $sqlite $sql
done
echo "Using $dir"

Today, on my system, it needs extra --quote=\' passed to mdb-export in order to make it work due to sqlite changes, that make it not accept double quotes anymore