CODEWITHSUNDEEP
mysqllinuxbashterminal
Nomad
Nomad

Reputation: 268

Linux bash MySQL load infile

I'm trying to simplify a load data local infile, by putting it into a .sh file, and bash to run it.

Here is my count_portal.sh file

mysql -h host -u root -p password 
load data local infile
"/workplace/user/dump/count_portal.txt" 
replace into table test.icqa_count_portal 
fields terminated by '\t' lines terminated by '\n' ignore 1 lines;

And here is my bash script

bash /home/user/Desktop/count_portal.sh I get an output that doesn't do what it is designed to do. When I simply make the count_portal.sh contain mysql -h host it longs in when running the script.

Upvotes: 1

Views: 7461

Answers (5)

vishu9219
vishu9219

Reputation: 791

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 '\"'"

Upvotes: 0

Nomad
Nomad

Reputation: 268

I figured it out. Here is my file.

#!/bin/bash
/usr/bin/mysql --host=host --user=root --password=password --database=test<<EOFMYSQL
load data local infile '/workplace/user/ETLdump/count_portal.txt' replace INTO TABLE count_portal fields terminated by '\t' LINES
TERMINATED BY '\n' ignore 1 lines;
EOFMYSQL

Works flawlessly!

Upvotes: 1

Jason Heo
Jason Heo

Reputation: 10236

I agree with @PasteBT, his answer should work well. I think there are escape problem or shell variable is empty.

Have you tried this?:

echo "LOAD DATA LOCAL INFILE '/workplace/user/dump/count_portal.txt' REPLACE INTO TABLE test.icqa_count_portal FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' IGNORE 1 LINES;" | mysql -h host -u root -ppassword

and you original shell script is wrong. could you post again what did you do?

Upvotes: 0

PasteBT
PasteBT

Reputation: 2198

missed -e ?

mysql -h host -u root -ppassword dbname -e "load data local infile '/workplace/user/dump/count_portal.txt' replace into table test.icqa_count_portal fields terminated by '\t' lines terminated by '\n' ignore 1 lines;"

Upvotes: 0

RustProof Labs
RustProof Labs

Reputation: 1277

Try removing the space between the user and password switches. So it would be something like:

mysql -h host -uroot -ppassword etc....

The spaces seem to cause problems for me when doing similar things inside bash scripts.

Upvotes: 0

Related Questions