I load a CSV file using MySQL LOAD DATA INFILE, but only half the lines are loaded. I tested different files, and exactly half of the lines will be loaded every time. How can I load all the lines?
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 diagram:
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 line) data set that is stored in a CSV file (only 1 line is loaded):
6682,"AD,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"
Each line has CR / LF line ends, but you do not see them here. In addition, the complete dataset does not have duplicate identifiers (primary keys), and MySQL does not generate any warnings or errors.
I use WAMP on Windows 7. In addition, I cannot use the LOCAL keyword because it will not work on the production server, where this code will eventually live.
Any help or suggestions are greatly appreciated.