2

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?

  • 1
    This can help you: http://stackoverflow.com/questions/2811096/mysql-bulk-insert-from-csv-data-files?rq=1 – fedorqui Apr 15 '13 at 08:51

4 Answers4

8

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.

4

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

Maksym Polshcha
  • 18,030
  • 8
  • 52
  • 77
  • Thanks for the answer, however when I enter the command, it returns this error : `ERROR 13 (HY000) at line 1: Can't get stat of /home/sf/data.csv (Errcode:2)` –  Apr 15 '13 at 09:23
  • 1
    The errors means that data file in not accessible in `/home/sf/data.csv` – Alepac Apr 15 '13 at 09:30
  • I am getting error : `ERROR 29 (HY000) at line 1: File '/var/www/html/random_data_Sheet_2.csv' not found (Errcode: 13) ` but file exist on that pathh and it is accessible. What could be the issue? – Asmita Nov 16 '15 at 07:30
  • @Asmita It must be accessible by a user who owns mysqld process. – Maksym Polshcha Nov 16 '15 at 07:58
1

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';
Unihedron
  • 10,902
  • 13
  • 62
  • 72
Ashish kumar Pandey
  • 11
  • 2
0

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.

Sanaulla
  • 1,329
  • 14
  • 13