How to Create a CSV File with Kanji Character for MySQL Import
I am working on some databases and the client sends me MS Excel files to populate that to the MySQL database server. I was having trouble at first since it has some Kanji characters with it. When I directly import the Excel file to the MySQL server, the Kanji is not populated as Kanji but showing weird characters. So, I searched and experimented some mechanism to achieve clean import of data.
First, all data in the Excel file was copy-pasted to a new table in MS Access. Then at MS Access, I exported this to Text File Document format. This will result to a CSV file. Then, this CSV file is now loaded to a PHP script, which in turn loads all data from the CSV to the MySQL table specified. Here’s an example script:
mysql_connect("host", "username", "password") or die(mysql_error());
mysql_select_db("databasename") or die(mysql_error());
$query = mysql_query("SET NAMES 'utf8'");
$handle = fopen ('d:\\\TableNameOfCSV.txt', 'r');
while (($data = fgetcsv($handle, 1000, ';', '"')) !== FALSE)
{
$query = "INSERT INTO Table VALUES ('". implode("','", $data)."')";
$query = @mysql_query($query);
}
?>
Copy the script above, modify all necessary parameters and save it as .PHP file. Run this script and see a clean data loaded in your MySQL server.
Tags: CSV FileRelated Stories
POSTED IN: Excel, Microsoft, Productivity
0 opinions for How to Create a CSV File with Kanji Character for MySQL Import
No one has left a comment yet. You know what this means, right? You could be first!
Have an opinion? Leave a comment: