Friday 21 April 2017

CSV File to HTML Table Using AJAX jQuery



This is the simple web tutorial in which We are going to learn how to import CSV file into HTML table by using Jquery Ajax method. In simple words we will parse CSV file data and display into HTML table format on web page by using Ajax JQuery method. Here We will not use any JQuery plugin or library for read CSV file and display CSV file data in HTML table. We all know CSV stands for Comma Separated Values file format and in this we have store data in string format which are separated by comma. This format file we can also open excel also so it is widely used for exchange data between application.

Here We have one file which we have stored in our working folder and we want to read data from that CSV file by using Ajax and after getting data in string format we will convert into HTML table by using Jquery. If you have developed any web application and you want to load data from CSV file then you can follow this code in which we do not have used any server side script but we have used simply client side script like Ajax and JQuery. This will not increase load on your system. In this script we will not upload CSV file on our system but we will only give CSV file into Ajax request. So Via Ajax request we can get data from CSV file and by using JavaScript we will converted into HTML table code.

Here we have perform all operation at client side, so no load has been come on our server. By using this script we can load large number of data from CSV file to HTML table. This type of feature is very useful if you have build some large project in which if you want to reduce load on database server, then you have so load some data from CSV file, so it will reduce some on database server. So, this our simple tutorial in which we have display CSV file in HTML Table on web page.








Source Code


index.html



<!DOCTYPE html>
<html>
 <head>
  <title>CSV File to HTML Table Using AJAX jQuery</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>
  <div class="container">
   <div class="table-responsive">
    <h1 align="center">CSV File to HTML Table Using AJAX jQuery</h1>
    <br />
    <div align="center">
     <button type="button" name="load_data" id="load_data" class="btn btn-info">Load Data</button>
    </div>
    <br />
    <div id="employee_table">
    </div>
   </div>
  </div>
 </body>
</html>

<script>
$(document).ready(function(){
 $('#load_data').click(function(){
  $.ajax({
   url:"employee.csv",
   dataType:"text",
   success:function(data)
   {
    var employee_data = data.split(/\r?\n|\r/);
    var table_data = '<table class="table table-bordered table-striped">';
    for(var count = 0; count<employee_data.length; count++)
    {
     var cell_data = employee_data[count].split(",");
     table_data += '<tr>';
     for(var cell_count=0; cell_count<cell_data.length; cell_count++)
     {
      if(count === 0)
      {
       table_data += '<th>'+cell_data[cell_count]+'</th>';
      }
      else
      {
       table_data += '<td>'+cell_data[cell_count]+'</td>';
      }
     }
     table_data += '</tr>';
    }
    table_data += '</table>';
    $('#employee_table').html(table_data);
   }
  });
 });
 
});
</script>


employee.csv



Name,Address,Gender,Designation,Age
John Smith,656 Edsel Road Sherman Oaks CA 91403,Male,Manager,40
Clara Berry,63 Woodridge Lane Memphis TN 38138,Male,Programmer,22
Barbra K. Hurley,1241 Canis Heights Drive Los Angeles CA 90017,Female,Service technician,26
Antonio J. Forbes,403 Snyder Avenue Charlotte NC 28208,Male,Falling,32
Charles D. Horst,1636 Walnut Hill Drive Cincinnati OH 45202,Male,Financial investigator,29
Beau L. Clayton,3588 Karen Lane Louisville KY 40223,Male,Extractive metallurgical engin,33
Ramona W. Burns,2170 Ocala Street Orlando FL 32801,Female,Electronic typesetting machine operator,27
Jennifer A. Morrison,2135 Lakeland Terrace Plymouth MI 48170,Female,Rigging chaser,29
Susan Juarez,3177 Horseshoe Lane Norristown PA 19403,Male,Control and valve installe,52
Ellan D. Downie,384 Flynn Street Strongsville OH 44136,Female,Education and training manager,26
Larry T. Williamson,1424 Andell Road Brentwood TN 37027,Male,Teaching assistant,30
Lauren M. Reynolds,4798 Echo Lane Kentwood MI 49512,Female,Internet developer,22
Joseph L. Judge,3717 Junkins Avenue Moultrie GA 31768,Male,Refrigeration mechanic,35
Eric C. Lavelle,1120 Whitetail Lane Dallas TX 75207,Male,Model,21
Cheryl T. Smithers,1203 Abia Martin Drive Commack NY 11725,Female,Personal banker,23
Tonia Diaz,4724 Rocky Road Philadelphia PA 19107,Female,Facilitator,29
Stephanie P. Lederman,2117 Larry Street Waukesha WI 53186,Female,Mental health aide,27
Edward F. Sanchez,2313 Elliott Street Manchester NH 03101,Male,Marine oilerp,28
Peter Parker,403 Snyder Avenue Charlotte NC 28208,Male,Programmer,28
John Smith,384 Flynn Street Strongsville OH 44136,Male,Web Developer,25
Mark Boucher,256 Olive Street NY,Male,Techbical Assistance,23

38 comments:

  1. Hi Thx for nice tutorials. How about autocomplete search boxes (serverside processing) that and allow you to export the results to Excel or CSV in table format. Its difficult to export thousands of rows with many columns from a normal table. Pls be so kind

    ReplyDelete
  2. How do you get around the cross site error? "Cross origin requests are only supported for protocol schemes: http, data, chrome, chrome-extension, https"

    ReplyDelete
  3. thank you sir for sharing your knowledge and tutorials I really learn from it I hope there's more tutorials to come

    ReplyDelete
  4. doesnt work, i get a blank html page

    ReplyDelete
  5. how to upload a csv file data into jquery datatables

    ReplyDelete
  6. Why this work only with Firefox browser?
    Its not working with Googel Chrome Version 68.0.3440.106 (Official Build) (64-bit)
    and its also not working with IE-1

    ReplyDelete
  7. Hi,
    what must modify if employee_data is on Cyrillic?
    Show me broken signs. I add meta charset=UTF-8 in HEAD tag but not working on table.

    ReplyDelete
    Replies
    1. What kind of broken signs you meant?

      Delete
    2. What kind of broken signs you meant?

      Delete
  8. Hi,
    Thank you for the article. I am having a question as I want to upload multiple csv files on radio button click (i.e. one radio button -> get data from 1.csv and plot the chart, then click another radio button -> get data from 2.csv and plot the chart)

    Any help would be really appreciated.
    Thank you

    ReplyDelete
  9. it shows me "NetworkError when attempting to fetch resource"

    ReplyDelete
  10. Hi! Thank you for your great tutorial. This is really usefull for me.
    In my csv file I have a first column "location". Is it possible to only show the rows where the first row has a specific value? How could I do this?

    Thanks!

    ReplyDelete
  11. Hello,
    How to handle double quotes ("") and coma (,).. For example if my value is "Jack,Jones" I want everything between ("") to be under one column and not to break.
    Thanks

    ReplyDelete
  12. I'm getting cross platform error. Please help me

    ReplyDelete
  13. And How can i Include Filters in this process

    ReplyDelete
  14. we are getting following error on using this code :
    Access to XMLHttpRequest at 'file:///C:/Users/shelja.sehdev/Desktop/piechart/cca_data.csv' from origin 'null' has been blocked by CORS policy: Cross origin requests are only supported for protocol schemes: http, data, chrome, chrome-extension, https.

    ReplyDelete
  15. It is very helpful for me Thanks!

    ReplyDelete
  16. what is the reason to write the script in after completing html tag?

    ReplyDelete
  17. I need to load vehicle fitment data, into a table just as you do with your data exemple.

    But I have an array something like this: epid array [87538735,98739700,91486986,98798723,]

    These numbers, epids are in the file in the first column;
    Something like this:
    ePID,Year,Make,Model,Trim,Engine
    87538735,2019,Ford,Escort,SE,2.4L GAS
    98739700,2019,Ford,Escort,L,2.2L GAS
    91486986,2018,Ford,Escort,SE,2.2L GAS
    so I only want to load the AJAX response into the HTML if ePID is in the CSV

    Questions:
    1. How do I modify the code of yours so I only load the data if there is corresponding ePID in the file?
    2.Also I will need to filter it and then the sort the rows, based on the 2nd column values.

    Your code in action here I only load the first 20 rows, because mu fitment.csv has over 200,00 rows...: http://tmjmark.com
    Please help me. Thanks

    Erno

    ReplyDelete
  18. Does this need to be done on a webserver of some kind, because I copied the HTML and CSV above, exactly. Put them both in the same folder, fired up the html, clicked the LOAD DATA button and not a damn thing happened.

    ReplyDelete
  19. hi, if i were to do multiple buttons with different csv files what would i need to change/add

    ReplyDelete
  20. You have added a static file in ajax url.Can you please write code of dynamic file url in ajax to import csv file within html table using jquery.

    ReplyDelete
  21. Hello,
    sorry but it doesn't work for me, when i click on the load data button nothing happens. I would like to know what to do

    ReplyDelete
  22. error while clicking on button

    ReplyDelete
  23. This is the tutorial that I was looking for, calling CSV files into web pages. thanks buddy http://18.182.187.26/

    ReplyDelete
  24. Page loads fine with button but clicking button produces nothing. Not sure why. Copied and pasted the code exactly, just changed the csv file name and the name of the index.html file.

    ReplyDelete
  25. To those that are wondering why it won't work.

    make sure the url is locating your file in the same directory as your index page. If it's in the folder you will need to specify the path of it "FolderName/xxx.csv"

    ReplyDelete
  26. It no longer works in Mozilla Firefox... the table does not 'load'. It's not also working in Google Chrome, Microsoft Edge, and Internet Explorer.

    Please help...

    ReplyDelete
  27. Works in Firefox but not in Chrome.

    ReplyDelete
  28. Hi, What if I want to add a cloumn to the table which is not present in excel sheet.

    Please Help

    ReplyDelete
  29. hello, i want to ask how to doing this but file from form upload not url default csv. thank you

    ReplyDelete
  30. I can't load special character like "Ñ"

    ReplyDelete
  31. i am not able to load anything

    ReplyDelete
  32. hello i want to as how could i convert this to automtically update the table every second thanks in adevance

    ReplyDelete