Using PHP to create CSV that Excel can read

[ppowell]

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.

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 &generateContent($type) {							// STATIC STRING METHOD
	$result = $this->getResult();

	switch (strtolower($type)) {
	 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(strtolower($type), 'csv') != 0) $content = '<table><tr>';
	if ($result) $content .= $this->generateHeaders($type) . $lb;

	$isFirstRow = true; $isEndOfFirstRow = true;

	for ($i = 0; $i < @sizeof($result); $i++) {
	 $header = @array_keys(get_object_vars($result[$i]));
	 if (strcmp(strtolower($type), 'csv') != 0 && $isFirstRow) {
	  $isFirstRow = false;
	  $field = '<td valign="top">';
	 }
	 for ($j = 0; $j < @sizeof($header); $j++) {
	  if (strcmp(strtolower($type), 'csv') == 0) {
	   $field .= '"' . nl2br(preg_replace('/\r/', "\n", preg_replace('/[\t]/', '&nbsp;', str_replace('"', '&quot;', $result[$i]->$header[$j])))) . '"';
	  } else {
	   $field .= nl2br(preg_replace('/\r/', "\n", preg_replace('/[\t]/', '&nbsp;', $result[$i]->$header[$j])));
	  }
	  $content .= $field . $delimiter;
	  $field = '';
	 }
	 if (strcmp(strtolower($type), 'csv') != 0 && $isEndOfFirstRow) {
	  $isEndOfFirstRow = false;
	  $content = preg_replace('/<td valign="top">$/', '', $content);
	 }
	 $content .= $lb;
	 $field = '';
	 $isFirstRow = true; $isEndOfFirstRow = true;
	}

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

	return $content;

}

}

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

[sneakyimp]

when you say 'legitimate content' does that mean you've tried to open it with excel and it doesn't work?

my advice to you is to make a simple spreadsheet in excel and save as CSV. I'm not certain, but I believe excel only applies quotes optionally and when it does, they are sometimes doubled.

once you've saved the CSV, open it up and look at it. this should help.

[ppowell]

In OpenOffice you can open up a CSV file straight away, however, in Excel you have to import it, is this correct?

Phil

[sneakyimp]

on my machine (windows 98 / office 2000) all you have to do is give a file a .csv filename and excel will open it.

OR you can import it. importing gives you more control over the formatting that gets automatically applied.

[ppowell]

Different platform; they're using Windows XP and Office XP.

Phil

[amcgrath]

For what it's worth, I created an html table with the data and saved the file with a ".xls" extension, and Excel read it in with no problems at all. amc

[sneakyimp]

XP should behave the same as 98 as far as windows opening a file named my_file.csv

shouldn't make a difference.

[Weedpacket]

Originally posted by sneakyimp
XP should behave the same as 98 as far as windows opening a file named my_file.csv

shouldn't make a difference.

Not necessarily; Microsoft has tended to tinker with the CSV format from version to version of Office - generally making a hash of the whole thing (like, no-one has ever designed a text-based file format before). Excel has suffered particularly from this.