Friday, 27 July 2018

How to Load CSV File in JQuery Datatables using Ajax PHP



This post covert How to Import CSV file data into JQuery Datatables plugin using PHP script with Ajax without refresh of web page. CSV file is stand for Comma-Seperated values which stores tabular data like numbers or text in plain text format which has been widely used for large amount of data. And same way jquery Datatables plugin is a powerful jquery plugin for display data on webpage in tabular format with extra feature like searching of table data, sorting of data, pagination of data with client side or server side processing without writing single line of code. So, Now in this post we have discuss how to use CSV file with JQuery Datatables plugin or How to load or Import CSV file data in Jquery Datatables plugin on web page.

In CSV file we can store very large amount of data then we want to search data in CSV file then it is very difficult to find any data from CSV file but if that data has been loaded into Datatables plugin in tabular format then we can easily search or filter data in this plugin. So there is a question how to load CSV File data into JQuery Datatables in our web application. For solve this problem we have make this post in which we have covered step by step to import CSV File string delimited data into tabular format in Jquery Datatables plugin using PHP code with Ajax and can perform client side processing of CSV file data.

For Importing of CSV file Data first we have to upload CSV file. For uploading of CSV file here we have use Ajax, so by using Ajax with PHP we will upload file without refresh of webpage. When we have send file to PHP script using Ajax then first by using fopen() function we have read csv file and store csv file data into one variable. After this by using fgetcsv() function we have converted csv file plain text data into PHP array. After this for fetch data from PHP Array we have use while loop and store into single array in Array format. Lastly for convert PHP array to JSON string we have use json_encode() function and send to Ajax request and under success function Ajax we have initialiaze Jquery Datatables plugin by using DataTable() method with json data source which has been define in data option. This way we can import or load CSV file data into Jquery Datatables plugin by using Ajax with PHP. Below you can find complete source code of this tutorial.










index.php



<?php

//index.php

?>
<!DOCTYPE html>
<html>
 <head>
  <title>Import CSV File into Jquery Datatables using PHP Ajax</title>
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
  <script src="https://cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js"></script>
  <script src="https://cdn.datatables.net/1.10.12/js/dataTables.bootstrap.min.js"></script>  
  <link rel="stylesheet" href="https://cdn.datatables.net/1.10.12/css/dataTables.bootstrap.min.css" />
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>
  <style>
  .box
  {
   max-width:600px;
   width:100%;
   margin: 0 auto;;
  }
  </style>
 </head>
 <body>
  <div class="container">
   <br />
   <h3 align="center">Import CSV File into Jquery Datatables using PHP Ajax</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="csv_file" id="csv_file" accept=".csv" 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 />
   <div class="table-responsive">
    <table class="table table-striped table-bordered" id="data-table">
     <thead>
      <tr>
       <th>Student ID</th>
       <th>Student Name</th>
       <th>Phone Number</th>
      </tr>
     </thead>
    </table>
   </div>
  </div>
 </body>
</html>

<script>

$(document).ready(function(){
 $('#upload_csv').on('submit', function(event){
  event.preventDefault();
  $.ajax({
   url:"import.php",
   method:"POST",
   data:new FormData(this),
   dataType:'json',
   contentType:false,
   cache:false,
   processData:false,
   success:function(jsonData)
   {
    $('#csv_file').val('');
    $('#data-table').DataTable({
     data  :  jsonData,
     columns :  [
      { data : "student_id" },
      { data : "student_name" },
      { data : "student_phone" }
     ]
    });
   }
  });
 });
});

</script>


import.php



<?php

//import.php

if(!empty($_FILES['csv_file']['name']))
{
 $file_data = fopen($_FILES['csv_file']['name'], 'r');
 fgetcsv($file_data);
 while($row = fgetcsv($file_data))
 {
  $data[] = array(
   'student_id'  => $row[0],
   'student_name'  => $row[1],
   'student_phone'  => $row[2]
  );
 }
 echo json_encode($data);
}

?>


tbl_student.csv



student_id,student_name,student_phone
1,Pauline S. Rich,412-735-0224
2,Sarah C. White,320-552-9961
3,Samuel L. Leslie,201-324-8264
4,Norma R. Manly,478-322-4715
5,Kimberly R. Castro,479-966-6788
6,Elaine R. Davis,701-685-8912
7,Concepcion S. Gardner,607-829-8758
8,Patricia J. White,803-789-0429
9,Michael M. Bothwell,214-585-0737
10,Ronald C. Vansickle,630-571-4107
11,Clarence A. Rich,904-459-3747
12,Elizabeth W. Peterson,404-380-9481
13,Renee R. Hewitt,323-350-4973
14,John K. Love,337-229-1983
15,Teresa J. Rincon,216-394-6894
16,Erin S. Huckaby,503-284-8652
17,Brian A. Handley,989-304-7122
18,Michelle A. Polk,540-232-0351
19,Wanda M. Brown,718-262-7466
20,Phillip A. Hatcher,407-492-5727
21,Dennis J. Terrell,903-863-5810
22,Britney F. Johnson,972-421-6933
23,Rachelle J. Martin,920-397-4224
24,Leila E. Ledoux,615-425-9930
25,Darrell A. Fields,708-887-1913
26,Linda D. Carter,909-386-7998
27,Melva J. Palmisano,630-643-8763
28,Jessica V. Windham,513-807-9224
29,Karen T. Martin,847-385-1621
30,Jack K. McDonough,561-641-4509
31,John M. Williams,508-269-9346
32,Amelia W. Davis,347-537-8052
33,Gertrude W. Lawrence,510-702-7415
34,Michael L. Harris,252-219-4076
35,Casey A. Groves,810-334-9674
36,James H. Wilson,865-259-6772
37,James A. Wesley,443-217-1859
38,Armando C. Gay,716-252-9230
39,James M. Duarte,402-840-0541
40,Jason E. West,360-610-7730
41,Gloria H. Saucedo,205-861-3306
42,Paul T. Moody,914-683-4994
43,Sandra L. Williams,310-335-1336
44,Elaine T. Deville,626-513-8306
45,Robyn L. Spangler,754-224-7023
46,Sam A. Pino,806-823-5344
47,Joseph H. Marble,201-917-2804
48,Mark M. Bassett,206-592-4665
49,Edgar M. Billy,978-365-0324
50,Connie M. Yang,815-288-5435

1 comment:

  1. there is error on php file line 7 , correct is :
    $file_data = fopen($_FILES['csv_file']['tmp_name'], 'r');

    ReplyDelete