CSV & Excel to JSON in JavaScript

Quick Intro to Ajax & Fetch

There is a couple of JavaScript plugins that can be used to read and process CSV and Excel files as well as just hard coding your own script to do so.

CSVstands for : Comma Separated Values

It is a file format used to store tabular data, such as a spreadsheet or database. Files in the CSV format can be imported to and exported from programs that store data in tables, such as Microsoft Excel or OpenOffice Calc way of exchanging data between applications and data stores.

JSON:JavaScript Object Notation

Screenshot of my project folder and files

In this tutorial, we’ll explore and demonstrate how to parse data stored in CSV format and an excel file into JSON format and display data to HTML using j Query-CSV and SheetJS js-xlsx JavaScript libraries.

We’ll also explore how to make Ajax call and Fetch request.

read and process a CSV file

Tip: Optimize teamwork by using the right tools for code-sharing

Use Bit to share, install and collaborate on individual JS modules and UI components. Stop wasting time configuring packages, managing multiple repositories or maintaining cumbersome monorepos.

Components with Bit: Easily share across projects as a team

Let’s go ahead and add the jQuery-CSV library dependency in the “head tag” of my index.html file:

<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jquery-csv/1.0.3/jquery.csv.min.js"></script>

As you saw above, I have this sample CSV file inside in my project folder:

Note: On the very top of my script, I have declared API variables that are the link the CSV and Excel files inside my project folder:

var csv_file_API = './UsersSample.csv';var excel_file_API = './soccer_players.xlsx';

Using Ajax

“Ajax is a technique to send and retrieve information behind the scenes without needing to refresh the page. It allows browsers to send and retrieve information, then does things with what it gets back, like add or change HTML on the page.”

Whenever we send or retrieve information with JavaScript, we initiate a thing known as an Ajax call. We could do Ajax without jQuery, but we had to write an XMLHttpRequest; Which is a bit more complex and complicated . Initiating an Ajax call through “jQuery’s Ajax method” is one the easiest way available. source: css-tricks.com

Now inside my script.js file, I’m making an Ajax call to read the CSV file, convert the data result to JSON, and display it in a list on the HTML page. Here the piece of code that makes the call and display data using Jquery append method:

read and process Excel file

We are going to use SheetJS js-xlsx ( Spreadsheets simplified ) to read and process the excel file. This JavaScript plugin works in web browsers and servers. It supports every Excel file format. So Let’s go ahead and add the XLSX library dependency from its content delivery network js (CDNjs) in the “head tag” of my index.html file:

<script type="text/javascript" lang="javascript"src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.15.1/xlsx.full.min.js"></script>

Using Fetch

For learning purpose; let’s explore another method to Ajax. In the last recent years, browsers have improved so much (except Internet Explorer to my opinion). Most of those browsers now support the Fetch API , which is a modern way to Ajax without helper libraries like jQuery or Axios .

The Fetch API provides a JavaScript interface for accessing and manipulating parts of the HTTP pipeline, such as requests and responses. It also provides a global fetch() method that provides an easy, logical way to fetch resources asynchronously across the network. Fetch also provides a single logical place to define other HTTP-related concepts such as CORS and extensions to HTTP.

So for the second part of this tutorial we are going to use fetch to read the excel file and convert the data result to JSON. Here the piece of code that does the fetch request file in my script.js and display data using jQuery append method:

Just make this fun I’m suing Material Design Bootstrap for styling and data display. I have also added additional style to make my page a bit more stylish. Feel free to style your page as you like. This is my HTML page:

My demo page Screenshot

At this point, if you still have issues getting your data displayed and would live to review my files. Feel to check out my GitHub demo repository .