Export Chinese words to CSV from MySQL DB

Question

Here's my code:

mysql_set_charset("utf8");
mysql_query("SET NAMES utf8");
header('Content-Type: text/html; charset=utf-8');
mysql_connect("localhost","root","");
mysql_select_db("ladli");
mysql_query("SET NAMES 'utf8'");
mysql_query("SET CHARACTER SET utf8");
mysql_query("SET COLLATION_CONNECTION = 'utf8_unicode_ci'");

$output         = "";
$table          = ""; 
$sql            = mysql_query("select * from table'");
mysql_query("SET NAMES utf8");
$columns_total  = mysql_num_fields($sql);

for ($i = 0; $i < $columns_total; $i++) {
$heading    =   mysql_field_name($sql, $i);
$output     .= '"'.$heading.'",';
}
$output .="\n";

// Get Records from the table

while ($row = mysql_fetch_array($sql)) {
for ($i = 0; $i < $columns_total; $i++) {

$output .='"'.$row["$i"].'",';

}
$output .="\n";
}

$filename =  "chinese_test.csv";

header('Content-Description: File Transfer');
header("Content-type: application/vnd.ms-excel");
header("Content-disposition: csv" . date("Y-m-d") . ".csv");
header("Content-disposition: filename=".$filename.".csv");
header('Content-Transfer-Encoding: binary');
header('Pragma: public');
print "\xEF\xBB\xBF"; // UTF-8 BOM
print $output;
exit;

In my DB: 測試一下

I the csv: æˆ‘æ˜¯

When I am trying to download a csv using PHP I get messed up csv data with non readable format "æˆ‘æ˜¯" etc..

I need to export Chinese words in csv/xsl format from a MySQL database.

Thanks in advance.

If you're writing new code, **_please_ don't use the `mysql_*` functions**. They are old and broken, were deprecated in PHP 5.5 (which is so old it no longer even receives security updates), and completely removed in PHP 7. Use [`PDO`](https://secure.php.net/manual/en/book.pdo.php) or [`mysqli_*`](https://secure.php.net/manual/en/book.mysqli.php) with _prepared statements_ and _parameter binding_ instead. See http://stackoverflow.com/q/12859942/354577 for details. — ChrisGPT was on strike, Dec 19 '17 at 13:27
Is this working(chinese) fine ....when i am changed code to mysql_** ? — Giri jeeva, Dec 19 '17 at 14:13
The recommendation to avoid `mysql_` functions has nothing to do with your encoding question. It's a very important thing to do in general. — ChrisGPT was on strike, Dec 19 '17 at 15:02

score 0 · Answer 1 · answered Dec 19 '17 at 12:50

0

A simple workaround is to use Google Spreadsheet. Paste (values only if you have complex formulas) or import the sheet then download CSV. I just tried a few characters and it works rather well.

NOTE: Google Sheets does have limitations when importing. See here.

NOTE: Be careful of sensitive data with Google Sheets.

EDIT: Another alternative - basically they use VB macro or addins to force the save as UTF8. I have not tried any of these solutions but they sound reasonable.

answered Dec 19 '17 at 12:50

Er Nilay Parekh

569
5
17

can you mention any addins name here ? – Giri jeeva Dec 19 '17 at 12:53
I tried Notepad++ first and that worked only in some cases. This worked brilliantly. – Er Nilay Parekh Dec 19 '17 at 12:55

score 0 · Answer 2 · answered Dec 22 '17 at 17:39

æˆ‘æ˜¯ is "Mojibake" for 我是. See Trouble with UTF-8 characters; what I see is not what I stored for a discussion of that common problem and what causes it.

In Chinese, some characters need 4-byte UTF-8 characters. For that, you need utf8mb4 in MySQL.

Export Chinese words to CSV from MySQL DB

2 Answers2