Import CSV file data to the MySQL using PHP

In PHPMyAdmin you can import data in the database table in various formats e.g. SQL, CSV XML, etc. formats.

While import CSV file in PHPMyAdmin need to make sure that it should be the valid format like – the number of columns, data.

Similar functionality can be implemented with PHP where read the uploaded file and insert the record in the MySQL database table.

It is possible to check for duplicate entry with PHP while import but it is not possible in PHPMyAdmin.

Import CSV file data to the MySQL using PHP


Contents

  1. Table structure
  2. Configuration
  3. HTML and PHP
  4. CSS
  5. Conclusion

 


 

1. Table structure

I am using user table in the tutorial demonstration.

CREATE TABLE `user` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `username` varchar(80) NOT NULL,
  `fname` varchar(50) NOT NULL,
  `lname` varchar(50) NOT NULL,
  `email` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

2. Configuration

Create a config.php for database connection.

Completed Code

<?php

$host = "localhost";    /* Host name */
$user = "root";         /* User */
$password = "";         /* Password */
$dbname = "tutorial";   /* Database name */

$con = mysql_connect($host, $user, $password) or die("Unable to connect");

// selecting database
$db = mysql_select_db($dbname, $con) or die("Database not found");

 

3. HTML and PHP

Create a uploads directory for storing the CSV file.

In HTML create a <form > with file element and a submit button. On submit, button click uploads the selected file in the uploads directory.

Read the uploaded file and initialize the $importData_arr array with the data. Loop on $importData_arr and skip the header row.

Check for duplicate entry before insert record in MySQL database table.

Remove the CSV file after successfully importing and display the last 10 records to check data imported or not.

Completed Code

<?php
include "config.php";

$total_data = 0; $import_count = 0;
if(isset($_POST['but_import'])){
  $target_dir = "uploads/";
  $target_file = $target_dir . basename($_FILES["importfile"]["name"]);

  $imageFileType = pathinfo($target_file,PATHINFO_EXTENSION);

  $uploadOk = 1;
  if($imageFileType != "csv" ) {
    $uploadOk = 0;
  }

  if ($uploadOk != 0) {
    if (move_uploaded_file($_FILES["importfile"]["tmp_name"], $target_dir.'importfile.csv')) {

     // Checking file exists or not
     $target_file = $target_dir . 'importfile.csv';
     $fileexists = 0;
     if (file_exists($target_file)) {
       $fileexists = 1;
     }
     if ($fileexists == 1 ) {

      // Reading file
      $file = fopen($target_file,"r");
      $i = 0;

      $importData_arr = array();
      while(! feof($file)) {

       foreach(fgetcsv($file) as $key=>$value){
        $importData_arr[$i][] = $value;
       }

       $i++;
     }
     fclose($file);

     $skip = 0;
     // insert import data
     foreach($importData_arr as $data){
      if($skip != 0){
       $username = $data[0];
       $fname = $data[1];
       $lname = $data[2];
       $email = $data[3];

       // Checking duplicate entry
       $sql = "select count(*) as allcount from user where username='" . $username . "' and fname='" . $fname . "' and  lname='" . $lname . "' and email='" . $email . "' ";

       $retrieve_data = mysql_query($sql);
       $row = mysql_fetch_array($retrieve_data);
       $count = $row['allcount'];

       if($count == 0){
        // Insert record
        $insert_query = "insert into user(username,fname,lname,email) values('".$username."','".$fname."','".$lname."','".$email."')";
        mysql_query($insert_query);
       }
      }
      $skip ++;
     }
     $newtargetfile = $target_file;
     if (file_exists($newtargetfile)) {
       unlink($newtargetfile);
     }
    }
   }
  }
}
?>
 
<!-- Import form (start) -->
<div class="popup_import">
 <form method="post" action="" enctype="multipart/form-data" id="import_form">
  <table width="100%">

   <tr>
    <td colspan="2">
     <input type='file' name="importfile" id="importfile">
    </td>
   </tr>
   <tr>
    <td colspan="2" ><input type="submit" id="but_import" name="but_import" value="Import"></td>
   </tr>
   <tr>
    <td colspan="2" align="center"><span class="format">Username, First name, Last name,Email</span> </td>
   </tr>
   <tr>
    <td colspan="2" align="center"><a href="import_example.csv" target="_blank">Download Sample</a></td>
   </tr>

   <tr>
    <td colspan="2"><b>Instruction : </b><br/>
     <ul>
      <li>Enclose text field in quotes (' , " ) if text contains comma (,) is used.</li>
      <li>Enclose text field in single quotes (') if text contains double quotes (")</li>
      <li>Enclose text field in double quotes (") if text contains single quotes (')</li>
     </ul>
    </td>
   </tr>
  </table>
 </form>
</div>
<!-- Import form (end) -->

<!-- Displaying imported users -->
<table border="1" id="userTable">
  <tr>
   <td>S.no</td>
   <td>Username</td>
   <td>First name</td>
   <td>Last name</td>
   <td>Email</td>
  </tr>
  <?php
    $sql = "select * from user order by id desc limit 10";
    $sno = 1;
    $retrieve_data = mysql_query($sql);
    while($row = mysql_fetch_array($retrieve_data)){
        $id = $row['id'];
        $username = $row['username'];
        $fname = $row['fname'];
        $lname = $row['lname'];
        $email = $row['email'];

        echo "<tr>
            <td>".$sno."</td>
            <td>".$username."</td>
            <td>".$fname."</td>
            <td>".$lname."</td>
            <td>".$email."</td>

        </tr>";
        $sno++;
    }
   ?>
</table>

 

4. CSS

.popup_import{
 border: 1px solid black;
 width: 550px;
 height: auto;
 background: white;
 border-radius: 3px;
 margin: 0 auto;
 padding: 5px;

}

.format{
 color: red;
}

#userTable{
 border-collapse: collapse;
 margin: 0 auto;
 margin-top: 15px;
 width: 550px;
}

#but_import{
 margin-left: 10px;
}


 

5. Conclusion

With above PHP script, you can provide an interface to the users for import data and insert it into MySQL database table with PHP.

Remove the skip if($skip != 0) condition from the PHP script if the CSV file doesn’t have the header row.

Related Post

Spread the love

2 Comments

  1. gunjan said:

    it doesn’t restrict duplicacy in the data

    March 24, 2017
    Reply
    • Yogesh Singh said:

      Thank you for your comment.

      I checked the code and it working fine. It doesn’t allow duplicate entries to insert.

      March 25, 2017
      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *