is there a way to display correctly characters encoded in utf-8 in Excel? I exported some data from mysql database, but they are not displayed correctly in Excel.

Thanks!

is there a way to display correctly characters encoded in utf-8 in Excel?



Yes, I have personally coded an export module, from PHP, that would actually allow users to export their readings from their mySQL Database into XLS / CSV format. ;)

May you share some idea? that's the whole point of me asking. :)

Thinking of it again, if it's just about exporting data into CSV files, why not doing it from your phpmyadmin, since your request is not about integrations ? ;)

CSV is simply comma seperated variables or whatever.

You simply pull the data from the database and stick it in a file with commas seperating the data, you then repeat for each row using a newline.

In my remember there are some free editors can convert utf-8 to other charset encoded format. try google

Thinking of it again, if it's just about exporting data into CSV files, why not doing it from your phpmyadmin, since your request is not about integrations ? ;)


CSV is simply comma seperated variables or whatever.

You simply pull the data from the database and stick it in a file with commas seperating the data, you then repeat for each row using a newline.


this is for an application that I wrote for someone who does not know anything about programming and no access to phpmyadmin.

arkin, I also have no problem of how about writing codes you mentioned above.

The problem is that non-English characters being utf-8 encoded do not display correctly when exported to Excel cells, unless I merge it to Word document it shows correctly.

Thanks.

In my remember there are some free editors can convert utf-8 to other charset encoded format. try google

I believe I found one application doing that but it has limit number of lines of text. I am still googling though.

Thanks!

Are you sure the text are encoded with utf-8? If so excel should do the trick by itself. The problem might be the text are exported in other charset, not utf-8.

lutan, I believe I did; it is weird that the text did not show up correctly on Excel but Word when the text has been merged to.

mostly looks like the text is not in utf-8. if you dump database by phpmyadmin, try to set your browser charset to utf-8 before dumping. Text showing correctly in word does not mean it is encoded in utf-8. It may mean the text is correctly encoded in your localization, not utf-8. what version of your mysql server?

just recalled utf-8 does not support all character sets, for example chinese characters are not supported by utf-8, but utf-7 supports it. try to dump database in utf-7 see what happens.

With phpmyadmin, (since it's free), you can easily export these readings into CSV format as you can as easily import them into Microsoft Excel. From that point, it should automatically set all the readings seperately for each boxes. Then, you could instantly save it into XLS format. ;)

just recalled utf-8 does not support all character sets, for example chinese characters are not supported by utf-8, but utf-7 supports it. try to dump database in utf-7 see what happens.

Thanks, I will try that and let you know the results.

With phpmyadmin, (since it's free), you can easily export these readings into CSV format as you can as easily import them into Microsoft Excel. From that point, it should automatically set all the readings seperately for each boxes. Then, you could instantly save it into XLS format. ;)

I don't know why but I believe there is no bridge to connect my communication with yours. Thanks anyway. You seem to never get my point. lolz. Please read all my posts again - I thought I could not explain any better.

You seem to never get my point.



Rather than mentionning something you think I don't understand, you should rather install the most easiest free tool on earth (phpmyadmin) to export these readings instantly. When you'll be about to import these readings through Microsoft Excel, there will be no difference than coding your own PHP tool.

It's as easy to understand as it was easy to understand the point of your topic.

Rather than mentionning something you think I don't understand, you should rather install the most easiest free tool on earth (phpmyadmin) to export these readings instantly. When you'll be about to import these readings through Microsoft Excel, there will be no difference than coding your own PHP tool.

It's as easy to understand as it was easy to understand the point of your topic.

How come "lutan" did not have any problem understanding my problem? Now, you've made me mad - just kidding. You must've missed reading my reply to one of the posters that this is an application I am working for someone who does not know anything about programming, no access to phpmyadmin. Exporting stuff out of mysql is just one of 30 features in the applications. Besides, the information pulled does not come from one single table but multiple table in a query. My customer is not a tech person who knows how to do all this. Furthermore, not even phpmyadmin can do what I was trying to do.

Since, my problem was just the encoded stuff, I did not have to go into details, as mentioned above, about my application.

I hope you should get my point by now.
Thanks for your effort of trying to help me though. I appreciate that :)

PS. if you still get confused. We'll go out for a beer or so. lolz

You may set four types of language charsets in mysql after connection is made. For example after connection is established and database selected,
$linkID = @mysql_connect($hostname, $username, $password);
@mysql_select_db($database, $linkID) ) {
you can set your client side charset, the connection charset, collation charset and charset for returned result, as follows,
@mysql_query("SET character_set_client=$user_charset", $linkID);
@mysql_query("SET character_set_connection=$user_charset", $linkID);
@mysql_query("SET collation_connection=$user_charset", $linkID);
@mysql_query("SET character_set_results=$user_charset", $linkID);
From MySQL online document you can find all what these mean. If $user_charset = utf-8 does not work, try set it to utf-7 or latin1.