I am currently stuck and unable to upload a complete CSV file into a MySQL database. Unfortunately, the nice folks at Stackoverflow haven't been able to help me solve this issue, so I'm hoping someone here may be able to assist me.
I would like to upload a CSV file into a MySQL database programmatically using PHP and MySQL's LOAD DATA INFILE function. This is the code that is auto-generated from PHPMyAdmin's import page:
"LOAD DATA LOCAL INFILE '..path/property_re_1.csv' REPLACE INTO TABLE `markers` FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n'";
The problem is the CSV file only loads until row 44, where there is a text string with quotations.
I have included the actual SQL table and CSV file for debugging purposes since the information used in this case is public information. Feel free to download it:
https://www.dropbox.com/sh/4iq10i51qlqyq8q/UjEQwvXKDA
Ultimately I am looking for a PHP code with the proper SQL function for this task, as I will be running the PHP script routinely to update the database on the server.
Thanks in advance!
Best Answer
What you're looking for is...
In CSV, one way of including literal quotation marks inside a single field is by doubling them up.
In the example file you posted, that's exactly what's happening at line 44.
The rest of your statement looks correct. Replace the
ESCAPED BY
part with what I've shown above, and you should be good.http://dev.mysql.com/doc/refman/5.5/en/load-data.html
...which is exactly what you're needing.