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

Question

I am writing a function to handle CSV output using fputcsv(). I've worked around the dreaded \r\n issue that so many people have had with fputcsv() (see code).

Now I'm trying to figure out how to handle \r or \n characters that are included in a field (not the line ending returns \r\n). Should it be escaped somehow before being passed into fputcsv()?

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

/*
* Revised Get CSV Line using PHP's 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;
    }
}

Related question: [Generating CSV file for Excel, how to have a newline inside a value?](http://stackoverflow.com/questions/1241220/generating-csv-file-for-excel-how-to-have-a-newline-inside-a-value) — Anne, Nov 10 '11 at 01:40
I've worked on something like this before, CSV is a terrible thing to work with, you could even face problems like fields containing your enclosure or separation symbol. I end up having to choose some exotic language characters for those symbols, and run through a really long script to check for those \n\r and make sure there are enough enclosure and separation characters. — Jonathan Chow, Oct 12 '12 at 09:24

score 4 · Answer 1 · edited Nov 22 '11 at 06:18

If there are \r or \n within the field just make sure the entire field is enclosed by double quotes (and that literal double quotes are double-double quoted)

So for a final result of

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

where 'a' and 'multiline' are separated by either a \n or \r (remember, \r wont show up on excel, but its there), then it should have worked.

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

score 0 · Answer 2 · answered Dec 09 '11 at 08:06

0

UTF files that contain a BOM will cause Excel to treat new lines literally even in that field is surrounded by quotes. (Tested Excel 2008 Mac)

The solution is to make any new lines a carriage return (CHR 13) rather than a line feed.

answered Dec 09 '11 at 08:06

Stephen

373
2
9

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

2 Answers2