MySQL database unrecognize chinese characters extracted from Excel file (xls)

Question

I try to extract data (Chinese Charaters) from excel(.xls), but when inserted, the database does not recognize the characters (appear blank and symbols). Please help me to solve the problems.

<html>
<body>
<form action="upload_file_xls.php" method="post" enctype="multipart/form-data">
    <input type="file" name="file" id="file" /><br/>
    <input type="submit" name="insert" value="Upload"/>
</form>
</body>
<?php
if(isset($_POST['insert'])){

    if ($_FILES["file"]["error"] > 0)
    {
        echo "Error: " . $_FILES["file"]["error"] ;
    }
    else{
        include 'excel/reader.php';
                $excel = new Spreadsheet_Excel_Reader();
                $file = $_FILES['file']['tmp_name'];
                $excel->read($file);
                $x=1;
                while($x<=$excel->sheets[0]['numRows']) {
                    $number = isset($excel->sheets[0]['cells'][$x][1]) ? $excel->sheets[0]['cells'][$x][1] : '';
                    $province = isset($excel->sheets[0]['cells'][$x][2]) ? $excel->sheets[0]['cells'][$x][2] : '';
                    $city = isset($excel->sheets[0]['cells'][$x][3]) ? $excel->sheets[0]['cells'][$x][3] : '';
                    $alphabet = isset($excel->sheets[0]['cells'][$x][4]) ? $excel->sheets[0]['cells'][$x][4] : '';


                    $link = mysql_connect('localhost', 'root', '');
                    mysql_set_charset('utf8',$link);
                    $db_selected = mysql_select_db('sugoroku01', $link);
                    if (!$db_selected) { die ('Database access error : ' . mysql_error());}

                    $query = "INSERT INTO `province_city_tbl`(`province`, `city`, `alphabet`) 
                                 VALUES ('".$province."', '".$city."', '".$alphabet."') ";

                    mysql_query($query) or die('Error, Feedback insert into database failed');

                    $x++;

                }

        }
}

?>

</html>

http://stackoverflow.com/questions/6819679/how-do-i-insert-unicode-characters-into-mysql-with-an-insert-statement — internals-in, Sep 30 '13 at 03:39
It works when I insert the data directly, but it doesn't work when I extract from excel.xls and insert the data into the database. Thank for help though. — TapiYuki, Sep 30 '13 at 03:54

score 0 · Answer 1 · answered Sep 30 '13 at 03:43

Try to run a query "SET NAMES utf8" before inserting something.

mysql_query("SET NAMES utf8");

Also, you do not need to put your mysql_connect() in a loop.

Try this code:

<html>
<body>
<form action="upload_file_xls.php" method="post" enctype="multipart/form-data">
    <input type="file" name="file" id="file" /><br/>
    <input type="submit" name="insert" value="Upload"/>
</form>
</body>
<?php
$link = mysql_connect('localhost', 'root', '');
$db_selected = mysql_select_db('sugoroku01', $link);
if (!$db_selected) { die ('Database access error : ' . mysql_error()); }
mysql_query("SET NAMES utf8");
mysql_query("SET CHARACTER SET utf8");

if(isset($_POST['insert'])){

    if ($_FILES["file"]["error"] > 0)
    {
        echo "Error: " . $_FILES["file"]["error"] ;
    }
    else{
        include 'excel/reader.php';
                $excel = new Spreadsheet_Excel_Reader();
                $file = $_FILES['file']['tmp_name'];
                $excel->read($file);
                $x=1;
                while($x<=$excel->sheets[0]['numRows']) {
                    $number = isset($excel->sheets[0]['cells'][$x][1]) ? $excel->sheets[0]['cells'][$x][1] : '';
                    $province = isset($excel->sheets[0]['cells'][$x][2]) ? $excel->sheets[0]['cells'][$x][2] : '';
                    $city = isset($excel->sheets[0]['cells'][$x][3]) ? $excel->sheets[0]['cells'][$x][3] : '';
                    $alphabet = isset($excel->sheets[0]['cells'][$x][4]) ? $excel->sheets[0]['cells'][$x][4] : '';


                    $query = "INSERT INTO `province_city_tbl`(`province`, `city`, `alphabet`) 
                                 VALUES ('".$province."', '".$city."', '".$alphabet."') ";

                    mysql_query($query) or die('Error, Feedback insert into database failed');

                    $x++;

                }

        }
}

?>

</html>

@TapiYuki Also try to see what is in your variables before put it in the database. May be there is no support from Spreadsheet_Excel_Reader? — Paul Denisevich, Sep 30 '13 at 04:02
@ Paul Denisevich : database and table collation is utf8_general_ci. So how do I make it to support with Spreadsheet_Excel_Reader? — TapiYuki, Sep 30 '13 at 04:39

MySQL database unrecognize chinese characters extracted from Excel file (xls)

1 Answers1