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

Mac text files usually end in \r but you can find this out by using a hex editor and seeing what the lines end with.

Answer 3

Just try removing LINES TERMINATED BY '\n' altogether from your query.

If you don't specify a delimiter, MySQL fill figure it out automatically.