How can i import chinese data from excel to mysql?

I'm a excel file where i've Chinese content and i want to import them into my tables.

My table is set utf-8 collation and my excel sheet has three worksheets.

When i tried to convert the worksheet into csv file and use them for import but when i opened the csv file it doesn't have the chinese characters properly..

And i tried to convert them into txt file and then import and nothing seems to be working..

How can i import chinese content into my mysql table?

Update:

I tried your answers but didn't work on my way.. So i asked for a new file from the client from his native os. My client sent me the .csv file with chinese data, when i opened it in excel, it has some odd characters but when i opened it using dreamweaver it showed the chinese characters.

When i tried to import data using load data it has the same weird characters inside the db .. Then i uploaded the file to the server and tried into insert into db using php script , it also doesn't work..

The script i used

<?php
  header('Content-type: text/html; charset=utf-8');
  include("includes/config.php");
  db_connect();
  mysql_query("SET NAMES utf8");
  $file = fopen("../file.csv", "r");
  $i = 0;
  while (!feof($file)) {
      $arr = fgetcsv($file);
      $qry = "insert into tbl_wine_tasting_note开发者_JAVA百科s
    (`color` , `name` , `producer` , `vintage` , `size` , `country` , `region` ,
    `sub_region` , `comments` , `alcohol` , `points` , `varietals` ,
    `website`,`label`)
    values 
    ('" . implode("','", $arr) . "')";

      mysql_query("SET NAMES utf8");
      mysql_query($qry);
  }
  fclose($file);
?> 

The qry statement was correct when i echoed that but it didn't even inserted a single row..

What's wrong with this? I need them to upload them ..

---

Try using iconv to convert your .csv file from the major Chinese encodings below, transforming to UTF-8. The first '-f' from encoding that doesn't result in an error will be the correct encoding of your .csv file. From the command line on a system with iconv, try this:

iconv -f [Chinese encoding] -t UTF-8 [filename].csv > [filename]_utf8.csv

For the -f flag value, try:

1. CP936
2. GB18030
3. BIG-5

For example, I would guess that your .csv file was probably created on a Chinese Windows environment, so this could work:

iconv -f CP936 -t UTF-8 [filename].csv > [filename]_utf8.csv

You would then be able to open up [filename]_utf8.csv in a UTF-8 friendly text editor and see the Chinese displayed properly. It is this UTF-8 encoded [filename]_utf8.csv file that you would use to load the data into your database.

---

If you are inserting data into a database that has a UTF-8 encoding, you need to be sure that you're inserting a string that is encoded in UTF-8. Like buruzaemon said, your CSV file is probably encoded in CP936, BIG-5 or GB18030.

You can convert from one encoding to another in PHP by using mb_convert_encoding(str, to, [from]). Since we're not sure which encoding the file is in, we can attempt to auto-detect it this way:

<?php
  header('Content-type: text/html; charset=utf-8');
  include("includes/config.php");
  db_connect();
  mysql_query("SET NAMES utf8");
  $file = fopen("../file.csv", "r");
  $i = 0;
  while (!feof($file)) {

    $arr = fgetcsv($file);
    foreach ($arr as $key => $item) { 
       $arr[$key] = mb_convert_encoding($item, 'UTF-8', 'CP936, GB18030, BIG-5');
    }

      $qry = "insert into tbl_wine_tasting_notes
      (`color` , `name` , `producer` , `vintage` , `size` , `country` , `region` ,
       `sub_region` , `comments` , `alcohol` , `points` , `varietals` ,
       `website`,`label`)
        values 
      ('" . implode("','", $arr) . "')";

    mysql_query("SET NAMES utf8");
    mysql_query($qry);
 }
 fclose($file);
?> 

You can see the docs for mb_convert_encoding here:

• http://php.net/manual/en/function.mb-convert-encoding.php

---

1. export it to csv from excel,
2. use text editor like notepad++ to change encoding to UTF-8 without DOM,
3. import to db via phpmyadmin

---

Other than suggesting you may be reading unicode not utf8, I can't comment on the charset issue. However an alternate approach might work.

I've found PHPExcel to be useful for working directly with Excel files. A big plus is that it's a pure PHP implementation, so will run as happily on a *NIX or Mac server as a Windows machine with Excel.

---

Also, you could do direct xls data import with a Data Import tool in dbForge Studio for MySQL. Command-line is supported.