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

Slothful

Command as such doesnt have any problem. Did you check the command in Windows or Linux? – Halcomb

Answer 1

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

Scherzando

Answer 2

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

Gavrielle

Mac text files are generally ended with '\n' nowadays. An older class app might still use '\r' but that's pretty rare these days. – Bemused

its not working i used this content in csv sodocumentation.net/mysql/topic/2356/load-data-infile – Darlleen

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.

Adigranth

Answer 4

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.

Distort

'\n' is more common nowadays. See comment above. – Bemused

Thanks! Got the same problem and this one solved the problem! – Lachman

I read somewhere that it was Mac OS 9 or older that uses '\r'. Mac OS X uses '\n'. – Pipsqueak

Answer 5

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

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

Triune

If you specify no FIELDS or LINES clause, the defaults are the same as if you had written this: FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' STARTING BY '' ---- that is what the mySQL documentation says. – Pipsqueak