Insert CSV to MySQL using Ubuntu Terminal via shell script

Question

Is it possible to insert a CSV file into MySQL using a shell script in Ubuntu?

Here's what I tried :

mysql -uroot -proot mysfdb < /home/sf/data.csv

But I am given an error

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

Here's a sample content from the CSV file:

showinventory_SST312V8N4615041313_1366009574txt_,800-200002.A0,00007985

Any ideas?

Answer 1

Maksym Polshcha's answer is correct but is only missing a few things. Apparently, since it's a local file, I have to declare it as a local file in the mysql command. The final command should be something like this:

 mysql -uroot -proot --local_infile=1 3parsfdb -e "LOAD DATA LOCAL INFILE '/logfiles/Bat_res.csv' INTO TABLE Bat_res FIELDS TERMINATED BY ','"

Also I made sure that the /logfiles directory and the Bat_res.csv are world readable.

Thank you for the great answers.

Answer 2

Try this:

mysql -uroot -proot mysfdb -e "LOAD DATA INFILE '/home/sf/data.csv' INTO TABLE mytable"

where mytable is your table for the data. If you have non-standard field/line separators in your CSV file use FIELDS TERMINATED BY and LINES TERMINATED BY

See http://dev.mysql.com/doc/refman/5.1/en/load-data.html