MySQL load data infile function and format of the csv file

Question

So I need some help, I'm stuck on this for hours. So I have this array of data in PHP which I write into a CSV file using the fputcsv function. Now the data gets saved into a CSV file and it is of the following format:

Becoming Human, "becoming_human_uk", "2011", 
Bed of Roses, "bed_of_roses", "2008", 
Bed of Roses, "Bed_of_Roses_2008", "2008", 
Bedlam, "bedlam", "2011",

This goes on for ~10k more rows. Now up to this point it is all hunky dory. However now I want to import all of this data from the CSV to my database table. To do this I use the LOAD DATA INFILE mysql function of the following format:

$sql="LOAD DATA INFILE 'path/series.csv' INTO TABLE series FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\\r\\n' (title, link, year)";

It is now here where the problem lies. Only one row gets inserted into the database and its the first row. I suspect the problem lies in these optional parameters like TERMINATED BY or ENCLOSED BY. I put fields to optionally enclosed by because the first field is not enclosed in "" while the other two are. Also the fields terminate by (,) so I guess that is ok. This only leaves the line termination and I don't know if this is correct. There might be something else wrong with this. Or can I somehow better format the input of the data into the CSV so that all the fields are enclosed the same way. So if someone can help me spot the problem here I would greatly appreciate it. Thanks.

The most common reason I've found for only one row being inserted is the line terminator definition not matching the actual line terminator — Mark Baker
– Mark Baker, Commented May 30, 2013 at 20:13
I can't go from array to db because i have about half a million rows and loading the data from csv to db is faster. At least that's what I'm trying to find out now. — Emcho
– Emcho, Commented May 30, 2013 at 20:16
I also think it's the line terminator but I tried almost everything and it still doesn't work. — Emcho
– Emcho, Commented May 30, 2013 at 20:17
i cant see how the creation of a file then import in to db is faster than going directly to the db. — user557846
– user557846, Commented May 30, 2013 at 20:20

Bill Karwin · Accepted Answer · 2013-05-30 20:28:09Z

6

I tested in MySQL 5.5.31 and this worked:

mysql> LOAD DATA LOCAL INFILE 'series.csv' INTO TABLE series 
  FIELDS TERMINATED BY ','
  OPTIONALLY ENCLOSED BY '\"' 
  LINES TERMINATED BY '\n' 
  (title, link, year);
Query OK, 4 rows affected (0.00 sec)

I had to edit the csv file:

Don't put spaces after commas.
Don't put a comma at the end of the line.
Don't use \r\n if your file contains only \n newlines.
Don't put spaces at the end of the line.
I was testing in the MySQL client, not in PHP, so I removed the double-backslashes.

answered May 30, 2013 at 20:28

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

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

4 Comments

Emcho Over a year ago

Im just about the make the modifications you did. I'll post back the results.

Emcho Over a year ago

Ok I changed the format of the CSV file and now it is like this: data1,data2,data3 - I removed the " and the spaces. I also removed the optionally closed by '\"' parameter now. So fields are terminated by (,) and lines are terminated by (\n). But now I'm not getting anything inputted into the db.

Emcho Over a year ago

No wait it's ok just needed to remove some commas that were in the title. Works now. Thanks a lot for the help Bill.

K. Kilian Lindberg Over a year ago

Just an idea to keep the commas etc in the titles use something quite unlikely as delimiter as for example §§§, fputcsv($fp, $fields, "§§§"); (I guess there is a better way though)

Collectives™ on Stack Overflow

MySQL load data infile function and format of the csv file

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related