Exporting MySQL to Excel in PHP

netfrugal

Okay, I know how to export information from MySQL to an excel sheet using php. However, Some data is going to have html formatting. I'm having a problem with excel not translating HTML in its own cells. And whenever there is a comma in my database, then excel will separate info to the next cell.

For example: (Dr. Johnson M.D., B.S.E) The comma will send B.S.E. to the next cell.

Here's the code I am using to export:

<?php if (!$HTTP_GET_VARS['submit']) { ?>

<?php
echo "Export and Save Customer Data onto your Local Machine";
echo '<form action="'. $phpself.'">';
echo '<input type="submit" value="Export" name="submit"></form>';
?>

<?php
}
else
{
$contents="HR Category,Posting,Statement\n";
$user_query = mysql_query('select
f.faqdesk_id
, c.categories_id
, f.faqdesk_question
, c.categories_name
, f.faqdesk_answer_short
from faqdesk_description as f
join faqdesk_to_categories as f2c
on f.faqdesk_id = f2c.faqdesk_id
join faqdesk_categories_description as c
on f2c.categories_id = c.categories_id');
while($row = mysql_fetch_array($user_query))
{

$contents.=$row[categories_name].",";
$contents.=$row[faqdesk_question].",";
$contents.=$row[faqdesk_answer_short]."\n";
}
Header("Content-Disposition: attachment; filename=export.csv");
print $contents;
}

And this works just fine - only if there is simple data. But if there is formatting or html involved (like: <b>customer name</b>) then I get the whole thing exported to the cell, instead of just "customer name".

Is there a way to make excel read html correctly. Or maybe a different way to code it so the html doesn't get exported?

Any ideas?
thanks,
Marc

drew010

as far as i know, excel doesnt understand html, i think they use rich text for font sizes and styling etc.
you can use

$nohtml = preg_replace('/<[^>]+>/', '', $html);

to strip out the html tags.

and the , in a name will need to be escaped, so something like

$escaped = str_replace(',', '\,', $unescaped);

will be necessary for all possible values that excel may parse as part of the formatting.

netfrugal

How would you properly code that correctly into what I already did?
I've been testing it in several ways and so far I've been only getting half of it right.

thanks!

drew010

Header("Content-Disposition: attachment; filename=export.csv");

while($row = mysql_fetch_array($user_query))
{
  $catname = escapeCommaStripHtml($row['categories_name']);
  $faqquest = escapeCommaStripHtml($row['faqdesk_question']);
  $faqanswer = escapeCommaStripHtml($row['faqdesk_answer_short']);

  echo "$catname,$faqquest,$faqanswer\n";
}




function escapeCommaStripHtml($string)
{
  return preg_replace('/<[^>]+>/', '', str_replace(',', '\,', $string));
}

netfrugal

I hate to sound like an idiot, but do i add that to the code i already have?

Like this?

<?php
require ('includes/application_top.php');
if (!$HTTP_GET_VARS['submit']) { ?>

<?php
echo "Export and Save Board Questions onto your Local Machine";
echo '<form action="'. $phpself.'">';
echo '<input type="submit" value="Export" name="submit"></form>';
?>

<?php
}
else
{
$contents="HR Category,Posting,Statement\n";
$user_query = mysql_query('select
f.faqdesk_id
, c.categories_id
, f.faqdesk_question
, c.categories_name
, f.faqdesk_answer_short
from faqdesk_description as f
join faqdesk_to_categories as f2c
on f.faqdesk_id = f2c.faqdesk_id
join faqdesk_categories_description as c
on f2c.categories_id = c.categories_id');

Header("Content-Disposition: attachment; filename=export.csv");

while($row = mysql_fetch_array($user_query))
{
$catname = escapeCommaStripHtml($row['categories_name']);
$faqquest = escapeCommaStripHtml($row['faqdesk_question']);
$faqanswer = escapeCommaStripHtml($row['faqdesk_answer_short']);

echo "$catname,$faqquest,$faqanswer\n";
}

function escapeCommaStripHtml($string)
{
return preg_replace('/<[^>]+>/', '', str_replace(',', '\,', $string));
}
}
?>

I've been playing with it and testing it in so many ways that I'm getting cross-eyed.

when I apply it I get an error inside the excel sheet. Here is the link: www.mastertheboard.com/board_topic_export2.php where I am trying to make it work. It's part of an Oscommerce cart.

Before I had it working at this domain: www.mastertheboard.com/board_topic_export.php
Except that there is still the html and comma problems.

I have no idea what I'm doing wrong.

drew010

<?php
require ('includes/application_top.php');

if (!$HTTP_GET_VARS['submit']) {
  echo "Export and Save Board Questions onto your Local Machine";
  echo '<form action="'. $phpself.'">';
  echo '<input type="submit" value="Export" name="submit"></form>';
}
else
{
  $contents="HR Category,Posting,Statement\n";
  $user_query = mysql_query('select
  f.faqdesk_id
  , c.categories_id
  , f.faqdesk_question
, c.categories_name
, f.faqdesk_answer_short
  from faqdesk_description as f
  join faqdesk_to_categories as f2c
  on f.faqdesk_id = f2c.faqdesk_id
  join faqdesk_categories_description as c
  on f2c.categories_id = c.categories_id');


  Header("Content-Disposition: attachment; filename=export.csv");

  while($row = mysql_fetch_array($user_query))
  {
    $catname = escapeCommaStripHtml($row['categories_name']);
    $faqquest = escapeCommaStripHtml($row['faqdesk_question']);
    $faqanswer = escapeCommaStripHtml($row['faqdesk_answer_short']);

echo "$catname,$faqquest,$faqanswer\n";
  }
}


function escapeCommaStripHtml($string)
{
return preg_replace('/<[^>]+>/', '', str_replace(',', '\,', $string));
}

?>

try that and see how it works

netfrugal

thanks!

It stripped the code between the brackets. so that worked. It still leaves  
I'm sure that can be added to the stripping function. However, the commas still have a problem. I may need to replace the comma for a dash '-' .

I've left it up so you can see how it resolves, in case you're interested: www.mastertheboard.com/board_topic_export.php

Any ideas on the comma issue? I'm wondering if the comma cannot be fixed. It seems that any comma, even if it is replaced with \, will still separate the value to the next cell.

thanks!

drew010

ah yea, my mistake i had it wrong with the . its any field w/ commas or newlines needs to be enclosed in "s

the function can be changed to

function escapeCommaStripHtml($string)
{
  return '"' . preg_replace('/<[^>]+>/', '', str_replace('"', '""', $string)) . '"';
}

this page can be of good help: http://www.creativyst.com/Doc/Articles/CSV/CSV01.htm