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-03 02:17:06Z

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 3, 2014 at 2:17

answered Mar 3, 2014 at 2:02

that other guy

125k12 gold badges187 silver badges214 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Ярослав Рахматуллин Over a year ago

You don't really need to escape newlines in this case.

XFerrariX Over a year ago

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

Ярослав Рахматуллин Over a year ago

@XFerrariX remove both \" in INFILE (\"$f\") .. or replace them with '

XFerrariX Over a year ago

Thanks so much @that other guy. Your script aboves works great.

Anthony Over a year ago

I inherited a script that was using "mysql < $infile" format, and this was the direction it needed to go to work thanks @thatotherguy

vishu9219 · Accepted Answer · 2015-03-31 11:31:40Z

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, 2015 at 11:31

vishu9219

8137 silver badges15 bronze badges

Collectives™ on Stack Overflow

Bash Script for Load Data Infile MySQL

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related