Import CSV file data to the MySQL using PHP

In this tutorial, I will show How you can insert data into MySQL table from CSV file using PHP.

The common example of this you will see in PHPMyAdmin where you can import data to MySQL table in multiple formats (.sql, .csv, .xml, etc).

What is CSV?

CSV stands for Comma Separated Values, that are stored as plain text which is separated by commas.

If you are a developer, you already heard about it and  you used it many times like – in reports, for importing data, etc.

What we create?

In the tutorial demonstration, we will create a upload area from where the user can upload CSV file and click on import button for importing CSV file data to MySQL table.

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

We are only using one Table in the tutorial example. user table. Where we store CSV file data.

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 new PHP file (config.php). This file contains database connection. In this tutorial, we are using a tutorial database.

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 new index.php file this file contain below code and also create a uploads folder for storing uploaded file temporarily which we delete after data imported  successfully to the user table.

Completed Code

<!doctype html>
<html>
    <head>
        <title>Import CSV file data to the MySQL using PHP</title>
        <link href="style.css" type="text/css" rel="stylesheet">

        <?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);
                        }
                    }
                }
            }
        }
        ?>
    </head>
    <body>
    <!-- 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>
    </body>
</html>

Explanation

First created a form which has a file element and a submit button. When the to submit button is gets clicked uploading the selected file to uploads folder.

When the file uploaded successfully, then start reading the file in read mode and initializing the $importData_arr array. After initializing array variable looping over it and skipping 1st header row and checking duplicate entry before insert.

Note – If your CSV file doesn’t have header column you can remove if($skip != 0) condition from foreach loop.

After importing data successfully deleting uploaded CSV file from upload folder.

And for checking data is imported or not displaying last 10 records from the user table.


 

4. CSS

Adding some CSS.

Completed Code

.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

We have learned how to import CSV file data to MySQL table using PHP, for reading CSV file used some FILE handling functions.

Spread the love

Related Post

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 *