CODEWITHSUNDEEP
mysqlload-data-infile
user1786284
user1786284

Reputation: 75

LOAD DATA LOCAL INFILE importing only one row in MySQL

Using MySQL Database, I want to import a csv file with 13 columns and 120 rows. I am trying to upload them into a table.

Here is my command:

mysql> load data local infile 'restaurants.csv' 
       into table restaurants_restaurant fields        
       terminated by ',' enclosed by '"' lines terminated by '\n';

Here is what was reported:

Query OK, 1 row affected, 4 warnings (0.05 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 4

Only 1 row was added, and only partially? What am I doing wrong?

My Table Structure:

class Restaurant(models.Model):
name = models.CharField(max_length=200)
category = models.CharField(max_length=200)
city = models.CharField(max_length=200)
location= models.CharField(max_length=200)
address= models.CharField(max_length=500)
phone_number= models.IntegerField()
email= models.CharField(max_length=200)
description= models.CharField(max_length=2000)
hours= models.CharField(max_length=200)
photos= models.CharField(max_length=200)
price= models.CharField(max_length=200)
rating= models.IntegerField()
review= models.CharField(max_length=2000)

First 4 lines of my csv:

American Burger,American,Dhaka,,,,,,,,2 to 4,4
ARIRANG,Korean,Dhaka,,"House 3, Road 51, Gulshan 2, Dhaka, Bangladesh.",(88-02) 989-6453,,,,,,
Aroma,,Dhaka,,,,,,,,15 to 20,3.5
Bacaru,,Dhaka,,,,,,,,,4

Upvotes: 1

Views: 3990

Answers (2)

Eric Leschinski
Eric Leschinski

Reputation: 153822

If you have any unique constraints on your table that your data file rows are violating, then LOAD DATA INFILE will skip them, as illustrated in this post:

https://stackoverflow.com/a/20913896/445131

Either remove all the unique constraints on your table and try again, or remove the duplicates in each column of each row in the text file and try again.

Upvotes: 0

Devart
Devart

Reputation: 121902

In your CSV file line separator is (0D 0A). So, you need to use '\r\n' as line separator - 

...
lines terminated by '\r\n'

Upvotes: 1

Related Questions