# [[access2MySQL.sh]] convert access db (maybe also other formats) to standard MySQL
# Required:  mdbtools, mdbtools-gmdb, konwert
# sudo apt-get install mdbtools mdbtools-gmdb konwert

# Fred Radeff (aka FR), fradeff@akademia.ch, www.akademia.ch
# lundi 10 mars 2008
# todo: script pour inclusion automatique des données dans les tables créées
echo "Entrer le fichier access à analyser (ex. windaube.mdb )"
ls -1 *.mdb
read access
echo "Le fichier à analyser: " $access  

#backup schema db access + conversion sql
echo "#import $i 2 MySQL" > create.sql
mdb-schema $access >> create.sql

#div compatibilite mysql
replace "DROP TABLE " "DROP TABLE IF EXISTS " -- create.sql
replace "Single,"  " Text,"  -- create.sql 
replace "Memo/Hyperlink (255)"  " Text"  -- create.sql 

replace "	Single"  " Text"  -- create.sql 
replace "Long Integer"  " int(20) "  -- create.sql 
replace "DateTime (Short)"  " DATETIME "  -- create.sql 

replace "Table des erreurs"  "TableDesErreurs"  -- create.sql 

#liste des tables
echo "#import2"  > create2.sql
echo "#"  >> create2.sql
mdb-tables $access | sed 's| |\n|g' >>   create2.sql

echo "Entrer le nom du fichier d'importation MySQL (ex. test)"
read mysql

#mysql="test"

#creation de la structure mysql
echo "mysql $mysql < create.sql"
	mysql $mysql < create.sql


#import des donnees au format csv avec nettoyage préalable

egrep -v '^#' create2.sql > t && mv t create2.sql
egrep -v '^$' create2.sql> t && mv t create2.sql
egrep -v '^Table$' create2.sql> t && mv t create2.sql
egrep -v '^des$' create2.sql> t && mv t create2.sql
egrep -v '^erreurs$' create2.sql> t && mv t create2.sql


cat create2.sql | while read i

do
	mdb-export $access $i > junk
	replace "latable" $i -- import.sql	
	konwert any-iso1 junk > t && mv t junk
	mysql $mysql < import.sql
        replace $i "latable" -- import.sql #on remet a zero le texte a remplacer (nom table)
done

le contenu du fichier import.sql:

TRUNCATE TABLE `latable` ;
LOAD DATA LOCAL INFILE 'junk' INTO TABLE latable FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' IGNORE 1 LINES;  

The MDB tools include Gnome MDB Viewer (gmdb2), a desktop tool for viewing MDB databases on Linux