How to import csv file data into mysql database in php

By | August 3, 2017
import csv file

Hello friends, In this tutorial i am going to tell your to import csv file  data into mysql database using PHP. First you should to know that in which situation you need to implement this code. Let’s assume you are developing an application in which users have some data in csv file and want to upload this csv sheet into your database then it is important to develop a panel from where a user can be able to upload csv file. So Please follow the steps to develop such a panel. I am here taking an example to show how it is working.

  1. Create a table into your database
  2. Create file upload box in html
  3. Write server side script to import the csv sheet into a table
  1. I am creating  user table with name, email, phone, created_at, updated_at fields.
CREATE TABLE `users` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
 `email` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
 `phone` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
 `created_at` datetime NOT NULL,
 `updated_at` datetime NOT NULL,
 `is_active` enum('1','0') COLLATE utf8_unicode_ci NOT NULL DEFAULT '1',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Then create a file named as index.php with html code to upload csv sheet

<!DOCTYPE html>
<html lang="en">

<head>
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" crossorigin="anonymous">
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap-theme.min.css" crossorigin="anonymous">
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js" crossorigin="anonymous"></script>

</head>

<body>
    <div id="wrap">
        <div class="container">
            <div class="row">

                <form class="form-horizontal" action="upload_process.php" method="post" name="upload_csv" enctype="multipart/form-data">
                    <fieldset>

                        <!-- Form Name -->
                        <legend>Form Name</legend>

                        <!-- File Button -->
                        <div class="form-group">
                            <label class="col-md-4 control-label" for="filebutton">Select File</label>
                            <div class="col-md-4">
                                <input type="file" name="filename" id="filename" class="input-large">
                            </div>
                        </div>

                        <!-- Button -->
                        <div class="form-group">
                            <label class="col-md-4 control-label" for="singlebutton">Import CSV</label>
                            <div class="col-md-4">
                                <button type="submit" id="submit" name="Import CSV" class="btn btn-primary button-loading" data-loading-text="Loading...">Import CSV</button>
                            </div>
                        </div>

                    </fieldset>
                </form>

            </div>
           
        </div>
    </div>
</body>

</html>

Then after create another php file named as upload_process.php with database connection to process the uploaded sheet and import data from this sheet to your users table created in the first step.

<?php

$host = 'localhost';
$username = 'root';
$password = '';
$database_name = 'user_record';

$con = mysqli_connect($host, $username, $password, $database_name); 


 if(isset($_POST["upload_csv"])){
    
    $filename=$_FILES["filename"]["tmp_name"];		


     if($_FILES["filename"]["size"] > 0)
     {
      	$file = fopen($filename, "r");
          while (($getData = fgetcsv($file, 10000, ",")) !== FALSE)
           {


             $query = "INSERT into users(name,email,phone,created_at,updated_at) 
                   values ('".$getData[0]."','".$getData[1]."','".$getData[2]."','".date('Y-m-d H:i:s')."','".date('Y-m-d H:i:s')."')";
                   $result = mysqli_query($con, $query);
        if(!isset($result))
        {
          echo "<script type=\"text/javascript\">
              alert(\"Invalid File:Please Upload valid CSV File.\");
              window.location = \"index.php\"
              </script>";		
        }
        else {
            echo "<script type=\"text/javascript\">
            alert(\"CSV File has been Imported successfully.\");
            window.location = \"index.php\"
          </script>";
        }
           }
      
           fclose($file);	
     }
  }	 


 ?>

In this file i am checking if upload_csv form is posted then checking whether file has some content or not then using fgetcsv() in build php function  read all data from sheet to array and inserting it to the users table. Here one point you have to notice that number of column in the sheet and number of column in the table in which you are inserting should have equal otherwise it will throw an error.