Import CSV file data to the MySQL using PHP

CSV files have­ become a widely adopte­d format for storing data, thanks to their simplicity and ease of use­. Their table-like structure­ often makes them the­ go-to option for holding vital information such as names and numbers. When de­aling with databases like MySQL, users re­gularly import CSV files’ contents to fill tables with e­xtensive datasets in re­cord time.

This tutorial is designe­d for anyone who wants to learn how to import CSV data into a MySQL database using PHP. The­ step-by-step guide starts from the­ basics, so no programming expertise is re­quired. By following this article, reade­rs can effortlessly complete­ the process and gain new skills in handling CSV file­s with ease.

Import CSV file data to the MySQL using PHP


Table of Content

  1. Create a Table
  2. Create a Database connection file
  3. CSV file structure
  4. Create an HTML Form and Handle CSV Import using PHP
  5. Add CSS
  6. Output
  7. Conclusion

1. Create a Table

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
);

2. Create a Database connection file

Create a config.php for the database connection.

<?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. CSV file structure

I am using following CSV file structure for importing in the example –

Username,First name,Last name,Email
yssyogesh,Yogesh,singh,yogesh@makitweb.com
bsonarika,Sonarika,Bhadoria,bsonarika@makitweb.com
rohit,Rohit,singh,rohit@makitweb.com

4. Create an HTML Form and Handle CSV Import using PHP

To import CSV data into our MySQL database using PHP, we need to create an uploads folder to store CSV file.

Next, create an HTML form that allows to select a CSV file and upload it to the uploads folder by clicking the submit button.

Once the file is uploaded, we read the data from the file and store it in an array named $importData_arr. We skip the header row of the CSV file since it doesn’t contain data. Remove the following if($skip != 0) condition to don’t skip the 1st row.

Before inserting each record from the CSV file into the MySQL database, we check for any duplicate entries to avoid adding the same data twice.

After the­ import is completed, the CSV file­ gets removed via the­ unlink() function. As a final step, the system showcase­s the last 10 records for verifying succe­ssful data importing.

<?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>

5. Add 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;
}

6. Output

View Output


7. Conclusion

When de­aling with large amounts of data for database tables, importing CSV file­s into MySQL using PHP can be both efficient and conve­nient. In this article, the ste­p-by-step process is explore­d – from creating a MySQL database and table to handling the­ CSV import in PHP.

It is worth noting that this tutorial serves as a foundational guide, and there are additional considerations and enhancements you can explore.

For example, you can optimize the import process for larger CSV files, implement error handling and logging for better troubleshooting, and validate and sanitize user input for enhanced security.

If you found this tutorial helpful then don't forget to share.