Convert a MDB (Access) file to SQL

mdb2sql.sh

#!/bin/bash
# Convert a MDB file (Access) to SQL
# Needs mdbtools[http://mdbtools.sourceforge.net/]
#  run 'aptitude install mdbtools' on Debian/Ubuntu
# Created by Álvaro Justen <https://github.com/turicas>
# License: GPLv2

mdb=$1
sql=$2

if [ -z "$2" ]; then
    echo 'This script convert a MDB file to SQL file. You need to specify the name of both'
    echo "Usage: $0 <mdb_file> <sql_file>"
    exit 1
fi

if [ -z "$(which mdb-tables)" ]; then
    echo 'You need mdbtools installed.'
    echo 'Learn more at http://mdbtools.sourceforge.net/'
    echo 'If you use Debian/Ubuntu, just execute:'
    echo '  sudo aptitude install mdbtools'
    exit 2
fi

mdb-schema $mdb > $sql
sed -i 's/Long Integer/INT(11)/g; s/Text /VARCHAR/g' $sql
for table in $(mdb-tables $mdb); do
    mdb-export -I -R ';' $mdb $table >> $sql
done
sed -i '/^-\{2,\}/d; s/DROP TABLE /DROP TABLE IF EXISTS /' $sql

@anentropic Yes it's fairly common for shell scripts to be missing quotes, and to make other dubious assumptions (for var in $( ... ) is usually a bad idea, as is checking the output of which).

It is also missing the database type after the -I option to mdb-export, which will cause (some versions of?) mdb-export to fail.

The default string quote used by (some versions of?) mdb-export is ", which is not standard SQL; we need to add -q\' to get the correct behaviour.

My suggestion below incorporates these:

1. add quotes where needed to ensure correct behaviour
2. allow the user to specify the preferred SQL format (default to mysql)
3. allow output to go to stdout without forcing the user to add /dev/stdout to the end of the script invocation
   • avoid opening the output multiple times
4. get rid of unnecessary sed commands
   • ask mdb-schema to output the correct format to begin with
   • tell mdb-export which database type
   • leave SQL "--" comments alone; they might actually be useful to a future reader
5. avoid depending on "word splitting": use cmd | while read x ; do ... instead of for x in $( cmd ) ; do ...
6. if mdb-schema fails, skip the remaining steps
7. use command instead of which, and check all required commands
8. informative exit values taken from /usr/include/sysexits.h (but if you don't like them you can just use exit 1)

Normally I would point out the necessity to adjust the #! line to match wherever you have bash installed on your system, but that would be pointless in this case since it can only run on Linux (where the mdb-tools package is available, and where/bin/bash is more reliable than /usr/bin/env bash).

#!/bin/bash

[[ $* = --help || $* = -h ]] && exec cat <<-EndOfHelp
$0 INPUT-FILE [OUTPUT-FILE [DIALECT [FLUSH]]]
  INPUT-FILE    source MDB file
  OUTPUT-FILE   target SQL file, use '-' for stdout; default stdout
  DIALECT       one of 'access', 'mysql', 'oracle', 'postgres', 'sqlite', or 'sybase'; default 'mysql'.
  FLUSH         one of 'drop', 'truncate', or 'none'; default 'none'
EndOfHelp

#### Check prerequisites

for c in  mdb-schema  mdb-sql mdb-tables
command -v "$c" >/dev/null || { printf >&2 'The "%s" command appears to be missing; please ensure that mdb-tools is installed\n' "$c" ; exit 72 ; }

#### Check CLI options

(($# >= 1)) || { printf >&2 'Not enough args\n' ; exit 64 ; }
(($# <= 4)) || { printf >&2 'Too many args\n'   ; exit 64 ; }

mdb=$1
output=${2:--}
dialect=${3:-mysql}
flush=${4:-none}

[[ -n $mdb && -f $mdb ]] || { printf >&2 'Invalid input file (%s)\n' "$mdb" ; exit 66 ; }

# Edit this to match the list of dialects supported by your version of mdb-tools
case $dialect in
  access ) quote=\" ;;
  mysql | oracle | postgres | sqlite | sybase ) quote=\' ;;
  * ) printf >&2 'Invalid output database type (%s)\n' "$dbtype" ; exit 64 ;;
esac

case $flush in
  drop | truncate | none ) ;;
  * ) printf >&2 'Invalid table clearing method (%s)\n' "$flush" ; exit 64 ;;
esac

#### Last prerequisite: can read the MDB file to get list of tables

readarray -t Tables < <(
    mdb-tables -1 "$mdb"
)
(( ${#Tables[@]} > 0 )) || { printf >&2 'Cannot read list of tables from %s\n' "$mdb" ; exit 65 ; }

#### Establish the output file

# If output filename given, then all stdout from remainder of this script goes into it.
# This will truncate any existing file, so do this after all prerequisites have been checked.

[[ $output != - ]] && exec >"$output"

#### Ok, good to go

case $flush in
  drop )        printf 'drop table if exists %s;\n' "${Tables[@]}" ;;
  truncate )    printf 'truncate table %s;\n' "${Tables[@]}" ;;
esac

mdb-schema "$mdb" "$dialect" |
case $flush in
  truncate )    sed 's/^create table /&if not exists /i' ;;
  * )           cat ;;
esac
    ((PIPESTATUS[0] == 0)) || { printf >&2 'Cannot generate schema from "%s"\n' "$mdb" ; exit 65 ; }

for table in "${Tables[@]}"
do
    mdb-export -I "$dialect" -q "$quote" -boctal -D%F "$mdb" "$table"
done

The same thing with all error checking removed:

#!/bin/bash

mdb=$1
output=${2:--}
dialect=${3:-mysql}

[[ $output != - ]] && exec >"$output"

mdb-schema "$mdb" "$dialect"

mdb-tables -1 "$mdb" |
while read -r table
do
    mdb-export -I "$dialect" -q\' -boctal -D%F "$mdb" "$table"
done