PHP to create CSV that Excel can read

[Phil Powell]

I am using PHP 4.3.2 to create a CSV file, however, Excel constantly
views it as a single-column spreadsheet with everything in quotes,
whereas OpenOffice Calc views it as a legitimate spreadsheet in
separate columns/cells.

[PHP]
class ReportGenerator {

/**
* Generate content based upon type
*
* @access private
* @param mixed $type type of content (e.g. Excel, CSV, Word, etc)
* @return mixed formatted content
*/
function &generateConten t($type) { // STATIC STRING METHOD
$result = $this->getResult();

switch (strtolower($ty pe)) {
case 'excel':
$delimiter = '</td><td valign="top">';
$lb = "\n</tr>\n<tr>";
break;
case 'csv':
$delimiter = ',';
$lb = "\n";
break;
default: // DO NOTHING
break;
}

if ($result && strcmp(strtolow er($type), 'csv') != 0) $content =
'<table><tr>';
if ($result) $content .= $this->generateHeader s($type) . $lb;

$isFirstRow = true; $isEndOfFirstRo w = true;

for ($i = 0; $i < @sizeof($result ); $i++) {
$header = @array_keys(get _object_vars($r esult[$i]));
if (strcmp(strtolo wer($type), 'csv') != 0 && $isFirstRow) {
$isFirstRow = false;
$field = '<td valign="top">';
}
for ($j = 0; $j < @sizeof($header ); $j++) {
if (strcmp(strtolo wer($type), 'csv') == 0) {
$field .= '"' . nl2br(preg_repl ace('/\r/', "\n",
preg_replace('/[\t]/', ' ', str_replace('"' , '"',
$result[$i]->$header[$j])))) . '"';
} else {
$field .= nl2br(preg_repl ace('/\r/', "\n", preg_replace('/[\t]/',
' ', $result[$i]->$header[$j])));
}
$content .= $field . $delimiter;
$field = '';
}
if (strcmp(strtolo wer($type), 'csv') != 0 && $isEndOfFirstRo w) {
$isEndOfFirstRo w = false;
$content = preg_replace('/<td valign="top">$/', '', $content);
}
$content .= $lb;
$field = '';
$isFirstRow = true; $isEndOfFirstRo w = true;
}

if ($result && strcmp(strtolow er($type), 'csv') != 0) $content =
preg_replace('/<tr>$/', '', $content) . '</table>';

return $content;

}

}
[/PHP]

This class contains a method that will generate the CSV-formatted
content, however, it again fails to produce legitimate content in
Excel while producing just-fine content in OpenOffice.

Help!

Thanx
Phil

[Sacs]

Re: PHP to create CSV that Excel can read

Phil Powell wrote:[color=blue]
> I am using PHP 4.3.2 to create a CSV file, however, Excel constantly
> views it as a single-column spreadsheet with everything in quotes,
> whereas OpenOffice Calc views it as a legitimate spreadsheet in
> separate columns/cells.
>[/color]
*snip*[color=blue]
> This class contains a method that will generate the CSV-formatted
> content, however, it again fails to produce legitimate content in
> Excel while producing just-fine content in OpenOffice.
>
> Help!
>
> Thanx
> Phil[/color]

Are you sending the appropriate http headers? I've used:

header ("Content-Type: application/msexcel");
header ("Content-Disposition: attachment; filename=\"file name.csv\"");

followed by a stream of comma separated text and it just works, in both
excel and oo.o

Be aware that you DONT want the first bytes in the csv to be "ID", Excel
treats the file as a .slk no matter what you tell it it really is. (THAT
took me a while to figure out! Doh!)

Sacs

[Michael Fesser]

Re: PHP to create CSV that Excel can read

.oO(Sacs)
[color=blue]
>Are you sending the appropriate http headers? I've used:
>
>header ("Content-Type: application/msexcel");[/color]

Shouldn't that be application/vnd.ms-excel instead?

According to <http://www.iana.org/assignments/media-types/application/>
application/msexcel is not a registered MIME type.

Micha

[Sacs]

Re: PHP to create CSV that Excel can read

Michael Fesser wrote:[color=blue]
> .oO(Sacs)
>
>[color=green]
>>Are you sending the appropriate http headers? I've used:
>>
>>header ("Content-Type: application/msexcel");[/color]
>
>
> Shouldn't that be application/vnd.ms-excel instead?
>
> According to <http://www.iana.org/assignments/media-types/application/>
> application/msexcel is not a registered MIME type.
>
> Micha[/color]

Err, yes, it probably should be vnd.ms-excel :-)

application/msexcel does work though, for oo.o too, and thats the only
requirement I had at the time...

Thanks for pointing that out!

Sacs

[Michael Vilain]

Re: PHP to create CSV that Excel can read

In article <dfhip0lepgikfd 8phfid9mb2l0tja o64me@4ax.com>,
Michael Fesser <netizen@gmx.ne t> wrote:
[color=blue][color=green]
> >Are you sending the appropriate http headers? I've used:
> >
> >header ("Content-Type: application/msexcel");[/color]
>
> Shouldn't that be application/vnd.ms-excel instead?
>
> According to <http://www.iana.org/assignments/media-types/application/>
> application/msexcel is not a registered MIME type.[/color]

THANK YOU for this URL! It's a great mystery to me if I'm using the
right mime type when I download things. This cleared up a lot. I do
note that Macintosh IE tends to use "octetstrea m" while all the other
browsers use "octet-stream". Anyone know why?

--
DeeDee, don't press that button! DeeDee! NO! Dee...

[R. Rajesh Jeba Anbiah]

Re: PHP to create CSV that Excel can read

phillip.s.powel l@gmail.com (Phil Powell) wrote in message news:<b5476f2f. 0411151426.4f94 7f0@posting.goo gle.com>...[color=blue]
> I am using PHP 4.3.2 to create a CSV file, however, Excel constantly
> views it as a single-column spreadsheet with everything in quotes,
> whereas OpenOffice Calc views it as a legitimate spreadsheet in
> separate columns/cells.
>
> [PHP][/color]
<snip>[color=blue]
> if (strcmp(strtolo wer($type), 'csv') == 0) {
> $field .= '"' . nl2br(preg_repl ace('/\r/', "\n",
> preg_replace('/[\t]/', ' ', str_replace('"' , '"',
> $result[$i]->$header[$j])))) . '"';[/color]


I have no clue, why you use nl2br() here. Also, what's the point in
str_replace('"' , '"',..) ??

Perhaps you may want to try <http://in2.php.net/fputcsv> (CVS?) or
<http://in2.php.net/fgetcsv#14788>. The later worked very well for me
in many cases.

--
| Just another PHP saint |
Email: rrjanbiah-at-Y!com

[John Dunlop]

Re: PHP to create CSV that Excel can read

Michael Vilain wrote:
[color=blue]
> I do note that Macintosh IE tends to use "octetstrea m" while
> all the other browsers use "octet-stream". Anyone know why?[/color]

'Octetstream' is an unregistered subtype of the application
top-level type, and should therefore be prefixed with 'x-';
'octet-stream' is the registered subtype. The former is
more likely to cause the browser to ask its user what to do.

Note that browsers use whatever MIME media type they're
given, unless they're trying to guess the media type of a
file to be uploaded.

--
Jock

[Phil Powell]

Re: PHP to create CSV that Excel can read

ng4rrjanbiah@re diffmail.com (R. Rajesh Jeba Anbiah) wrote in message news:<abc4d8b8. 0411160003.4feb d736@posting.go ogle.com>...[color=blue]
> phillip.s.powel l@gmail.com (Phil Powell) wrote in message news:<b5476f2f. 0411151426.4f94 7f0@posting.goo gle.com>...[color=green]
> > I am using PHP 4.3.2 to create a CSV file, however, Excel constantly
> > views it as a single-column spreadsheet with everything in quotes,
> > whereas OpenOffice Calc views it as a legitimate spreadsheet in
> > separate columns/cells.
> >
> > [PHP][/color]
> <snip>[color=green]
> > if (strcmp(strtolo wer($type), 'csv') == 0) {
> > $field .= '"' . nl2br(preg_repl ace('/\r/', "\n",
> > preg_replace('/[\t]/', ' ', str_replace('"' , '"',
> > $result[$i]->$header[$j])))) . '"';[/color]
>
>
> I have no clue, why you use nl2br() here. Also, what's the point in
> str_replace('"' , '"',..) ??
>
> Perhaps you may want to try <http://in2.php.net/fputcsv> (CVS?) or
> <http://in2.php.net/fgetcsv#14788>. The later worked very well for me
> in many cases.[/color]

Because. according to http://www.php.net, "fputcsv" is still in CVS,
and fgetcsv is useless to me since I'm doing a conversion to csv.

This works just fine again when opened in OpenOffice, but comes across
formatted incorrectly in Excel XP. I was told, however, you could
import the CSV instead.

Phil

[John Sjouken]

Re: PHP to create CSV that Excel can read

Just maybe a stupid remark, but if you go to:
start => settings => control panel => regional options => numbers =>
list eperator and add ; (semicolon)you get it right

"John Dunlop" <usenet+2004@jo hn.dunlop.name> wrote in message
news:MPG.1c03d3 f44c8691ae9897c 1@News.Individu al.NET...[color=blue]
> Michael Vilain wrote:
>[color=green]
> > I do note that Macintosh IE tends to use "octetstrea m" while
> > all the other browsers use "octet-stream". Anyone know why?[/color]
>
> 'Octetstream' is an unregistered subtype of the application
> top-level type, and should therefore be prefixed with 'x-';
> 'octet-stream' is the registered subtype. The former is
> more likely to cause the browser to ask its user what to do.
>
> Note that browsers use whatever MIME media type they're
> given, unless they're trying to guess the media type of a
> file to be uploaded.
>
> --
> Jock[/color]

[R. Rajesh Jeba Anbiah]

Re: PHP to create CSV that Excel can read

phillip.s.powel l@gmail.com (Phil Powell) wrote in message news:<b5476f2f. 0411160840.e182 400@posting.goo gle.com>...
<snip>[color=blue][color=green]
> > I have no clue, why you use nl2br() here. Also, what's the point in
> > str_replace('"' , '"',..) ??
> >
> > Perhaps you may want to try <http://in2.php.net/fputcsv> (CVS?) or
> > <http://in2.php.net/fgetcsv#14788>. The later worked very well for me
> > in many cases.[/color]
>
> Because. according to http://www.php.net, "fputcsv" is still in CVS,
> and fgetcsv is useless to me since I'm doing a conversion to csv.[/color]

Did you click both the links? *Read* the contents of the links?

--
<?php echo 'Just another PHP saint'; ?>
Email: rrjanbiah-at-Y!com