'Exporting Chinese characters from MySQL

I have a problem with Chinese characters while exporting query results to an XLS file. In my database Chinese character are stored like as encoded format like this

新里程集团

When I show these character on a webpage they are automatically converted in standard Chinese characters with the help of a header function provided by PHP. But we want to extract Chinese characters for an XLS file from MySQL with standard Chinese character.

phpmysqlencodingcjk


Solution 1:[1]

Try this

mysqli_set_charset($connect,"utf8");

Solution 2:[2]

Sorry for bringing it back from the dead but since this is the first result in Google to this problem I thought I'd share my solution after banging my head against the wall for an hour.

As mentioned your data in mySQL should be stored as UTF8 and the columns should also be UTF8 (utf8mb4_general_ci).

The problem is not the export itself but the Excel - this happens when the exported file is encoded in UTF8 and Excel data is handled in ANSI (which happens by default if you just open the file by clicking on it).

You need to import the file into Excel like this:

  1. Create a new Excel file (just open Excel).
  2. Choose Data > From Text.
  3. Select the exported CSV file. The Text Import Wizard dialog box is displayed. Look for "Data source" dropdown with "Windows - ANSI" preselected. Change it to "65001 - Unicode (UTF8)".
  4. Select Delimited and click Next.
  5. Deselect Tab, select Comma, and click Next.
  6. Click Finish.
  7. In the Import Data dialog box, click OK.

Voila! If anyone have an easier solution for this - please share. Gosh it's the 21st century and we still have to deal with crap like this

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 chi fai lai
Solution 2 Philarmon

Related Questions