LOAD DATA LOCAL INFILE loading one record only

1
votes

In MySQL I use LOAD DATA LOCAL INFILE like this:

LOAD DATA LOCAL INFILE 'code.csv' 
INTO TABLE MyDB.code FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' LINES TERMINATED BY '\r\n' (code_id,code_name,code_date);

I get a response like this:

Query OK, 1 row affected, 2 warnings (1.80 sec) Records: 665893 Deleted: 0 Skipped: 665892 Warnings: 1

I am using Ubuntu and MySQL is running on localhost. What is causing LOAD DATA LOCAL INFILE command to skip these records?

2
mysqlload-data-infile

2 Answers

1
votes

Have your verified the lines are actually terminated by \r\n? Also, have you exported PROPER CSV, or just comma-delimitation? If your values are not properly quoted (as you've specified), that could also lead to an import issue.

You may want to try and removing the line-break specification:

LOAD DATA LOCAL INFILE 'code.csv' INTO TABLE MyDB.code FIELDS TERMINATED BY ',' ENCLOSED BY '"' (code_id,code_name,code_date);
0
votes

If you have any unique constraints on your table then LOAD DATA command will skip the rows which violate that constraint.

An illustration of the problem here: https://stackoverflow.com/a/20913896/445131

To fix it, either remove the unique constraint on that table, or remove the rows that are violating that constraint in the text file.