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 the CSV file in PHPMyAdmin needs to make sure that it should be a 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 the database connection.

Completed Code

<?php

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

$con = mysqli_connect($host, $user, $password,$dbname);
// Check connection
if (!$con) {
  die("Connection failed: " . mysqli_connect_error());
}

3. HTML and PHP

Create a uploads folder 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 using unlink() and display the last 10 records to check data imported or not.

Completed Code

<?php
include "config.php";

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 (($data = fgetcsv($file, 1000, ",")) !== FALSE) {
             $num = count($data);

             for ($c=0; $c < $num; $c++) {
                $importData_arr[$i][] = mysqli_real_escape_string($con, $data[$c]);
             }
             $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 = mysqli_query($con,$sql);
                 $row = mysqli_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."')";
                    mysqli_query($con,$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 = mysqli_query($con,$sql);
    while($row = mysqli_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 the above PHP script, you can provide an interface to the users for import data and insert it into a 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.

If you found this tutorial helpful then don't forget to share.
Are you want to get implementation help, or modify or extend the functionality of this script? Submit paid service request.

22 thoughts on “Import CSV file data to the MySQL using PHP”

  1. Thank you for sharing!
    I have encountered the following errors and wonder if you could help?

    Warning: Invalid argument supplied for foreach() in C:\xampp\htdocs\index.php on line 39
    Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\index.php on line 60
    Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\index.php on line 131

    Reply
  2. Thank you for your reply. I have tried to solve this warning by cast that variable to an array in the loop, but it doesn’t seem to work. Hope you can assist.
    foreach((array) fgetcsv($file) as $key=>$value)

    Reply
  3. Hello!
    Can you help me with the error:
    Warning: Invalid argument supplied for foreach() in C:\xampp\htdocs\index.php on line 39
    Thank you for your attention!
    Regards.

    Reply
  4. I tried the code from the tutorial video in youtube but I’m using wampsever instead of xampp. The problem is everytime I upload the csv file then checked the database, nothing is added, the table was empty.

    Reply
  5. Access forbidden!
    You don’t have permission to access the requested object. It is either read-protected or not readable by the server.

    I am getting this message. How to over come this???

    Reply

Leave a Comment