Jump to content


MySQL data to Excel help

  • Please log in to reply
1 reply to this topic

#1 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']) { ?>

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

$contents="HR Category,Posting,Statement\n";
$user_query = mysql_query('select
, 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))

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.

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

  • 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