[SOLVED] bash script to update MySQL from Access using MDB Tools

bash script to update MySQL from Access using MDB Tools

I'm trying to make a cronjob script that will take entries in a MS Access database (*.mdb) and update the sql database on the server. The script I found drops the tables and replaces them with the ones in the mdb file.

#!/bin/bash    
TABLES=$(mdb-tables -1 $1)

MUSER="cloyd"
MPASS="******"
MDB="$2"

MYSQL=$(which mysql)

for t in $TABLES
do
    $MYSQL -u $MUSER -p$MPASS $MDB -e "DROP TABLE IF EXISTS $t"
done

mdb-schema $1 mysql | $MYSQL -u $MUSER -p$MPASS $MDB

for t in $TABLES
do
    mdb-export -D '%Y-%m-%d %H:%M:%S' -I mysql $1 $t | $MYSQL -u $MUSER -p$MPASS $MDB
done

I tried adding unique keys to the table on the IDs and remove the drop table part but it just wasn't updating the table.

Solution

I have figured it out. I am using mysqlimport instead and it seems to be working much better.

    #!/bin/bash

    TABLES=$(mdb-tables -1 $1)

    MUSER="cloyd"
    MPASS="************"
    MDB="$2"

    MYSQL=$(which mysql)


    for t in $TABLES
    do
        mdb-export -D '%Y-%m-%d %H:%M:%S' -Q $1 $t > $t.csv
        mysqlimport --fields-terminated-by=, --silent --local --replace --ignore-lines=1 --user=cloyd --password=**** timesystem $t.csv
    done