Skip to content

Instantly share code, notes, and snippets.

@waldoj
Last active Aug 29, 2015
Embed
What would you like to do?
A modified version of the "Converting MS Access mdb files to sqlite" script posted at: https://gist.github.com/umrysh/4291298
#!/bin/bash
# Inspired by:
# http://nialldonegan.me/2007/03/10/converting-microsoft-access-mdb-into-csv-or-mysql-in-linux/
# http://cltb.ojuba.org/en/articles/mdb2sqlite.html
# https://gist.github.com/umrysh/4291298
# 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/" \
| sed "s/DROP TABLE/DROP TABLE IF EXISTS/" \
| 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:`
ACTUALLINE=$((LINEOFCREATE+LINEOFCOLUMN-1))
sed -i -e "${ACTUALLINE}s/,/ PRIMARY KEY,/" create.sql
sed -i -e "/^.*${k}.*$/d" create.sql
done
# 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`
do
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" \
>>import-data.sql
done
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