Importing CSV to MySQL database inserting only first row

Question

I ran below SQL query against my MySQL database:

load data local infile '/tmp/ad_packs.csv' 
into table ad_packs 
fields terminated by ',' 
enclosed by '"' 
lines terminated by '\n' 
(company_name,pack_size,remaining,ad_pack_purchase_officer,advertising_contact_officer,updated_ad_pack,email,phone,website,note,organisation_id,categories,modified)

This seems to insert only one row and I already checked the csv and it has heading so it only inserts the first row which is the header.

Is there a reason why?

If you are using Unix or have access to Unix commands, try running the command "od -a" on the data file. ("od" stands for octal dump.) It will show you exactly the character(s) used to terminate the lines. Is there a similar command in Windows? — Gordon Linoff
– Gordon Linoff, Commented May 14, 2012 at 13:33

Scott · Accepted Answer · 2012-10-24 10:36:38Z

2

Belated answer, I have just had this problem myself; Tried everything. The solution for me was to set AUTO_INCREMENT against my id field, and voila in came the records.

edited Oct 24, 2012 at 10:36

Scott

21.6k8 gold badges67 silver badges73 bronze badges

answered Oct 24, 2012 at 10:12

David Steer

212 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

John Conde · Accepted Answer · 2012-05-14 13:30:20Z

1

Are you sure sure your lines are terminated by "\n"? Try "\r\n";

answered May 14, 2012 at 13:30

John Conde

220k100 gold badges464 silver badges504 bronze badges

4 Comments

John Kim Over a year ago

Actually the csv doesn't have " quotation marks enclosed with values. Is that a reason why? If so, can we still import without those?

John Conde Over a year ago

Your import statement does say "enclosed by '"' " so that could very well be the problem

John Kim Over a year ago

I tried taking it out but it still imports only one row. Is there an easy way to enclose this quotation on values?

John Conde Over a year ago

If you're using PHP i'd recommend looking at the csv functions for importing the data. They're handle all of the issues for you.

phybiol · Accepted Answer · 2012-08-18 00:12:18Z

1

Try to look into your primary key of table. If no problem with the '\n', '\r\n' issue, it is very likely the first row and second row in your csv file share the same primary key.

answered Aug 18, 2012 at 0:12

phybiol

111 bronze badge

Comments

Animorv · Accepted Answer · 2013-04-28 20:03:24Z

0

Had the same issue just recently and used the od -c command on the csv file. It showed that \r was being use for the line breaks.

answered Apr 28, 2013 at 20:03

Animorv

1

Collectives™ on Stack Overflow

Importing CSV to MySQL database inserting only first row

4 Answers 4

Comments

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related