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

[egillsigurdur]

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.

[Mark Baker]

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.

[egillsigurdur]

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.

[Mark Baker]

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.

[egillsigurdur]

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

 

Thank you though!