CODEWITHSUNDEEP
mysqlshellubuntucsv
user2281648
user2281648

Reputation:

Insert CSV to MySQL using Ubuntu Terminal via shell script

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?

Upvotes: 2

Views: 31654

Answers (4)

Sanaulla
Sanaulla

Reputation: 1609

Open Ubuntu Terminal and just run the following command

# mysql -u admin -p --local_infile=1 DATABASE_NAME -e "LOAD DATA LOCAL INFILE 'students.csv' INTO TABLE TABLE_NAME FIELDS TERMINATED BY ',' enclosed by '\"'"

Here,

  • DATABASE_NAME = The name of your database
  • students.csv = The CSV file directory, that you want to upload in the database
  • TABLE_NAME = in which table you want to upload your data
  • admin = Database user name

After run this command system asked for the password of the admin user.

Write the password and Enjoy.

Upvotes: 0

Ashish kumar Pandey
Ashish kumar Pandey

Reputation: 11

I used this and it worked.

Login in mysql using `mysql -uroot -ppassword --local-infile`

Then in terminal:

LOAD DATA LOCAL INFILE '.csv path' INTO TABLE table_name FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

Upvotes: 1

user2281648
user2281648

Reputation:

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.

Upvotes: 8

Maksym Polshcha
Maksym Polshcha

Reputation: 18358

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

Upvotes: 4

Related Questions