Bash Script for Load Data Infile MySQL

XFerrariX picture XFerrariX · Mar 3, 2014 · Viewed 17.8k times · Source

So i'm trying to create a script that I can run that will do a batch import of csv files into a table.

I'm having trouble getting the script to work.

Here is the script i'm running:

#!/bin/bash
for f in *.csv
do
"/opt/lampp/bin/mysql -e use test -e LOAD DATA LOCAL INFILE ("$f") INTO TABLE temp_table FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES (DATE, TIME, SITE_NAME, SITE_IP, TOTAL_TALKTIME, EDGE_UL_BYTES, EDGE_DL_BYTES);"
done

When I run the script I receive the following error message:

./script.sh: line 5: unexpected EOF while looking for matching `''
./script.sh: line 7: syntax error: unexpected end of file

The load data local infile command works fine directly in mysql.

Answer

that other guy picture that other guy · Mar 3, 2014

When you want to use literal double quotes in double quoted strings, escape them with \". Since mysql doesn't care about line feeds, you can also break the line to make it more readable:

#!/bin/bash
for f in *.csv
do
/opt/lampp/bin/mysql -e "use test" -e "
      LOAD DATA LOCAL INFILE '$f'
      INTO TABLE temp_table 
      FIELDS TERMINATED BY ',' 
      OPTIONALLY ENCLOSED BY '\"' 
      LINES TERMINATED BY '\n' 
      IGNORE 1 LINES 
      (DATE, TIME, SITE_NAME, SITE_IP, TOTAL_TALKTIME, 
           EDGE_UL_BYTES, EDGE_DL_BYTES);"
done