MySQL load data infile loading exactly half the records

Question

I am loading a CSV file using MySQL's LOAD DATA INFILE but only half the rows load. I have tested different files and exactly half of the rows will load every time. How can I get all the rows to load?

Here is the LOAD DATA INFILE SQL:

    LOAD DATA INFILE 
    'C:\\Users\\user\\Dropbox\\wamp\\www\\jobdesc\\data\\banp\\pa_class_posn.csv' 
    INTO TABLE pa_class_posn_temp 
    FIELDS TERMINATED BY ',' 
    OPTIONALLY ENCLOSED BY '"' 
    LINES TERMINATED BY '\n'
    (ID,POSN_CLASS_TITLE,SALARY_GRADE,POSN_CLASS_CODE,
     RECRUITMENT_TIER,EXEMPT_NONEXEMPT,REVISED); 

Here is the schema:

   CREATE TABLE IF NOT EXISTS `pa_class_posn_temp` (
    `ID` int(4) NOT NULL,
    `POSN_CLASS_TITLE` varchar(36) DEFAULT NULL,
    `SALARY_GRADE` varchar(4) DEFAULT NULL,
    `POSN_CLASS_CODE` varchar(5) NOT NULL,
    `RECRUITMENT_TIER` varchar(6) DEFAULT NULL,
    `EXEMPT_NONEXEMPT` varchar(10) DEFAULT NULL,
    `REVISED` varchar(10) DEFAULT NULL,
    PRIMARY KEY (`ID`),
    UNIQUE KEY `POSN_CLASS_CODE` (`POSN_CLASS_CODE`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Here is a very simple (two row) data set that is stored in a CSV file (only 1 row will load):

   6682,"A D,Stdnt Hlth&Cnslg Bsn Ops","15","A7078","Tier 1","Exempt","04/19/2013"
   7698,"AVP,Alumni Relations","17","N8004","Tier 2","Exempt","04/19/2013"

There are CR/LF line endings on each row but you can't see them here. Also, in the full dataset there are no duplicate ID (Primary Keys) and MySQL does not generate any warnings or errors.

I am using WAMP on Windows 7. Also, I can't use the LOCAL keyword because that will not work on the production server where this code will eventually live.

Any help or suggestions is much appreciated.

Scrappy

Answer 1

Did you generate your csv file under Windows as well ? If yes, you might have to use LINES TERMINATED BY '\r\n'

Joerg

LINES TERMINATED BY '\r\n' did the trick. I was missing the \r. Thank You! – Scrappy

Can't believe it was that simple. Worked! – Say

Answer 2

I too was facing this issue, the basic principle in this query is that,

if 'CSV' file is generated from :

windows

then new line character is '\r\n' [CR/LF] i.e, TERMINATED BY '\r\n' and for

macOS

then new line character is \n [LF] i.e, TERMINATED BY '\n'

---

A little tip: Working on macOS on IntelliJ, you can easily change the new character in a file simple by clicking on LF (image below)

Biogenesis