Jump to content


Photo

MySQL data to Excel help


  • Please log in to reply
1 reply to this topic

#1 netfrugal

netfrugal
  • Members
  • PipPip
  • Member
  • 26 posts

Posted 27 January 2006 - 09:11 PM

Okay, I know how to export information from MySQL to an excel sheet. 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, then excel will move to the next cell. For example: (Dr. Johnson M.D., B.S.E) The comma will send B.S.E. to the next cell.

I'm using a contribution from www.oscommerce.com . Here's the code I am using to export:


[!--coloro:#009900--][span style=\"color:#009900\"][!--/coloro--]<?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;
}[!--colorc--][/span][!--/colorc--]


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.

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?




#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 28 January 2006 - 11:49 PM

Well, first, I believe that if you enclosed the data in double-quotes, and then comma-separated it, Excel won't complain, but I'm not certain. Second, you can use a regex to remove the html formatting, but there's no guarantee it'll be properly cleaned in the end (e.g. if you have a table or a list tag inside). For just simple formatting (B/I/U), it should be fine.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users