MySQL - LOAD DATA LOCAL INFILE shell script not working

Question

can someone please assist, I am new to mysql, and i have noticed that "LOAD DATA LOCAL INFILE" does not work in mysql event scheduler to update my databases from a normal .csv

So I'm trying to setup a "cron job" in linux to run a shell script to do the LOAD DATA INFILE to my databases, but im getting errors on the following shell script, please help correct it, see my script layout below...

#!/bin/bash
mysql -u root -p xxxxxxx testdb --local_infile=1 -e"LOAD DATA LOCAL INFILE '/mnt/mysqldb/mysqldb-new/mysql/CK-BATCH-FTP/Acelity/activity.csv' 
INTO TABLE acelity_activity
FIELDS TERMINATED BY ';'
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

Thank you for helping me

@PaulT. the error message states the following: an unexpected EOF while looking for matching `' ' — Graham Jansen
– Graham Jansen, Commented Jun 28, 2020 at 18:41
hi @PaulT. do you have any further updates on this issues ? maybe some help on the correction of the script ? — Graham Jansen
– Graham Jansen, Commented Jun 29, 2020 at 6:57

nbk · Accepted Answer · 2020-06-28 18:49:36Z

2

You must enclose the Double quotes in ENCLOSED BY and i think that you have a finalizing Double quote in you original code

#!/bin/bash 
mysql -u root -p xxxxxxx testdb --local_infile=1 -e"LOAD DATA LOCAL INFILE '/mnt/mysqldb/mysqldb-new/mysql/CK-BATCH-FTP/Acelity/activity.csv' 
INTO TABLE acelity_activity
FIELDS TERMINATED BY ';'
ENCLOSED BY '\"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;"

And you should test the command, in wokbench or phpmyadmin

edited Jun 28, 2020 at 18:49

answered Jun 28, 2020 at 18:41

nbk

49.6k8 gold badges32 silver badges53 bronze badges

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

4 Comments

Graham Jansen Over a year ago

Thanks for the correction on the script, so i run it with no errors though but it did not import anything, after i run the script i got the following it just gave me the complete "help" guide for scripting features. I also did test the query (LOAD DATA LOCAL INFILE....) in phpmyadmin and it works. Ar you sure there should be a '\"' (back slash) at the ENCLOSED BY ?

nbk Over a year ago

yes pretty sure see unix.stackexchange.com/questions/30903/… and stackoverflow.com/questions/22137390/…

Graham Jansen Over a year ago

Thanks got it. So why would I get no error this time but the "help feature guide" after I run the script ? let me know if you need an upload of it ?

nbk Over a year ago

try to move --local_infile=1 to set global before the command, second, login(mysql.exe) and run the command, if that runs and the error log shows nothing, enable general log and see what the command actually looks like that reaches mysql server. you to check all the bases

Graham Jansen · Accepted Answer · 2020-07-07 08:00:56Z

0

Thanks for the help @nbk

So i modified the script above a little, and the following worked for me: (I removed the db name in the mysql syntax and add it in the query)

mysql -u root -pxxxxxxx -e"LOAD DATA LOCAL INFILE '/mnt/mysqldb/mysqldb-new/mysql/CK-BATCH-FTP/File/XXXX.csv' 
INTO TABLE <DB NAME>.<TABLE NAME>
FIELDS TERMINATED BY ';'
ENCLOSED BY '\"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;"

answered Jul 7, 2020 at 8:00

Graham Jansen

251 silver badge7 bronze badges

Comments

Atul · Accepted Answer · 2021-02-17 04:22:36Z

0

If you are using Mysql8 then this will help you.

mysql -udbuser -pXXXXX -h host-pc test1 --local_infile=1 -e "LOAD DATA LOCAL INFILE '/home/abc/data.csv' INTO TABLE tempTable FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n' (col1, col2);";

Make sure there is no space in username and password i.e. it should be like this as mentioned in the above command.

Also ENCLOSED BY needs to write in the mentioned format only.

answered Feb 17, 2021 at 4:22

Atul

3,45734 silver badges47 bronze badges

Collectives™ on Stack Overflow

MySQL - LOAD DATA LOCAL INFILE shell script not working

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related