Wednesday 5 October 2016

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:

  1. $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

    ReplyDelete
    Replies
    1. $tmp = explode(".", $_FILES["employee_file"]["name"]);
      $extension = end($tmp);

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

      Delete
    3. $extension ="csv"; //end(explode(".", $_FILES["employee_file"]["name"]));

      if you are on developer plateform . means its urgent to import database using php script then you can put csv manually

      Delete
    4. if it doesn't works,,, try to put "@" on the statement..

      Delete
  2. 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

    ReplyDelete
  3. how can set for validation for your example. i am create a school project.

    ReplyDelete
  4. $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.

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

    and it works............. :) ty

    ReplyDelete
  6. 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

    ReplyDelete
  7. 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

    ReplyDelete
  8. $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

    ReplyDelete
  9. url:"export.php". It's import.php not export.php.

    ReplyDelete