Skip to content

Instantly share code, notes, and snippets.

@umrysh
Created December 15, 2012 04:14
Show Gist options
  • Save umrysh/4291298 to your computer and use it in GitHub Desktop.
Save umrysh/4291298 to your computer and use it in GitHub Desktop.
A modified version of the "Converting MS Access mdb files to sqlite" script posted at: http://pnenp.wordpress.com/2011/02/10/converting-ms-access-mdb-files-to-sqlite-mdb2sqlite/
#!/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
# Dave's Modifications:
# Line 25: Added code to remove COMMENT and SET statements.
# Lines 28 to 37: Added code to handle primary keys.
# Line 51: Added "postgres" to mdb-export command.
# 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
# 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 and replace nan and inf with NULL
for table in `mdb-tables $1`
do
mdb-export -I postgres $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
@waldoj
Copy link

waldoj commented Aug 17, 2015

This bit is trouble:

mdb-export -I postgres $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" \

I wound up with every appearance of the word information in my database replaced with NULLormation.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment