Export MDB Tables

About

This is a very simple script that will export all queries in a database.

No Warranty, as is

To Run

• install mdb-tools, can be installed on most distribs
• Copy script in a folder
• change DB_FILE to point to access your database
• Change the credentials as MUSER,MPASS and MDATABASE
• run script, it will create a folder called data and put all tables and queries

Script

#!/bin/bash
quote_table_name() {
  echo "'$1'"
}

DB_FILE="legacy.mdb"
LIST_FILE="data/list_tables"
QUERIES_LIST="data/list_queries"
TABLE_FOLDER="data/tables"
QUERY_FOLDER="data/queries"

MUSER="root"
MPASS=""
MDB="legacy"

mkdir -p data/queries data/tables

mdb-tables -1 $DB_FILE >$LIST_FILE

#(echo "SELECT Name FROM MSysObjects WHERE Type = 5;" | mdb-sql "$DB_FILE" -P -H -F) > "$QUERIES_LIST"
mdb-queries -1 $DB_FILE>$QUERIES_LIST

echo "\n--------------------------"
echo "*** EXPORTING TABLES *** "
while IFS= read -r table; do
     echo "exporting $table"
    mdb-export --insert=mysql $DB_FILE "$table" > "$TABLE_FOLDER/$table.sql"
    #mysql -u "$MUSER" -p"$MPASS" "$MDB" <"$TABLE_FOLDER/$table.sql"
done < $LIST_FILE
echo "\n--------------------------"
echo "****** EXPORTING Queries ******* "
while IFS= read -r query; do
     echo " -- exporting $query"
    mdb-queries $DB_FILE "$query" > "$QUERY_FOLDER/$query.sql"
    sed -i -E 's/[[:space:]]+FROM/\nFROM/g' "$QUERY_FOLDER/$query.sql"
    sed -i -E '/Personnes\.[^`]*`/!s/(Personnes\.)([A-Za-z0-9_ °]+)/\1`\2`/g' "$QUERY_FOLDER/$query.sql"
    sed -i 's/\[/`/g; s/\]/`/g' "$QUERY_FOLDER/$query.sql"
    sed -i 's/`personnes`/`Personnes`/gI' "$QUERY_FOLDER/$query.sql"
    sed -i 's/`date`/`Date`/gI' "$QUERY_FOLDER/$query.sql"

    pg_format -o "$QUERY_FOLDER/$query.sql" "$QUERY_FOLDER/$query.sql"
    sed -i 's/Date()/CurDate()/g' "$QUERY_FOLDER/$query.sql"
    #mysql -u "$MUSER" -p"$MPASS" "$MDB" <"$QUERY_FOLDER/$query.sql"
done < $QUERIES_LIST