如何使用utf8 unicode将带有php的csv文件导入数据库

Question

I want to import a csv file to a database using php and mysql and my csv file has utf8 characters but when I import it, it doesn't support utf8 and I can't import my data correctly. Please help me how can do it?

/*CSV FILE Validation Start*/

if (!empty($_FILES['excelFile']['name'])) {
    $errFlg=0;
    $errMsg='';
    $valid_document_formats = array("csv"); 
    $valid_document_size=524288; // 512 KB

    $documentName = $_FILES['excelFile']['name'];    // Actual Document Name
    $documentSize = $_FILES['excelFile']['size'];    // Actual Document Size

    list($txt, $ext) = explode(".", $documentName);       // Get Actual Document Formate
    if (in_array($ext, $valid_document_formats)) {    // Check Document Formate
        if ($documentSize < $valid_document_size) {      // Check Document Size
            $new_doc_name = time() . "." . $ext;    // Document New Name
            $tmpDoc = $_FILES['excelFile']['tmp_name'];
        }else{
            $errMsg= "Document size max 512 KB";       // Error Message for max size
            $errFlg=1;
        }
    }else{
        $errMsg= "Invalid Document format";           // Error Message for Invalid format size4
        $errFlg=1;
    }
} else{
    $errMsg= "Please select an Document";
    $errFlg=1;
}
/*CSV FILE Validation End*/



/*CSV FILE Import Start*/
if(!$errFlg){
    $uploadedDocPath='excel_file/';        // folder name  for document upload
    if(move_uploaded_file($tmpDoc, $uploadedDocPath.$new_doc_name)){
        if(($handle = fopen("excel_file/".$new_doc_name , "r")) !== FALSE) 
        {
            $fileAdded=true;
            while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) 
            {
                $num = count($data);
                //echo var_dump($data);
                mysql_query("SET NAMES UTF8");
                echo $query="INSERT INTO csv_data(name,phone,city) values('".$data[0]."','".$data[1]."','".$data[2]."')";
                mysql_query($query);
            }
            fclose($handle);
               $csvname = "excel_file/".$new_doc_name;
               unlink($csvname);
        }
    }else{
        $errMsg= "Please Try after some time";
        $errFlg=1;
    }
}
/*CSV FILE Import End*/

How can I import with utf8?

Answer 1

Csv could be in UTF-8 but your file will be in some windows-* encoding (or ISO*). The simple way is transform data to UTF-8. Use iconv function for this task.

echo $query="INSERT INTO csv_data(name,phone,city)  values('". iconv('cp1250', 'utf-8', $data[0]) ."','". iconv('cp1250', 'utf-8', $data[1]) ."','". iconv('cp1250', 'utf-8', $data[2])."')";

You have to find encoding of your csv. For me it is cp1250 (windows-1250).

EDIT: You can also try to change (if iconv does not help)

mysql_query("SET NAMES UTF8");

to

mysql_query("SET NAMES 'utf8'");

And check your PHP script is also in UTF-8.