----- Original Message -----
From: "whoisquilty"
I've got a CSV file in which I need to use double quotes and import into
MySQL. However, I've
tried using a \ in front of each quote. I've tried using the HTML code.
The \ doesn't work (and it's not something I want to mess with during data
entry).
The HTML code gives a parse error.
Any ideas?
Jeremy
----------------------------
Hello Jeremy,
You did not mention how you are importing this file.
There may be another cause to this problem.
Basic CSV rules.
1) commas separate values
2) any field can be enclosed in double quotes
3) fields containing commas must be enclosed in double quotes
4) double quotes within fields must be preceded but a double quote for the
first double quote within the field (from memory)
5) field must be terminated with a \n (linux) or \r (windows)
It may be rule 4 or rule 5 that is giving you trouble and also how the
upload is handled.
example of rule 4
"Field ones value is ""7", "Fields twos value is ""9"
I am not 100% sure about the above
rule 5 -
Linux server requires that lines are terminated with \n while windows PC
terminates lines with \r
Fix this with any decent text editor with search '\r' - replace '\n' for
linux server
OR in PHP open the file and str_replace("\r", "\n", $mycsv) then save file.
OR upload file in 'ascii' mode.
The other issue is how you are uploading the file.
FTP: Binary mode does NOT effect \r or \n so they will not be changed
FTP: ascii mode \r or \n is changed to the correct format for the receiving
server.
If you are using phpmyadmin -
The FILE method - when you brows and select a file on the local PC - is the
same as the FTP binary method except for who is the file owner perhaps.
The POST method - when you paste the text of the csv file into a text box -
is completely different.
The differences are -
The browser will re-encode the text according to its default and escape the
data. As a part of this process, the browser will first convert the text to
a native string.
example -
original csv text pasted -
"Field one is &qt;7&qt;", "Field two is &qt;9&qt;"
is first converted to -
"Field one is ""7"", "Field two is ""9""
then converted to
\"Field one is \"\"7\"\", \"Field two is \"\"9\"\"
The safest way to do this is to format the file on a local PC and then us
FILE upload.
The format you need on your PC is -
for windows server -
"Field one is &qt;7&qt;", "Field two is &qt;9&qt;"\r
for linux server -
"Field one is &qt;7&qt;", "Field two is &qt;9&qt;"\n
