CODEWITHSUNDEEP
mysqlshellsh
Graham Jansen
Graham Jansen

Reputation: 25

MySQL - LOAD DATA LOCAL INFILE shell script not working

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

Upvotes: 1

Views: 2185

Answers (3)

Atul
Atul

Reputation: 3377

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.

Upvotes: 0

Graham Jansen
Graham Jansen

Reputation: 25

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;"

Upvotes: 0

nbk
nbk

Reputation: 49410

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

Upvotes: 2

Related Questions