Unable to import single csv file that contains unicode data to multiple tables using php and mysql

Question

I'm trying to import single csv file that contains unicode data to multiple tables using php and mysql .I'm able to insert data to multiple tables but doesnot support unicode. I have csv file with nepali data . Please help me. Thanks in advance

<?php
header('Content-Type: charset=utf-8');
  require_once('conn.php');
if(isset($_POST["submit"])){

          $file = $_FILES['file']['tmp_name'];
          $handle = fopen($file, "r");
          // echo $file;die;
          $c = 0;
          while(($filesop = fgetcsv($handle, 1000, ",")) !== false)
          // while(($filesop = fgetcsv($handle, 1000, "\t")) !== false)
                    {
                         $filesop = array_map("utf8_encode", $filesop);
          // var_dump($filesop);die;
          $id = $filesop[0];
          // echo $id;die;
          $registration_date = $filesop[1];

          $building_use_id = $filesop[2];
          $building_category = $filesop[3];


          $a=utf8_encode($registration_date);

          $bps_registration_newregistration = "insert into bps_registration_newregistration(id,registration_date) values ('$id','$registration_date')";
          $stmt = mysqli_prepare($conn,$bps_registration_newregistration);
          mysqli_stmt_execute($stmt);
 $bps_registration_application = "insert into bps_registration_application(reg_id,registration_date,building_use_id,building_category) values ('$id','$a','$building_use_id','$building_category')";
          $stmt1 = mysqli_prepare($conn,$bps_registration_application);
          mysqli_stmt_execute($stmt1);


         $c = $c + 1;
           }

            if($bps_registration_newregistration  && $bps_registration_application ){
               echo "sucess";
             } 

     else
     {
            echo "Sorry! Unable to impo.";
          }
}
?>
<!DOCTYPE html>
<html>
<meta http-equiv="Content-type" content="text/html; charset=utf-8" />
<body>
<form enctype="multipart/form-data" method="post" role="form" accept-charset="UTF-8">
    <div class="form-group">
        <label for="exampleInputFile">File Upload</label>
        <input type="file" name="file" id="file" size="150">
        <p class="help-block">Only Excel/CSV File Import.</p>
    </div>
    <button type="submit" class="btn btn-default" name="submit" value="submit">Upload</button>
</form>
</body>
</html>

Does this help you? https://mathiasbynens.be/notes/mysql-utf8mb4 — Marc Dix, Aug 27 '19 at 16:24
Can you show the first row of the CSV in a text file? Maybe it's tab separated instead of comma, or uses another separator — aynber, Aug 27 '19 at 16:27
I converted csv file to text file and i added image in question. @aynber — Prabina, Aug 27 '19 at 16:33
I used utf-8 in mysql database but still it gave me problem @Marc Dix — Prabina, Aug 27 '19 at 16:35
Possible duplicate of [UTF-8 all the way through](https://stackoverflow.com/questions/279170/utf-8-all-the-way-through) — Matt Raines, Aug 27 '19 at 16:58

score 0 · Answer 1 · answered Aug 27 '19 at 16:36

0

Your file is tab-separated, not comma-separated, so you need to tell the function that:

while(($filesop = fgetcsv($handle, 1000, "\t")) !== false)

answered Aug 27 '19 at 16:36

aynber

22,380
8
50
63

I still got same issue. How can isolve it as well asunicode issue? @aynber – Prabina Aug 27 '19 at 16:51
It's still giving you undefined offsets? You may need to `var_dump($filesop)` to see what it all contains. – aynber Aug 27 '19 at 17:29
When i var_dump inside while loop, and upload csv file and click upload button , it says me to save file in my computer. @aynber – Prabina Aug 27 '19 at 17:35
That is weird. It should not ask you to download anything – aynber Aug 27 '19 at 17:39
But i'm unable to obtain data from csv file. It shows only header . @aynber array(1) { [0]=> string(84) "id,registration_date,contactno,latitude,building_use_id,building_category,ch_name_np" } – Prabina Aug 28 '19 at 03:52
The header is CSV, and the rest is tab? You need to get the file into a consistent format. – aynber Aug 28 '19 at 12:39
Can I ask another question? @aynber – Prabina Aug 31 '19 at 17:03

score 0 · Answer 2 · answered Aug 28 '19 at 04:59

Its your database Unicode format issue, change your database encoding to UTF-8 with General_CI and same for the targeted tables. My recommendation will be to use Navicat to do that as it will be simple to convert the MySQL Database and tables formatting.

Remember to Change formatting of database as well its tables also.

score 0 · Answer 3 · answered Aug 31 '19 at 01:39

Instead of writing PHP code with fgetcsv, use MySQL's LOAD DATA INFILE to pull it directly into a table. Be sure to create the table with CHARACTER SET utf8 (or utf8mb4).

Use SELECT col, HEX(col) ... to fetch the hex of some of the Nepali text so we can verify its encoding. Assuming it is using the Devanagari alphabet, I would expect to see mostly E0 A4 xx and E0 A5 xx in the hex. That's 3 bytes per character.

Even if you get it loaded via PHP code, verify the table that way. If you have problems, see Trouble with UTF-8 characters; what I see is not what I stored

Unable to import single csv file that contains unicode data to multiple tables using php and mysql

3 Answers3