Skip to content

Instantly share code, notes, and snippets.

@akki-io

akki-io/script.sh

Created July 30, 2019 03:49
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save akki-io/386da40aabf4ee4ffb29cc6b83442571 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save akki-io/386da40aabf4ee4ffb29cc6b83442571 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. akki-io created this gist Jul 30, 2019.
    49 changes: 49 additions & 0 deletions script.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,49 @@
    #!/bin/bash
    #===========================================================
    # FILE : script.sh
    # DESCRIPTION : Convert MS Access to MySQL.
    #===========================================================
    set -o nounset # help avoid bugs
    shopt -s extglob
    PATH=/bin:/usr/bin:/sbin # for consistency

    # variables declaration
    DB_NAME=ENTER_YOUR_DB_NAME
    DB_UN=ENTER_YOUR_DB_USER
    PATH_TO_ACCESS_FILE=ENTER_THE_ABSOLUTE_PATH_TO_ACCESS_FILE

    # set login path to suppress warnings
    mysql_config_editor remove --login-path=local
    mysql_config_editor set --login-path=local --host=localhost --user=$DB_UN --password

    # get all the tables
    TABLES=$(mdb-tables -1 $PATH_TO_ACCESS_FILE)

    # drop tables if exits
    echo "$(date +%Y%m%d_%H%M) DROPPING TABLE IF EXISTS"
    for t in $TABLES
    do
    mysql --login-path=local $DB_NAME -Bse "DROP TABLE IF EXISTS $t;"
    done

    # create meta definition for the tables
    rm -rf meta.sql
    mdb-schema $PATH_TO_ACCESS_FILE mysql > meta.sql

    # create the tables using the meta.sql file generated above
    echo "$(date +%Y%m%d_%H%M) CREATING TABLES;"
    mysql --login-path=local $DB_NAME < meta.sql
    rm -rf meta.sql

    echo "$(date +%Y%m%d_%H%M) IMPORTING DATA INTO TABLES;"
    for t in $TABLES
    do
    echo "$(date +%Y%m%d_%H%M) IMPORTING DATA FOR $t;"
    rm -rf $t.csv
    mdb-export -D '%Y-%m-%d %H:%M:%S' $PATH_TO_ACCESS_FILE $t > $t.csv
    mysqlimport --login-path=local --ignore-lines=1 --fields-terminated-by=, --fields-optionally-enclosed-by='"' --local $DB_NAME $t.csv
    mysql --login-path=local $DB_NAME -Bse "ALTER TABLE $t CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;"
    rm -rf $t.csv
    done

    echo "$(date +%Y%m%d_%H%M) COMPLETED;"