0

I have created a script to export CSV data to mysql table.

#!/bin/bash

cd /data/NEW
for f in User*
do
        mysql --user="root" --password="user@123" -e "LOAD DATA LOCAL INFILE  '/data/NEW/"$f"' ignore into table new.table2 fields terminated by ',' enclosed by '"' lines terminated by '\n' (table_date, table_name, table_count);"

done

But I am getting following errors. But I could not find where I have messed this.

test.sh: line 6: unexpected EOF while looking for matching `''
test.sh: line 9: syntax error: unexpected end of file

Can someone show me where to improve?

2 Answers 2

5

You seem to want to include a double quote in your double-quoted command string:

"... enclosed by '"' lines ..."

You need to escape the embedded double-quote character so that it is included in the string and not terminating it:

"... enclosed by '\"' lines ..."

Note also that your use of $f is totally unquoted here:

"LOAD DATA LOCAL INFILE  '/data/NEW/"$f"' ignore ..."

It would be better to quote it (i.e. let it be quoted by the double-quoted string that it is already part of):

"LOAD DATA LOCAL INFILE  '/data/NEW/$f' ignore ..."

Or, if you need to include the double-quote characters in the command string:

"LOAD DATA LOCAL INFILE  '/data/NEW/\"$f\"' ignore ..."

You may also use a here-document to make the quoting simpler:

mysql ... <<END_SQL
LOAD DATA
    LOCAL
    INFILE '/data/NEW/$f' 
    IGNORE
    INTO TABLE new.table2
    FIELDS TERMINATED BY ','
    ENCLOSED BY '"'
    LINES TERMINATED BY '\n'
    (table_date, table_name, table_count);
END_SQL

Each quote is literal in the here-document above.

4

You have a lot of " symbols inside your query. You should escape them.

mysql --user="root" --password="user@123" -e "LOAD DATA LOCAL INFILE  '/data/NEW/\"$f\"' ...

Notice \" instead of simple "?

It is usually better in such cases to store the SQL script in a file and execute it with redirection:

mysql --user="root" --password="user@123" < text_file.sql
1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.