Search code examples
mysqlload-data-infile

LOAD DATA LOCAL INFILE loading one record only

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?

Solution

  • 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);