Bash Script for Load Data Infile MySQL

Question

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.

that other guy · Accepted Answer · 2014-03-03T02:17:06.897

11

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

edited Mar 03 '14 at 02:17

answered Mar 03 '14 at 02:02

that other guy

116,971
11
170
194

1

You don't really need to escape newlines in this case. – Ярослав Рахматуллин Mar 03 '14 at 02:10
1

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LOAD DATA LOCAL INFILE ("csv_name.csv") INTO TABLE temp_table' at line 1 – XFerrariX Mar 03 '14 at 02:11
@XFerrariX remove both \" in INFILE (\"$f\") .. or replace them with ' – Ярослав Рахматуллин Mar 03 '14 at 02:14
Thanks so much @that other guy. Your script aboves works great. – XFerrariX Mar 03 '14 at 02:29
I inherited a script that was using "mysql < $infile" format, and this was the direction it needed to go to work thanks @thatotherguy – Anthony Jul 10 '14 at 12:10

score 2 · Answer 2 · answered Mar 31 '15 at 11:31

2

mysql -u<username> -p<password> -h<hostname> <db_name> --local_infile=1 -e "use <db_name>" -e"LOAD DATA LOCAL INFILE '<path/file_name>' 
IGNORE INTO TABLE <table_name>
FIELDS TERMINATED BY '\t'
OPTIONALLY ENCLOSED BY '\"'"

answered Mar 31 '15 at 11:31

vishu9219

761
6
14

Bash Script for Load Data Infile MySQL

2 Answers2

Linked