Jump to content

Archived

This topic is now archived and is closed to further replies.

netfrugal

MySQL data to Excel help

Recommended Posts

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?


Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.