When creating a CSV file, how to handle the characters \ n and \ r in a field?

Question

I am writing a function to handle CSV output using fputcsv() . I worked on the \r\n problem that so many people had with fputcsv() (see Code).

Now I'm trying to figure out how to handle the characters \r or \n that are included in the field (and not the end of the string returns \r\n ). Should he somehow escape before going into fputcsv() ?

The function handles escaping for most characters. But, when a \n inserted into the field, both MS Excel and Google Docs have problems with \n , and CSV cannot load correctly.

 /* * Revised Get CSV Line using PHP fputcsv() * * Used to correct an issue with fputcsv() * http://stackoverflow.com/questions/4080456/fputcsv-and-newline-codes * MS Excel needs the MS Windows newline format of \r\n * */ if (!function_exists('get_csv_line')) { function get_csv_line($list, $seperator = ",", $enclosure = '"', $newline = "\r\n") { $fp = fopen('php://temp', 'r+'); fputcsv($fp, $list, $seperator, $enclosure ); rewind($fp); $line = fgets($fp); if ($newline && $newline != "\n") { if ($line[strlen($line)-2] != "\r" && $line[strlen($line)-1] == "\n") { $line = substr_replace($line,"",-1) . $newline; } else { die( 'original csv line is already \r\n style' ); } } if ($newline == "\r\n" && substr($line, -2) != "\r\n") { log_message('error', 'function get_csv_line: Error, needs \r\n to be MS Excel friendly!'); } return $line; } } 

Answer 1

If there is \ r or \ n in the field, just make sure the whole field is enclosed in double quotes (and that literal double quotes have double double quotes)

So for the final result

 value1,"so this is a multiline value",value2 

where 'a' and 'multiline' are separated by either \ n or \ r (remember, \ r does not appear on excel, but there), then it should work.

If the values ​​\ n or \ r are embedded in the values ​​that you have in the PHP array, then it should already be enclosed accordingly.

Answer 2

UTF files containing the specification will force Excel to process newlines literally, even in this field surrounded by quotation marks. (Tested Excel 2008 Mac)

The solution is to make any new lines a carriage return (CHR 13), not a line.