Posts Tagged MySQL
Export UTF-8 data from MySQL to Excel xls
今天心血來潮因為工作會需要將 MySQL 裡的 UTF-8 資料 export 到 excel 的 xls 裡 … 好吧我之前就有碰過這個問題，但是當時懶惰懶得管，硬轉 big5 輸出然後忽略那些問號 (誤
因為 excel 該死的不太認得 utf-8 格式的資料，本來是想說輸出直接用 ‘\t’ 分隔就行了，後來因為他不認識我還加上 bom (“\xEF\xBB\xBF”)，這樣子輸出是很正常，但是狀況很不對勁，為什麼呢？因為所有的欄位和資料都擠到 A 那欄了，那這樣是搞屁 … 然後我就想到，phpMyAdmin 匯出的 excel 2000 格式相當精美，然後我就照著他的模式處理這樣的窘境，果然是，相當不錯精美阿 XD （自己可以加上一點簡單的 align 來修飾輸出，但我懶惰，所以 pass）既然都寫了就順便分享一下好了，應該會有人用的上，我是拿別人寫輸出的 csv code 來接著改的。
// Author: Linmic, email: linmicya@gmail.com
$host = ""; // your db host (ip/dn)
$user = ""; // your db’s privileged user account
$password = ""; // and it’s password
$db_name = ""; // db name
$tbl_name = ""; // table name of the selected db
$link = mysql_connect ($host, $user, $password) or die(‘Could not connect: ‘ . mysql_error());
mysql_select_db($db_name) or die(‘Could not select database’);
$select = "SELECT * FROM `".$tbl_name."`";
mysql_query(‘SET NAMES utf8;’);
$export = mysql_query($select);
for ($i = 0; $i < $fields; $i++) {
$col_title .= ‘<td>’.mysql_field_name($export, $i).‘</td>’;
}
$col_title = ‘<tr>’.$col_title.‘</tr>’;
while($row = mysql_fetch_row($export)) {
$line = ”;
foreach($row as $value) {
if ((!isset($value)) OR ($value == "")) {
$value = "\t";
} else {
$value = str_replace(‘"’, ”, $value);
$value = ‘<td>’ . $value . ‘</td>’ . "\t";
}
$line .= $value;
}
$data .= trim("<tr>".$line."</tr>")."\n";
}
$data = str_replace("\r","",$data);
header("Content-Type: application/vnd.ms-excel;");
header("Content-Disposition: attachment; filename=export.xls");
header("Pragma: no-cache");
header("Expires: 0");
$xls_header = ‘<html xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns="http://www.w3.org/TR/REC-html40">
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head>
<meta http-equiv="Content-type" content="text/html;charset=utf-8" />
</head>
<body>
<table border="1" align="center">’;
$xls_footer = ‘</table>
</body>
</html>’;
print $xls_header.$col_title.$data.$xls_footer;
exit;
?>
微軟真靠杯，收工，回家。
Convert a MySQL DB from latin1 to UTF8
For work, I have to convert my very old database from latin1 based( with big5 in it ) to UTF8.
My Enviornment:
- OS: Gentoo
- Python: Python 2.4.3
- MySQL: MySQL 5.0.70-log Gentoo Linux mysql-5.0.70-r1
- Old encoding: latin1(big5)
- Try to convert to: UTF8
And here’s my solution, and memo.
Assume your private parameters as following:
- Your old db name: your_old_db
- Your new db name: your_new_db
- Your mysql account: your_db_acc
- Your mysql server’s ip or dn: your_db_host
mysqldump -u wwwuser -p –default-character-set=latin1 your_old_db > output.sql
# — fix origin output.sql’s big5 problem here ( do some regex replacement, it depends ) —
# convert the "real storage encoding" from "big5" to utf8, latin1 is just a tag note in db
piconv -f big5 -t utf8 output.sql > utf8.sql
# replace all charset and collate in sql file, and delete the line which include "SET NAMES latin1"
# for matching, the longer the better ex: match from "type=Mxxx….", you could do this from the beginning of each single line
sed -e ‘s/DEFAULT CHARSET=latin1/DEFAULT CHARSET=utf8/g’ -e ‘s/CHARSET=gbk/CHARSET=utf8/g’ -e ‘s/ collate gbk_bin//g’ -e ‘s/ COLLATE=gbk_bin//g’ -e ‘/SET NAMES latin1/d’ utf8.sql > utf8_after_sed.sql
# echo SET NAMES utf8 to a new file named tmp.sql
echo "SET NAMES utf8;" > tmp.sql
# merge the two files
cat utf8_after_sed.sql >> tmp.sql
# rename the file to utf_final.sql, just a clarify
mv tmp.sql utf8_final.sql
# import to a new unicode based db, we’re done here
mysql -h your_db_host -u your_db_acc -p –default_character_set utf8 your_new_db < utf8_ultimate.sql
Thanks sooooooo a lot for Tib‘s kindly provide his consultation.
Recent Comments