escaping commas for csv output
Bruce
of the fields, however, it will interpret to go to next column. Is there
a workaround? Thanks.
$fp = fopen('my.csv','a');
$content = "$var1,$var2,$var3...
fwrite($fp,$content);
Michael Fesser
fputcsv()
Micha
Sjoerd
> I'm outputting form content into a csv file. If a comma is used in one
> of the fields, however, it will interpret to go to next column.
You should use fputcsv().
Alternatively, there are two typical solutions for this:
1) use another delimiter which is less likely to occur in the data
2) escape the delimiter
Of course, you need both.
Solution 1: Consider for example that every var is quoted like this:
"banana", "apple", "pear"
Now, the delimiter has become ", ", and there is a " at the beginning and
the end. However, if the string ", " occurs in the data, you still have a
problem.
Solution 2: Consider that a comma may occur in the data. You have to
distinguish between a comma in the data and a comma at the end of the
data. This is done by preceding the comma with an escape character:
banana, apple, pear, hello\, I said
Here, the comma in the data is preceded by a backslash, to indicate that
it belongs to the data. But now, if you want to put \, in the data, it
will remove the backslash. Therefore, you also escape the backslash, like
so: \\,
A backslash is a typical escape character, but fputcsv() uses quotes to
escape quotes, so "" becomes ":
"banana", "apple", """Hello"", I said"
Bruce
>>
>> $fp = fopen('my.csv','a');
>> $content = "$var1,$var2,$var3...
>> fwrite($fp,$content);
>
> fputcsv()
>
> Micha
I'm assuming replacing fwrite() with fputcsv().
Was there something else (it did not work)?
Michael Fesser
>Michael Fesser wrote:
>>>
>>> $fp = fopen('my.csv','a');
>>> $content = "$var1,$var2,$var3...
>>> fwrite($fp,$content);
>>
>> fputcsv()
>>
>> Micha
>
>I'm assuming replacing fwrite() with fputcsv().
Not exactly.
>Was there something else (it did not work)?
Sometimes it helps to have a look at the manual. There's even an
example.
Micha
Gordon
If your aim is to write out data that would be compatible with
spreadsheet software, then the standard solution is to enclose any
field that's a string in quotes. For example, if your data was:
1,2,3,a,b,c,a man, a plan, a canal, panama
then rendering it as follows should produce the correct results when
you import into your CSV supporting spreadsheet application:
"1","2","3","a","b","c","a man, a plan, a canal, panama"
Of course, you then hit the problem of what to do if your string
contains a " character?
1,2,3,a,b,c,a man, a plan, a canal, "panama"
The answer is that you can escape the " character by doubling it up in
the output.
"1","2","3","a","b","c","a man, a plan, a canal, ""panama""
Data formatted like this should be imported correctly into most
software that supports with CSV. Do bear in mind, that Microsoft
Excel can have problems if any of your data contains a newline (the
general consensus is that if the newline is contained within quotes it
should be treated as part of the field instead of a line terminator,
but Excel seems to ignore that).
Captain Paralytic
If you're planning on using the output for Excel, I would recommend
using tabs instead of commas to delimit the file.
Jeff
Instead of writing to a file what if we just wanted to "write" to a
variable, and later write a header and echo that variable. Is there a
php method of doing that (I couldn't find it). Or should I just roll my
own, I was going to do that (doesn't seem like much) until I read this
thread...
Jeff
>
> Micha
Captain Paralytic
> Michael Fesser wrote:
> > .oO(Bruce)
>
> >> Michael Fesser wrote:
> >>>> $fp = fopen('my.csv','a');
> >>>> $content = "$var1,$var2,$var3...
> >>>> fwrite($fp,$content);
> >>> fputcsv()
>
> >>> Micha
> >> I'm assuming replacing fwrite() with fputcsv().
>
> > Not exactly.
>
> >> Was there something else (it did not work)?
>
> > Sometimes it helps to have a look at the manual. There's even an
> > example.
>
> Instead of writing to a file what if we just wanted to "write" to a
> variable, and later write a header and echo that variable. Is there a
> php method of doing that (I couldn't find it).
Jeff
Well, I found this:
$csv = fopen('php://temp/maxmemory:'. (5*1024*1024), 'r+');
fputcsv($csv, array('blah','blah'));
rewind($csv);
// put it all in a variable
$output = stream_get_contents($csv);
But frankly, I don't understand why there isn't a more direct method.
function csvLine($array){
$line = array();
foreach($array as $item){
$item = str_replace('"','""',$item);
$line[] = '"' . $item . '"';
}
$content = implode(',',$line);
return $content . "\n";
}
I don't believe Excell would have any trouble opening that.
Have I missed something?
Jeff