Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Convert Microsoft Access *.mdb file into PostgreSQL database
#!/usr/bin/env bash
# install mdbtools first!
# mdbtools: https://github.com/brianb/mdbtools
# ref: https://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL
DBMS=postgres
for MDB in `ls | grep .mdb$`
do
# extract the mdb file basename, and substitute upcase letters to lowercase
MdbBASE=`basename ${MDB} .mdb`
lMdbBASE=`basename ${MDB} .mdb | tr 'A-Z' 'a-z'`
echo "Processing ${MdbBASE}"
# create mdb file basename and create directory for output
if [ ! -d ${lMdbBASE} ]; then
mkdir -p ${lMdbBASE}
fi
# check if the target database exists or not
# if the database exists, back it up first
checkdb=`psql -qA -t postgres -c "SELECT datname FROM pg_database where datname='${lMdbBASE}'"`
if [[ ${checkdb} == ${lMdbBASE} ]]; then
# backup existed database first
pg_dump ${lMdbBASE} | gzip > ${lMdbBASE}.sql.gz
dropdb ${lMdbBASE}
fi
# create database and import table schema
createdb ${lMdbBASE}
mdb-schema ${MDB} ${DBMS} | tr 'A-Z' 'a-z' | psql -d ${lMdbBASE}
# import data
for T in $(mdb-tables ${MDB})
do
mdb-export ${MDB} ${T} > ${lMdbBASE}/${T}.csv
mdb-export -q "'" -I ${DBMS} ${MDB} ${T} | tr 'A-Z' 'a-z' | psql -d ${lMdbBASE}
done
done
@zkutch
Copy link

zkutch commented Jul 15, 2017

non comparably faster:

mdb-export -H -Q -q "'" $access_db_file $table | psql -d $postgres_db -c "COPY $table from STDIN with null as ''

text fields with "bad" symbols need additional work

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