LOAD DATA INFILE only 1 record inserted

Question

I have a csv file that I'm trying to import via the command line. But only 1 row is being inserted. They are comma separated values. I'm on a Mac using Excel Mac. I save as a csv file. How can I tell if the lines are terminated by \r or \n or both? Here is the code I used:

LOAD DATA LOCAL INFILE '/Users/eric/Documents/contacts_test.csv' INTO TABLE `contacts_tmp` FIELDS TERMINATED BY ',' ESCAPED BY '\\' LINES TERMINATED BY '\n' (clientid,contactid,title,fname,mname,lname,suffixname,salutation,occupation,employer,home_addr1,home_addr2,home_city,home_state,home_zip,home_county,primary_addr1,primary_addr2,primary_city,primary_state,primary_zip,primary_county,work_addr1,work_addr2,work_city,work_state,work_zip,work_county,email,phone_home,phone_mobile,phone_work,fax,phone_other,codes);

thanks

Answer 1

I would just recommend trying the same command with ... LINES TERMINATED BY '\r\n' ... and see if you have better luck.

Answer 2

Had the same issue, LINES TERMINATED BY '\r' did the job.

Answer 3

If in your file the line is terminated by the ,, then you should add LINES TERMINATED BY ',\r\n'.

This will solve your issue as it did with mine.