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

Writing my version based on some comments above (sadly before I saw kristopolous's clean version):

#!/bin/bash

MDB_NAME="migration-export.mdb"
OUTPUT_DB_NAME="sqlite/db.sqlite3"

mkdir -p ./sqlite/sql
mdb-schema "$MDB_NAME" sqlite > sqlite/schema.sql
sIFS=$IFS
IFS='|'
for table in $( mdb-tables --delimiter="|" "$MDB_NAME" ); do echo $i ; mdb-export --quote=\' -D "%Y-%m-%d %H:%M:%S" -H -I sqlite "$MDB_NAME" "$table" > "sqlite/sql/$table.sql"; done

cat sqlite/schema.sql | sqlite3 "$OUTPUT_DB_NAME"

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

IFS=$sIFS

The important change here is temporarily changing delimiter to |, since table names can and often have spaces!!! And that would mess up the current script, since mdb-tables prints them all space-delimited by default, which is impossible to properly parse. Plus fixed missing quotes around variable names that can contain spaces

Here is the ChatGPT:

https://chatgpt.com/share/68e4fac3-c604-8012-acc9-38d0876b29f3

It produced 100% accurate script for non-Latin database AND removed MS Access attachment type, which is a no-no ( ie. Attachments type 0012):

#!/bin/bash
set -euo pipefail

MDB_NAME="migration-export.mdb"
OUTPUT_DB_NAME="sqlite/db.sqlite3"

mkdir -p ./sqlite/sql

echo "Extracting schema..."
mdb-schema "$MDB_NAME" sqlite > sqlite/schema.sql

echo "Cleaning schema..."
# Fix unsupported Access types and syntax
sed -i '
    s/type 0012/BLOB/g;
    s/OLEOBJECT/BLOB/g;
    s/MEMO/TEXT/g;
    s/TEXT ([0-9]*)/TEXT/g;
    /CONSTRAINT/d
' sqlite/schema.sql

# ⚠️ Removed quote-wrapping for CREATE TABLE — mdb-schema already uses backticks

echo "Creating SQLite database..."
sqlite3 "$OUTPUT_DB_NAME" < sqlite/schema.sql || echo "⚠️ Schema import produced warnings"

echo "Exporting tables..."
mdb-tables -1 "$MDB_NAME" | while read -r table; do
    [ -z "$table" ] && continue
    echo "→ Exporting $table..."
    mdb-export -q \' -D "%Y-%m-%d %H:%M:%S" -H -I sqlite "$MDB_NAME" "$table" > "sqlite/sql/$table.sql" || {
        echo "⚠️ Failed to export $table"
        continue
    }
done

echo "Importing into SQLite..."
for f in sqlite/sql/*.sql; do
    [ -s "$f" ] || continue
    echo "→ Importing $(basename "$f")"
    (echo 'BEGIN;'; cat "$f"; echo 'COMMIT;') | sqlite3 "$OUTPUT_DB_NAME" || echo "⚠️ Import warning for $f"
done

echo "✅ Done."

Enjoy