0

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!

|improve this question
  • Is the behaviour the same when you try to run the command above from PhpMyAdmin? If yes, then the problem is somewhere in the options passed to it - or the CSV itself. – dezso Dec 17 '12 at 8:04
  • Yes, the behaviour is the same in PHPMyAdmin. I believe the issue may be in the options or the CSV itself. That's why I provide a link to the files I'm using. Thanks. – AME Dec 17 '12 at 8:08
  • Does it work when you replace ""As Is Sale"" with """As Is Sale"""? Anyway, if you simply don't use all the double quotes, you don't need to worry about those in the fields. – dezso Dec 17 '12 at 8:14
  • It doesn't work when I add quotations. I am looking for a way to escape the quotations. Do you know how I can do this? – AME Dec 17 '12 at 8:28
  • Simply don't use quotation around ordinary text. This way the quotes will be handled as a part of the text field. Possibly you want to choose a different delimiter (say a tab) instead of commas. – dezso Dec 17 '12 at 9:13
1

What you're looking for is...

ESCAPED BY '"'

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

Occurrences of the ENCLOSED BY character preceded by the ESCAPED BY character are interpreted as part of the current field value.

...which is exactly what you're needing.

|improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.