William Jiang

JavaScript,PHP,Node,Perl,LAMP Web Developer – http://williamjxj.com; https://github.com/williamjxj?tab=repositories

Bash and MySQL: load .CSV files

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’.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: