Edit report at https://bugs.php.net/bug.php?id=74713&edit=1
ID: 74713
User updated by: andreas at dqxtech dot net
Reported by: andreas at dqxtech dot net
Summary: CSV cell split after fputcsv() + fgetcsv() round
trip.
Status: Open
Type: Bug
Package: Filesystem function related
Operating System: Linux / 3v4l
PHP Version: 7.1.5
Block user comment: N
Private report: N
New Comment:
> Any fix around the behaviour you're seeing will result in a BC break for some existing
> applications.
Afaik, CSV implementations in other languages / platforms do not have a special "escape
character", and instead just duplicate all quotes (enclosure chars) in the cell text.
fputcsv() does not seem to support this. Someone on stackoverflow suggested to call fputcsv() with
$escape_char === $enclosure, so fputcsv($handle, $fields, ',', '"',
'"'). But this did not solve the problem.
Maybe we could allow another value for $escape_char that was previously not allowed. E.g.
$escape_char === FALSE or TRUE (pick one). So fputcsv($handle, $fields, ',',
'"', FALSE).
If $escape_char is FALSE, fputcsv() could run a standards-compliant behavior.
------
> I'd recommend using a userland CSV parser instead.
I actually did end up writing my own userland CSV parser.
It was very very easy with files I encoded myself, that correctly duplicated all quotes. Here my own
fgetcsv() only needed to count the quotes in a piece of data, and see if they are even.
However, it is more tricky with CSV coming from 3rd parties, which is often poor quality, and may
have rogue quotes that are not properly duplicated.
All I would ask here is for fgetcsv($handle, $length, ',', '"', FALSE) to
reliably decode correct CSV files, and have a sane fallback behavior for incorrect CSV files.
So:
- If a cell does NOT begin with a quote, it ends at the next comma or line break. Any quotes within
the cell are read as-is, and considered part of the cell content.
- If a cell DOES begin with a quote, but contains non-duplicate quotes surrounded by text, it ends
at the next comma or line break. Any quotes within the cell, and the one at the beginning, are read
as-is, and considered part of the cell content.
- If a cell DOES begin with a quote, and does not contain rogue quotes, it ends at the first comma
or line break after an even number of quotes.
I hope this makes sense.
- If a cell begins with a quote, we look for the next comma or line break after an even number of
quotes.
-
Previous Comments:
------------------------------------------------------------------------
[2017-06-12 01:27:56] danack@php.net
I looked at fixing the behaviour of the CSV functions before......basically, I'm not sure it is
fixable in a way that would be acceptable. Any fix around the behaviour you're seeing will
result in a BC break for some existing applications.
I'd recommend using a userland CSV parser instead.
------------------------------------------------------------------------
[2017-06-08 17:14:10] andreas at dqxtech dot net
Description:
------------
If one cell of the data sent to fputcsv() contains
"{$enclosure}{$escape_char}{$escape_char}{$enclosure}{$delimiter}", this cell will be
split after a round trip of fputcsv() + fgetcsv().
E.g. with specific choice of delimiter, enclosure and escape character:
Send ['"@@","B"'] to fputcsv() as a row of data.
fgetcsv() gives you back ['"@@', 'B"""'].
https://3v4l.org/3ZUO8
The reason is that fputcsv() writes
'"""@@",""B"""' to the file, instead of
'"""@@"",""B"""'.
In fact, writing the latter explicitly with fwrite() instead of fputcsv() fixes the problem:
https://3v4l.org/mjQRi
See https://stackoverflow.com/questions/44427926/data-gets-garbled-when-writing-to-csv-with-fputcsv-fgetcsv
Especially this answer, https://stackoverflow.com/a/44441433/246724
Test script:
---------------
$delimiter = ',';
$enclosure = '"';
$escape_char = "@";
$row_before =
["{$enclosure}{$escape_char}{$escape_char}{$enclosure}{$delimiter}{$enclosure}B{$enclosure}"];
print "\nBEFORE:\n";
var_export($row_before);
print "\n";
$fh = fopen($file = 'php://temp', 'rb+');
fputcsv($fh,$row_before,$delimiter,$enclosure, $escape_char);
# fwrite($fh, '"""@@"",""B"""');
rewind($fh);
$row_plain = fread($fh, 1000);
print "\nPLAIN:\n";
var_export($row_plain);
print "\n";
rewind($fh);
$row_after = fgetcsv($fh, 500,$delimiter,$enclosure, $escape_char);
print "\nAFTER:\n";
var_export($row_after);
print "\n\n";
fclose($fh);
Expected result:
----------------
BEFORE:
array (
0 => '"@@","B"',
)
PLAIN:
'"""@@"",""B"""
'
AFTER:
array (
0 => '"@@","B"',
)
Actual result:
--------------
BEFORE:
array (
0 => '"@@","B"',
)
PLAIN:
'"""@@",""B"""
'
AFTER:
array (
0 => '"@@',
1 => 'B"""',
)
------------------------------------------------------------------------
--
Edit this bug report at https://bugs.php.net/bug.php?id=74713&edit=1