[SOLVED] Special character problem when exporting a MySQL database to Excel

I've got this MySQL table that I'm trying to make people able to export to excel through PHP, but the characters are all messed up.  When I print the table out in HTML with encoding UTF-8 everything is okay, but when I put it into Excel ð turns to ð, ú becomes ú, ó becomes ó and so on.  They do look like this in phpMyAdmin, also when I export them there.

 

 

How can I fix characters in Excel export?  I have no knowledge at all of how .xls files are structured.

You need to store the UTF-8 encoded values in Excel as well.

 

You don't say how you're exporting to Excel though, and there's no simple solution to to writing an Excel file - giving you details of the structure of an xls file wouldn't particularly help because it is a fairly complex binary format - unless you're using one of the libraries that have been developed for that specific purpose.

I wasn't using a library, so I tried that now.  I'm now using PEAR’s ‘Excel Writer’, which is by the way sort of neat.

 

But still, when I do a loop that prints out info from the table, all special characters are wacky, exactly the way they look in phpMyAdmin.

 

Do you think I should do some loop that would alter all "ð"s and turn them into "ð"s?  Not completely how I'd do it though.

I wasn't using a library, so I tried that now. I'm now using PEAR’s ‘Excel Writer’, which is by the way sort of neat.

It's OK, though it wouldn't be my personal recommendation; but any writer should simplify the task and handle all the complexities of Excel's binary format.

 

 

But still, when I do a loop that prints out info from the table, all special characters are wacky, exactly the way they look in phpMyAdmin.

If the database data isn't UTF-8, then, you need to ensure that the characters are converted from whatever charset you might be using in your database to UTF-8 before you populate the Excel cell.

$cellValue = iconv('ISO-8859-1,'UTF-8',$cellValue);

using ISO-8859-1 or whatever character set you're using for your table data.

I'm not sure how good the PEAR Excel Writer is for handling charsets.

All fixed, had to change my character encoding to Western (Windows Latin 1) instead of Western (Mac OS Roman).

 

Thank you though!