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

Command as such doesnt have any problem. Did you check the command in Windows or Linux? — Sandeep, Feb 07 '10 at 02:14

score 5 · Answer 1 · answered Nov 19 '13 at 06:04

5

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

answered Nov 19 '13 at 06:04

Mauro

3,946
2
27
41

score 5 · Answer 2 · answered Feb 07 '10 at 01:51

5

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

answered Feb 07 '10 at 01:51

hhunter

172
6

Mac text files are generally ended with '\n' nowadays. An older class app might still use '\r' but that's pretty rare these days. – wadesworld Feb 07 '10 at 02:07
its not working i used this content in csv https://sodocumentation.net/mysql/topic/2356/load-data-infile – Anoop P S Dec 31 '20 at 10:10

score 3 · Answer 3 · edited Sep 05 '12 at 10:39

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.

edited Sep 05 '12 at 10:39

Taz

3,718
2
37
59

answered May 16 '12 at 08:37

abdelali

31
2

score 1 · Answer 4 · answered Feb 07 '10 at 02:01

1

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.

answered Feb 07 '10 at 02:01

Alex Budovski

17,947
6
53
58

1

'\n' is more common nowadays. See comment above. – wadesworld Feb 07 '10 at 02:08
Thanks! Got the same problem and this one solved the problem! – datasn.io Feb 07 '12 at 01:08
I read somewhere that it was Mac OS 9 or older that uses '\r'. Mac OS X uses '\n'. – Maurice Lam May 16 '12 at 02:18

score 1 · Answer 5 · answered Feb 08 '10 at 13:53

1

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

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

answered Feb 08 '10 at 13:53

Brian

26,662
52
135
170

1

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. – Maurice Lam May 16 '12 at 02:20

LOAD DATA INFILE only 1 record inserted

5 Answers5

Linked