Import from CSV in PHP (mysql) shows characterses like '???'

Question

When I am importing from CSV, it is showing me characters like ???

My DB and tables are aleady set to utf8 unicode ci and arabic and other data shows properly when i do select *

But when I import from CSV and use below code. It returns me uploaded successfully but data is shown as ??? in employee name

Please find code below

Import From CSV...

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<?php
    header('Content-Type: text/html; charset=utf-8');
    mb_internal_encoding('UTF-8');
    mb_http_output('UTF-8');
?>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8"/>
<html>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8"/>
<head>
    <style type="text/css">
    body
    {
        margin: 0;
        padding: 0;
        background-color:#D6F5F5;
        text-align:center;
    }
    .top-bar
        {
            width: 100%;
            height: auto;
            text-align: center;
            background-color:#FFF;
            border-bottom: 1px solid #000;
            margin-bottom: 20px;
        }
    .inside-top-bar
        {
            margin-top: 5px;
            margin-bottom: 5px;
        }
    .link
        {
            font-size: 18px;
            text-decoration: none;
            background-color: #000;
            color: #FFF;
            padding: 5px;
        }
    .link:hover
        {
            background-color: #9688B2;
        }
    </style>

    <script>
      (function(i,s,o,g,r,a,m){i['GoogleAnalyticsObject']=r;i[r]=i[r]||function(){
      (i[r].q=i[r].q||[]).push(arguments)},i[r].l=1*new Date();a=s.createElement(o),
      m=s.getElementsByTagName(o)[0];a.async=1;a.src=g;m.parentNode.insertBefore(a,m)
      })(window,document,'script','//www.google-analytics.com/analytics.js','ga');

      ga('create', 'UA-60962033-1', 'auto');
      ga('send', 'pageview');

    </script>
</head>

<body>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8"/>
    <div class="top-bar">
        <div class="inside-top-bar">
            <a href="" width="500px"></a>
            <br><br>
            <a href="../insert.php" class="link">&larr; Back to main Website</a>
        </div>

    </div>
    <h1> CSV Should be in Below Format:</h1><b>Full Name:&nbsp;Employee Number:&nbsp;Department:&nbsp;Email:</b>
    <div style="border:1px dashed #333333; width:300px; margin:0 auto; padding:10px;">

    <form name="import" method="post" enctype="multipart/form-data">
        <input type="file" name="file" /><br />
        <input type="submit" name="submit" value="Submit" />
    </form>
<?php
    include ("connect.php");

    if(isset($_POST["submit"]))
    {   
        $file = $_FILES['file']['tmp_name'];
        $handle = fopen($file, "r");
        $c = 0;
        setlocale(LC_ALL, 'ar_AE.utf8');
        while(($filesop = fgetcsv($handle, 1000, ",")) !== false)
        {
            $name = $filesop[0];
            $emp_number = $filesop[1];
            $department = $filesop[2];
            $email = $filesop[3];
            //$name = $filesop[0];
            //$email = $filesop[1];
            mysqli_query($link,"SET character_set_client=utf8");
            $sql = $link->query("INSERT INTO employees (first_name, emp_number,department,email) VALUES ('$name','$emp_number','$department','$email')");
            $c = $c + 1;
        }

            if($sql){
                echo "You database has imported successfully. You have inserted ". $c ." Records";
                echo '<td><a href="../view.php">&larr; View Inserted Records</a></td>';
            }else{
                echo "Sorry! There is some problem.";
            }

    }
?>

    </div>
    <hr style="margin-top:300px;" />    

    <div align="center" style="font-size:18px;"><b><a href="">&copy; ALL RIGHTS RESERVED BY DIBBA MUNICIPALITY FUJAIRAH</a></b></div>

</body>
</html>

The data I am testing is as follows محمود عبدالعزيز

1 abc def Where arabic one is the full name/first_name, 1 is employee number, abc is department and def is email

http://stackoverflow.com/questions/279170/utf-8-all-the-way-through — Matt, Apr 05 '17 at 05:31
@mkaatman I have already read it dear... they are all set to utf8... — UAE ADVERTISER, Apr 05 '17 at 05:32
Post a small sample of your CSV that contains the problem data. — Matt, Apr 05 '17 at 05:36
@mkaatman Have uploaded the CSV image.. cant upload CSV file dont know how to do it...see above please the edit — UAE ADVERTISER, Apr 05 '17 at 05:41
@mkaatman It is uploading correct instead only the first row in arabic shows ??? but others how well — UAE ADVERTISER, Apr 05 '17 at 05:43
Can you copy and paste that cell into your question so that readers can copy and paste it to test? — Matt, Apr 05 '17 at 05:50
@mkaatman I have added.. arabic name is not copying properly but its the first one then 1 is employee number and other two are dept and email... they are in one line .... — UAE ADVERTISER, Apr 05 '17 at 05:57

Kanad Godse · Answer 1 · 2017-04-06T05:32:14.970

Could you please check if your column's collation is also UTF8 unicode?

You can run mysql> show full columns from employees;

and check the output to make sure even the column's collation is able to accept UTF8.

EDIT CSV can't store UTF-8 data unless the UTF-8 Byte Order Mark (BOM) is set.

I don't know if the CSV you are saving is retaining the UTF-8 characters. Maybe you should try opening the CSV in noteapd and see if you get the characters properly.

EDIT2 I just modified the code to have it read the CSV properly and the form has a new accept attribute.

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<?php
    ini_set('display_errors', 'On');
    error_reporting(-1);
    header('Content-Type: text/html; charset=utf-8');
    mb_internal_encoding('UTF-8');
    mb_http_output('UTF-8');
?>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8"/>
<html>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8"/>
<head>
    <style type="text/css">
    body
    {
        margin: 0;
        padding: 0;
        background-color:#D6F5F5;
        text-align:center;
    }
    .top-bar
        {
            width: 100%;
            height: auto;
            text-align: center;
            background-color:#FFF;
            border-bottom: 1px solid #000;
            margin-bottom: 20px;
        }
    .inside-top-bar
        {
            margin-top: 5px;
            margin-bottom: 5px;
        }
    .link
        {
            font-size: 18px;
            text-decoration: none;
            background-color: #000;
            color: #FFF;
            padding: 5px;
        }
    .link:hover
        {
            background-color: #9688B2;
        }
    </style>


</head>

<body>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8"/>
    <div class="top-bar">
        <div class="inside-top-bar">
            <a href="" width="500px"></a>
            <br><br>
            <a href="#" class="link">&larr; Back to main Website</a>
        </div>

    </div>
    <h1> CSV Should be in Below Format:</h1><b>Full Name:&nbsp;Employee Number:&nbsp;Department:&nbsp;Email:</b>
    <div style="border:1px dashed #333333; width:300px; margin:0 auto; padding:10px;">

    <form name="import" method="post" enctype="multipart/form-data" accept-charset="UTF-8">
        <input type="file" name="file" /><br />
        <input type="submit" name="submit" value="Submit" />
    </form>
<?php
    $link = mysqli_connect('dbhost', 'dbuser', 'dbpass', 'test_arabic');

    if (!$link) {
        echo "Error: Unable to connect to MySQL." . PHP_EOL;
        echo "Debugging errno: " . mysqli_connect_errno() . PHP_EOL;
        echo "Debugging error: " . mysqli_connect_error() . PHP_EOL;
        exit;
    }

    if(isset($_POST["submit"]))
    {   
        $file = $_FILES['file']['tmp_name'];
        $handle = fopen($file, "r");
        $c = 0;
        setlocale(LC_ALL, 'ar_AE.utf8');
        while(($filesop = fgetcsv($handle, 1000, ";")) !== false)
        {

            $name = $emp_number = $department = $email = '';
            $filesop2 = explode(',', $filesop[0]);
            $name = $filesop2['0'];
            $emp_number = $filesop2['1'];
            $department = $filesop2['2'];
            $email = $filesop2['3'];

            mysqli_query($link, "SET NAMES utf8");
            mysqli_query($link, "set characer set utf8");


            $sql = $link->query("INSERT INTO employees (first_name, emp_number,department,email) VALUES ('".$name."','".$emp_number."','".$department."','".$email."')");
            $c = $c + 1;
        }

            if($sql){
                echo "You database has imported successfully. You have inserted ". $c ." Records";
                echo '<td><a href="../view.php">&larr; View Inserted Records</a></td>';
            }else{
                echo "Sorry! There is some problem. <br>".$link->error;
            }

    }
?>

    </div>
    <hr style="margin-top:300px;" />    

    <div align="center" style="font-size:18px;"><b><a href="">&copy; ALL RIGHTS RESERVED BY DIBBA MUNICIPALITY FUJAIRAH</a></b></div>

</body>
</html>

@UAE ADVERTISER : don't know if this will be helpful, but in order to answer your request from another post, I tested the code that is [HERE](https://pastebin.com/hqYCh1pC). I created DB table as 'utf8_unicode_ci', csv has been created and saved as 'utf8 unicode' w/o BOM (reads ok with OpenOffice / Excel / Notepad), and result is ok -> opening + reading CSV as well as insert + fetch + display are ok. I've read plenty of post here and there regarding this problem, and as I'm stuck, I can only say : please check (again) all settings (DB and CSV), and pray for a *real* PHP guru :) — OldPadawan, Apr 05 '17 at 08:44
@UAEADVERTISER If you select this specific problem data from mysql commandline does it show up correctly? We can at least determine if the data is making it into the db or if it's only being displayed incorrectly. — Matt, Apr 05 '17 at 14:02
For my above code, open a new text document in Notepad++, paste this: `محمود عبدالعزيز,1,abc,def ` and save it as a .csv file and use my above code to process it. I have tested this and it's working on my local machine. — Kanad Godse, Apr 06 '17 at 05:34
@OldPadawan can you please help in http://stackoverflow.com/questions/43339944/password-in-md5-decrupted-to-english-php-mysql — UAE ADVERTISER, Apr 11 '17 at 07:57
@KanadGodse I tried your code and it returns me error.. Sorry! There is some problem. Incorrect string value: '\xDD\xC7\xD8\xE3\xC9\xA0' for column 'first_name' at row 1... — UAE ADVERTISER, Apr 16 '17 at 09:15

Import from CSV in PHP (mysql) shows characterses like '???'

1 Answers1

Linked