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

12 comments:

AboHamza011428 September 2018 at 17:24
there is error on php file line 7 , correct is :
$file_data = fopen($_FILES['csv_file']['tmp_name'], 'r');
Unknown20 December 2018 at 08:51
its nor working
Unknown21 March 2019 at 06:39
Please help me. I want to load CSV automatically on page load (not after button click). How can I do this? I change script and import.php, but not works.

//import.php

$file_data = fopen('output.csv', 'r');
fgetcsv($file_data);
while($row = fgetcsv($file_data))
{
$data[] = array(
'Code' => $row[0],
'Name' => $row[1],
'Unit' => $row[2],
'Stock' => $row[3],
'Price' => $row[4]
);
}
echo json_encode($data);

.............................

index.php

...

$(document).ready(function(){
$.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 : "Code" },
{ data : "Name" },
{ data : "Unit" },
{ data : "Stock" },
{ data : "Price" }
]
});
}
});
});

NOT WORKING ...
Unknown21 March 2019 at 06:41

$(document).ready(function(){
$.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 : "Code" },
{ data : "Name" },
{ data : "Unit" },
{ data : "Stock" },
{ data : "Price" }
]
});
}
});
});

PLS I want to load CSV automatically on pageload. Pls HELP
Unknown26 June 2019 at 18:49
Merci, super tuto
Helmuntv29 July 2019 at 06:15
hello, what happens if in my datatable there is a selection field and in one of the fields of my csv I enter something in that field, example: in the select field I have BOGOTÁ and in my csv digito bogota, and I want to make them match to When you upload the file.

I appreciate your help
Helmuntv29 July 2019 at 06:16

hello, what happens if in my datatable there is a selection field and in one of the fields of my csv I enter something in that field, example: in the select field I have BOGOTÁ and in my csv digito bogota, and I want to make them match to When you upload the file.

I appreciate your help
The Journey21 October 2019 at 12:22
Thankyou for your example here, and also thanks ABO Hama for the fix. Hopeing to use this script for importing csv file and exporting a csv file based on what is imported.
Unknown19 March 2020 at 04:37
This is incorrect!!!!!!
$file_data = fopen($_FILES['csv_file']['name'], 'r');

Should be:
$file_data = fopen($_FILES['csv_file']['tmp_name'], 'r');

Please update it on your code above so people wont waste time wondering why it isn't working!
mf18 May 2020 at 04:12
doesnt work
Henry G.3 September 2021 at 10:14
Hi,
I need your help. I need a code to update only the qty from a list with the columns (part_number and qty)

Webslesson

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

Friday, 27 July 2018

How to Load CSV File in JQuery Datatables using Ajax PHP

index.php

import.php

tbl_student.csv

12 comments:

Popular Posts