Import CSV File Data into MySQL Database using PHP Ajax

Hello Friends in this tutorial we will discuss how can we import data from csv file to Mysql database without page refresh by using php script with jquery Ajax. Basically we have used csv file format data for storing large amount of data and it required less space as compared to excel file format. This format is basically used for import or export large amount of data from mysql table data and we can easily export or import csv data to mysql table data. And we will export or import large amount of data to mysql table in one single click. Here we will build php script for import csv data to mysql table data which will be execute though ajax call and it will fetch data from csv file and import that csv file data to mysql table data in one single click without page refresh. Here we define one form with one file element and one submit button, then after we have write jquery code on form submit event and in this event we have define ajax method and in ajax method we have pass form data by using form data object and send request to php file for execute php script and under this script we have put some validation like check file extension, suppose selected file extension is csv, then we have open selected file in read only mode by using file open function and then after we have get all csv data by line by line by using file get csv function and then after it will insert data into mysql employee table. This way we have import csv file data into mysql table without page refresh by using php with Jquery Ajax.

Source Code

tbl_employee

 --  
 -- Table structure for table `tbl_employee`  
 --  
 CREATE TABLE IF NOT EXISTS `tbl_employee` (  
  `id` int(11) NOT NULL AUTO_INCREMENT,  
  `name` varchar(50) NOT NULL,  
  `address` text NOT NULL,  
  `gender` varchar(10) NOT NULL,  
  `designation` varchar(100) NOT NULL,  
  `age` int(11) NOT NULL,  
  PRIMARY KEY (`id`)  
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=161 ;  
 --  
 -- Dumping data for table `tbl_employee`  
 --  
 INSERT INTO `tbl_employee` (`id`, `name`, `address`, `gender`, `designation`, `age`) VALUES  
 (1, 'Bruce Tom', '656 Edsel Road\r\nSherman Oaks, CA 91403', 'Male', 'Driver', 36),  
 (5, 'Clara Gilliam', '63 Woodridge Lane\r\nMemphis, TN 38138', 'Female', 'Programmer', 24),  
 (6, 'Barbra K. Hurley', '1241 Canis Heights Drive\r\nLos Angeles, CA 90017', 'Female', 'Service technician', 26),  
 (7, 'Antonio J. Forbes', '403 Snyder Avenue\r\nCharlotte, NC 28208', 'Male', 'Faller', 32),  
 (8, 'Charles D. Horst', '1636 Walnut Hill Drive\r\nCincinnati, OH 45202', 'Male', 'Financial investigator', 29);

index.php

 <?php  
 $connect = mysqli_connect("localhost", "root", "", "testing");  
 $query = "SELECT * FROM tbl_employee ORDER BY id desc";  
 $result = mysqli_query($connect, $query);  
 ?>  
 <!DOCTYPE html>  
 <html>  
      <head>  
           <title>Webslesson Tutorial | Import CSV File Data into MySQL Database using PHP & Ajax</title>  
           <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.0/jquery.min.js"></script>  
           <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />  
           <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>  
      </head>  
      <body>  
           <br /><br />  
           <div class="container" style="width:900px;">  
                <h2 align="center">Import CSV File Data into MySQL Database using PHP & Ajax</h2>  
                <h3 align="center">Employee Data</h3><br />  
                <form id="upload_csv" method="post" enctype="multipart/form-data">  
                     <div class="col-md-3">  
                          <br />  
                          <label>Add More Data</label>  
                     </div>  
                     <div class="col-md-4">  
                          <input type="file" name="employee_file" style="margin-top:15px;" />  
                     </div>  
                     <div class="col-md-5">  
                          <input type="submit" name="upload" id="upload" value="Upload" style="margin-top:10px;" class="btn btn-info" />  
                     </div>  
                     <div style="clear:both"></div>  
                </form>  
                <br /><br /><br />  
                <div class="table-responsive" id="employee_table">  
                     <table class="table table-bordered">  
                          <tr>  
                               <th width="5%">ID</th>  
                               <th width="25%">Name</th>  
                               <th width="35%">Address</th>  
                               <th width="10%">Gender</th>  
                               <th width="20%">Designation</th>  
                               <th width="5%">Age</th>  
                          </tr>  
                          <?php  
                          while($row = mysqli_fetch_array($result))  
                          {  
                          ?>  
                          <tr>  
                               <td><?php echo $row["id"]; ?></td>  
                               <td><?php echo $row["name"]; ?></td>  
                               <td><?php echo $row["address"]; ?></td>  
                               <td><?php echo $row["gender"]; ?></td>  
                               <td><?php echo $row["designation"]; ?></td>  
                               <td><?php echo $row["age"]; ?></td>  
                          </tr>  
                          <?php  
                          }  
                          ?>  
                     </table>  
                </div>  
           </div>  
      </body>  
 </html>  
 <script>  
      $(document).ready(function(){  
           $('#upload_csv').on("submit", function(e){  
                e.preventDefault(); //form will not submitted  
                $.ajax({  
                     url:"export.php",  
                     method:"POST",  
                     data:new FormData(this),  
                     contentType:false,          // The content type used when sending data to the server.  
                     cache:false,                // To unable request pages to be cached  
                     processData:false,          // To send DOMDocument or non processed data file it is set to false  
                     success: function(data){  
                          if(data=='Error1')  
                          {  
                               alert("Invalid File");  
                          }  
                          else if(data == "Error2")  
                          {  
                               alert("Please Select File");  
                          }  
                          else  
                          {  
                               $('#employee_table').html(data);  
                          }  
                     }  
                })  
           });  
      });  
 </script>

import.php

 <?php  
 if(!empty($_FILES["employee_file"]["name"]))  
 {  
      $connect = mysqli_connect("localhost", "root", "", "testing");  
      $output = '';  
      $allowed_ext = array("csv");  
      $extension = end(explode(".", $_FILES["employee_file"]["name"]));  
      if(in_array($extension, $allowed_ext))  
      {  
           $file_data = fopen($_FILES["employee_file"]["tmp_name"], 'r');  
           fgetcsv($file_data);  
           while($row = fgetcsv($file_data))  
           {  
                $name = mysqli_real_escape_string($connect, $row[0]);  
                $address = mysqli_real_escape_string($connect, $row[1]);  
                $gender = mysqli_real_escape_string($connect, $row[2]);  
                $designation = mysqli_real_escape_string($connect, $row[3]);  
                $age = mysqli_real_escape_string($connect, $row[4]);  
                $query = "  
                INSERT INTO tbl_employee  
                     (name, address, gender, designation, age)  
                     VALUES ('$name', '$address', '$gender', '$designation', '$age')  
                ";  
                mysqli_query($connect, $query);  
           }  
           $select = "SELECT * FROM tbl_employee ORDER BY id DESC";  
           $result = mysqli_query($connect, $select);  
           $output .= '  
                <table class="table table-bordered">  
                     <tr>  
                          <th width="5%">ID</th>  
                          <th width="25%">Name</th>  
                          <th width="35%">Address</th>  
                          <th width="10%">Gender</th>  
                          <th width="20%">Designation</th>  
                          <th width="5%">Age</th>  
                     </tr>  
           ';  
           while($row = mysqli_fetch_array($result))  
           {  
                $output .= '  
                     <tr>  
                          <td>'.$row["id"].'</td>  
                          <td>'.$row["name"].'</td>  
                          <td>'.$row["address"].'</td>  
                          <td>'.$row["gender"].'</td>  
                          <td>'.$row["designation"].'</td>  
                          <td>'.$row["age"].'</td>  
                     </tr>  
                ';  
           }  
           $output .= '</table>';  
           echo $output;  
      }  
      else  
      {  
           echo 'Error1';  
      }  
 }  
 else  
 {  
      echo "Error2";  
 }  
 ?>

14 comments:

Unknown15 October 2016 at 18:21
$extension = end(explode(".", $_FILES["employee_file"]["name"]));

Notice: Only variables should be passed by reference in C:\xampp\htdocs\test\import.php on line 7
Unknown14 December 2016 at 10:07
My csv file content is in this format,
Separated by a semicolon
How can i import this format csv file into mysql

name;address;gender;designation;age
Mustafa;3482 Pursglove Court, Rossburg, OH 45362;Female;Cost consultant;28
Yasemin;3827 Bingamon Road, Garfield Heights, OH 44125;Male;Manpower development advisor;64
Unknown3 July 2017 at 05:05
how can set for validation for your example. i am create a school project.
Krishna7 July 2017 at 05:54
thanks a lot
Unknown29 November 2017 at 23:25
$extension = end(explode(".", $_FILES["employee_file"]["name"]));
Notice: Only variables should be passed by reference in C:\xampp\htdocs\test\import.php on line 7

if the user sees this they will be shocked that what is happening.

you should try this code man.

$extension = explode(".", $_FILES["employee_file"]["name"]);
$file_extension = end($extension);

and it will be good.
Unknown8 December 2017 at 22:11
$extension = @end(explode(".", $_FILES["employee_file"]["name"]));

and it works............. :) ty
Ashfak16 March 2018 at 18:57
upload button iz not giving any response
as a result csv file is not uploading in database

needing immediate help
i copy
needing immediate help
D!VY@N$HU19 March 2019 at 01:55
the code works really great however the format of csv files that i get have lots of blank fields which enter null values in the data tables how do i tackle that in this code
dARU7 September 2019 at 04:20
$extension = end(explode(".", $_FILES["employee_file"]["name"]));

Notice: Only variables should be passed by reference in C:\xampp\htdocs\test\import.php on line 7
Nuno10 March 2020 at 02:54
url:"export.php". It's import.php not export.php.

Webslesson

PHP, Node.js, React.js, MySql, Jquery, AngularJS, Ajax, Codeigniter, Laravel Tutorial

Wednesday, 5 October 2016

Import CSV File Data into MySQL Database using PHP & Ajax

Source Code

tbl_employee

index.php

import.php

14 comments:

Popular Posts