Here is my snippet shell script example to auto load MySQL .CSV files. Automatically processing will save engergy and time. Using ’Here Doc’ is a good way for such case.

The script will loop a certain dir, to extract all .CSV files and insert them into DB. The DB tables are identified by file names.

#!/bin/bash
# set variables, such as USER, PASS, SRC etc ...
cd ${SRC}
for file in `ls  *.csv`
do
 if [[ "$file" =~ "re1"  ]]
 then
   TABLE='table1'
 elif [[ "$file" =~ "re2" ]]
 then
   TABLE='table2'
 elif [[ "$file" =~ "re3" ]]
 then
   TABLE='table3'
 else
   TABLE='table4'
 fi
 echo "processing file [" $file "] to table [" $TABLE "] ...";
$MYSQL -u "${USER}" -p"${PASS}" -h localhost -D ${DB} <<EOF

load data infile '${SRC}/${file}'
 into table ${TABLE}
 fields terminated by ','
 enclosed by '"'
 escaped by ''
 lines terminated by 'n';
 q
EOF
done

2 things need to be noticed:

1. Before load data file, make sure to assign the previlege to the user: grant file on *.* to test@localhost identified by ‘test’;
2. FIELDS ‘ESCAPED BY’ must use ” instead of ” or ” in Here DOC. Otherwise shell script will throw away error. refer to ’http://dev.mysql.com/doc/refman/5.1/en/load-data.html’ for more details about ‘FIELDS ESCAPED BY’.