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.

Accad

+1 for highlighting a problem that I've been having – Bartle

do you include your csv field header ? – Strike

In comparing the above code with the accepted solution (assuming this file is generated on Linux), it looks like the original problem might have been in transferring the file to the client, not necessarily its generation. For instance, downloading the original file (with CR+LF EOL) via FTP in ASCII mode, from Linux to Windows, would have resulted in the line endings being corrupted. – Careless

Answer 1

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.

Accad

I know this is an old answer but I'd be remiss if I didn't mention that this is an awful answer from experts-exchange. It doesn't even handle double-quote escapes. Please use a stream filter, instead. That's why they're there. php.net/manual/en/function.stream-filter-register.php – Severus

Answer 2

// 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);
  }
}

Rainfall

Great answer! I posted an improved version below, preserving the possibility of using custom delimiters and enclosures and returning fputcsv's original output – Viminal

Answer 3

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;
}

Viminal

What is here $handle? – Histidine

@MiomirDancevic it's a resource created with fopen() function – Viminal

Answer 4

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);

One

Answer 5

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);
...

Dada

I don't know what ([^\r])\n is trying to do. Maybe you mean /\R/. – Origami

It replaces lines that end with only newline (/n) which is the standard way to end a line under unix systems, by lines that have carriage return AND newline \r\n which is the way to end lines under Windows. ([^\r])\n means: A character that is not 'carriage return' followed by a 'new line' character – Dada

Seeing it now, it probably could be even improved with (^|[^\r])\n to also include lines that have only a newline – Dada

How about: '/(?<!\r)\n/', "\r\n" to improve pattern performance and eliminate the reference? There will be LOADS of characters that match [^\r], but very few that match \n -- this means that the lookbehind will only be executed when a \n is encountered. – Origami

Would be interesting to test. But sounds legit – Dada

Answer 6

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 .

Irrelevance

Is unix2dos something that can be installed on a server and called programmatically? Because I literally have to manual contact with these files - they get created programmatically and emailed programmatically to the client. – Accad

in php, you can make calls to programs from within the language: – Irrelevance

Thanks. Unfortunately, I've just been told this site is going to be cloud-hosted, so I have no way of knowing if it's installed or not, and no control over getting it installed if it's not. I need a way to do this with only standard php commands. – Accad

Answer 7

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.

Student

Answer 8

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

Slipslop

Answer 9

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

Irrelevance

Yes, I'm aware of that, and if you look at the code I posted you'll see that I'm appending \r\n to the line I'm writing to the file. It doesn't seem to matter - Windows is not reading it that way. – Accad