fputcsv and newline codes

Question

I'm using fputcsv in PHP to output a comma-delimited file of a database query. When opening the file in gedit in Ubuntu, it looks correct - each record has a line break (no visible line break characters, but you can tell each record is separated,and opening it in OpenOffice spreadsheet allows me to view the file correctly.)

However, we're sending these files on to a client on Windows, and on their systems, the file comes in as one big, long line. Opening it in Excel, it doesn't recognize multiple lines at all.

I've read several questions on here that are pretty similar, including this one, which includes a link to the really informative Great Newline Schism explanation.

Unfortunately, we can't just tell our clients to open the files in a "smarter" editor. They need to be able to open them in Excel. Is there any programmatic way to ensure that the correct newline characters are added so the file can be opened in a spreadsheet program on any OS?

I'm already using a custom function to force quotes around all values, since fputcsv is selective about it. I've tried doing something like this:

function my_fputcsv($handle, $fieldsarray, $delimiter = "~", $enclosure ='"'){

        $glue = $enclosure . $delimiter . $enclosure;

    return fwrite($handle, $enclosure . implode($glue,$fieldsarray) . $enclosure."\r\n");

}

But when the file is opened in a Windows text editor, it still shows up as a single long line.

Answer 1

// Writes an array to an open CSV file with a custom end of line.
//
// $fp: a seekable file pointer. Most file pointers are seekable, 
//   but some are not. example: fopen('php://output', 'w') is not seekable.
// $eol: probably one of "\r\n", "\n", or for super old macs: "\r"
function fputcsv_eol($fp, $array, $eol) {
  fputcsv($fp, $array);
  if("\n" != $eol && 0 === fseek($fp, -1, SEEK_CUR)) {
    fwrite($fp, $eol);
  }
}

Answer 2

This is an improved version of @John Douthat's great answer, preserving the possibility of using custom delimiters and enclosures and returning fputcsv's original output:

function fputcsv_eol($handle, $array, $delimiter = ',', $enclosure = '"', $eol = "\n") {
    $return = fputcsv($handle, $array, $delimiter, $enclosure);
    if($return !== FALSE && "\n" != $eol && 0 === fseek($handle, -1, SEEK_CUR)) {
        fwrite($handle, $eol);
    }
    return $return;
}

Answer 3

Using the php function fputcsv writes only \n and cannot be customized. This makes the function worthless for microsoft environment although some packages will detect the linux newline also.

Still the benefits of fputcsv kept me digging into a solution to replace the newline character just before sending to the file. This can be done by streaming the fputcsv to the build in php temp stream first. Then adapt the newline character(s) to whatever you want and then save to file. Like this:

function getcsvline($list,  $seperator, $enclosure, $newline = "" ){
    $fp = fopen('php://temp', 'r+'); 

    fputcsv($fp, $list, $seperator, $enclosure );
    rewind($fp);

    $line = fgets($fp);
    if( $newline and $newline != "\n" ) {
      if( $line[strlen($line)-2] != "\r" and $line[strlen($line)-1] == "\n") {
        $line = substr_replace($line,"",-1) . $newline;
      } else {
        // return the line as is (literal string)
        //die( 'original csv line is already \r\n style' );
      }
    }

        return $line;
}

/* to call the function with the array $row and save to file with filehandle $fp */
$line = getcsvline( $row, ",", "\"", "\r\n" );
fwrite( $fp, $line);

Answer 4

As webbiedave pointed out (thx!) probably the cleanest way is to use a stream filter.

It is a bit more complex than other solutions, but even works on streams that are not editable after writing to them (like a download using $handle = fopen('php://output', 'w'); )

Here is my approach:

class StreamFilterNewlines extends php_user_filter {
    function filter($in, $out, &$consumed, $closing) {

        while ( $bucket = stream_bucket_make_writeable($in) ) {
            $bucket->data = preg_replace('/([^\r])\n/', "$1\r\n", $bucket->data);
            $consumed += $bucket->datalen;
            stream_bucket_append($out, $bucket);
        }
        return PSFS_PASS_ON;
    }
}

stream_filter_register("newlines", "StreamFilterNewlines");
stream_filter_append($handle, "newlines");

fputcsv($handle, $list, $seperator, $enclosure);
...

Answer 5

alternatively, you can output in native unix format (\n only) then run unix2dos on the resulting file to convert to \r\n in the appropriate places. Just be careful that your data contains no \n's . Also, I see you are using a default separator of ~ . try a default separator of \t .

Answer 6

I've been dealing with a similiar situation. Here's a solution I've found that outputs CSV files with windows friendly line-endings.

http://www.php.net/manual/en/function.fputcsv.php#90883

I wasn't able to use the since I'm trying to stream a file to the client and can't use the fseeks.

Answer 7

PHP 8.1.0 has introduced a new option for eol, see the official document

Answer 8

windows needs \r\n as the linebreak/carriage return combo in order to show separate lines.

Answer 9

I did eventually get an answer over at experts-exchange; here's what worked:

function my_fputcsv($handle, $fieldsarray, $delimiter = "~", $enclosure ='"'){
   $glue = $enclosure . $delimiter . $enclosure;
   return fwrite($handle, $enclosure . implode($glue,$fieldsarray) . $enclosure.PHP_EOL);
}

to be used in place of standard fputcsv.