note 105981 modified in function.str-getcsv by danbrown
| From: | danbrown@php.net | Date: | Fri, 30 Sep 2011 21:40:21 +0000 |
| Subject: | note 105981 modified in function.str-getcsv by danbrown | ||
| References: | 1 | Groups: | php.notes |
| Request: | Send a blank email to php-notes+get-183569@lists.php.net to get a copy of this message | ||
Note that the CSV format handled by this function is not even close to being the same as the CSV
emitted and consumed by spreadsheet programs. In truth, one could argue that the real CSV
"specification" is actually just the behavior of Microsoft Excel and not an RFC that was
written 20 years after Excel used CSV (although thankfully RFC 4180 is actually the same as Excel
behavior with the exception that Excel actually uses a tab as a separator by default).
CSV used by spreadsheet programs like Excel do not use escape characters to escape stray separator
characters. Again, the default separator character is actually a tab and not a comma (but
historically this is usually still referred to it as CSV). Excel uses quotes to enclose an element
that has literal separator characters in it (and an extra quote to escape each literal quote within
the quotes - strange but true). Another quirk is that Excel will not trim extra space around
elements including line breaks. Meaning you can have multiple lines of text within an element by
just putting it in quotes. See the Wikipedia entry for "Comma-separated values" for good
examples that have quotes and escaped quotes and newlines and so on.
Note that if you want to generate data that Excel will load into cells when someone clicks on a
link, use tab as a separator, quote elements that have tabs or quotes and escape literal quotes with
an extra preceding quote.
The following routine can be used to properly quote an element and escape quotes with an extra quote
in the way Excel expects:
<?php
function escapeCsvElement($str, $sep) {
$quot = false;
$si = 0;
$slen = strlen($str);
$ret = '';
while ($si < $slen) {
$ch = $str[$si];
if ($ch == $sep)
$quot = true;
if ($ch == '"') {
$quot = true;
$ret .= '"';
}
$ret .= $ch;
$si++;
}
if ($quot)
return '"' . $ret . '"';
return $str;
}
?>
Then write each row to the browser terminated by carriage-return / newline linebreaks and send
headers like:
<?php
header('Content-type: application/csv');
header('Content-Disposition: attachment; filename=' . $filename);
?>
This is what Excel and other spreadsheet programs expect to automatically load your cell data as
expected without triggering the file import wizard.
As for parsing real CSV, the *getcsv functions are not going to work. To parse the quotes and
escaped quotes and literal line breaks and so on, that would be handled best with a proper
state-machine parser.
--was--
Note that the CSV format handled by this function is not even close to being the same as the CSV
emitted and consumed by spreadsheet programs. In truth, one could argue that the real CSV
"specification" is actually just the behavior of Microsoft Excel and not an RFC that was
written 20 years after Excel used CSV (although thankfully RFC 4180 is actually the same as Excel
behavior with the exception that Excel actually uses a tab as a separator by default).
CSV used by spreadsheet programs like Excel do not use escape characters to escape stray separator
characters. Again, the default separator character is actually a tab and not a comma (but
historically this is usually still referred to it as CSV). Excel uses quotes to enclose an element
that has literal separator characters in it (and an extra quote to escape each literal quote within
the quotes - strange but true). Another quirk is that Excel will not trim extra space around
elements including line breaks. Meaning you can have multiple lines of text within an element by
just putting it in quotes. See the Wikipedia entry for "Comma-separated values" for good
examples that have quotes and escaped quotes and newlines and so on.
Note that if you want to generate data that Excel will load into cells when someone clicks on a
link, use tab as a separator, quote elements that have tabs or quotes and escape literal quotes with
an extra preceding quote.
The following routine can be used to properly quote an element and escape quotes with an extra quote
in the way Excel expects:
function escapeCsvElement($str, $sep) {
$quot = false;
$si = 0;
$slen = strlen($str);
$ret = '';
while ($si < $slen) {
$ch = $str[$si];
if ($ch == $sep)
$quot = true;
if ($ch == '"') {
$quot = true;
$ret .= '"';
}
$ret .= $ch;
$si++;
}
if ($quot)
return '"' . $ret . '"';
return $str;
}
Then write each row to the browser terminated by carriage-return / newline linebreaks and send
headers like:
header('Content-type: application/csv');
header('Content-Disposition: attachment; filename=' . $filename);
This is what Excel and other spreadsheet programs expect to automatically load your cell data as
expected without triggering the file import wizard.
As for parsing real CSV, the *getcsv functions are not going to work. To parse the quotes and
escaped quotes and literal line breaks and so on, that would be handled best with a proper
state-machine parser.
http://php.net/manual/en/function.str-getcsv.php