Skip to content

Instantly share code, notes, and snippets.

Last active August 29, 2015 14:27
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save waldoj/437250184b736635b05e to your computer and use it in GitHub Desktop.
A modified version of the "Converting MS Access mdb files to sqlite" script posted at:
# Inspired by:
# Dave's Modifications:
# Line 29: Added code to remove COMMENT and SET statements.
# Lines 32 to 41: Added code to handle primary keys.
# Line 55: Added "postgres" to mdb-export command.
# Waldo's Modifications:
# Line 43: Change invalid field types to text.
# Line 55: Specify SQLite as database format.
# Use temporary files for sql statements to ease debugging if something goes wrong
# Export schema from mdb:
mdb-schema $1 postgres \
| sed "s/Int8/INTEGER(8)/" \
| sed "s/Int4/INTEGER(4)/" \
| sed "s/Float8/FLOAT(8)/" \
| sed "s/Float4/FLOAT(4)/" \
| sed "s/Bool/BOOLEAN/" \
| sed "s/Char /VARCHAR/" \
| grep -Ev "^--|COMMENT|SET" \
> create.sql
# Remove PRIMARY KEY Statement and add it to the TABLE definition
grep "CONSTRAINT" create.sql | while read k; do
TABLEIS=`echo $k | cut -f2 -d '"'`
COLUMNIS=`echo $k | cut -f6 -d '"'`
LINEOFCREATE=`grep -n 'CREATE TABLE "'"${TABLEIS}"'"' create.sql | cut -f1 -d:`
LINEOFCOLUMN=`grep -A 100 'CREATE TABLE "'"${TABLEIS}"'"' create.sql | grep -n '"'"${COLUMNIS}"'"' | head -1 | cut -f1 -d:`
sed -i -e "${ACTUALLINE}s/,/ PRIMARY KEY,/" create.sql
sed -i -e "/^.*${k}.*$/d" create.sql
# Set every "Postgres_Unknown" field type to plain text
sed -i 's/Postgres_Unknown 0x[[:alnum:]]\{2\}/TEXT/g' create.sql
# Import schema to sqlite3
sqlite3 $2<create.sql
# Delete old import data (adding to exising file later)
# Vast speed improvement with BEGIN..COMMIT
echo "BEGIN;">import-data.sql
# Export each table
for table in `mdb-tables $1`
mdb-export -I sqlite $1 $table |sed -e 's/)$/)\;/'\
| sed "s/\"-inf\"/\"NULL\"/mg" \
| sed "s/\"inf\"/\"NULL\"/mg" \
| sed "s/\"-nan\"/\"NULL\"/mg" \
| sed "s/\"nan\"/\"NULL\"/mg" \
echo "COMMIT;">>import-data.sql
# Import data to sqlite3
sqlite3 $2<import-data.sql
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment