Opensource CSV to JSON Tool For Testing APIs and its Complexity in JavaScript — Source Code Attached!
This opensource tool can make life easier for testing the APIs with the sufficient datasets and its complexity 😄
The primary reason why I created this CSV to JSON tool is to frequently convert and push the datasets from the Kaggle to the Relational Databases and Non-Relational Databases for testing the complexities of the APIs and how efficient the DB(s) are responding to the request with different size of the datasets.
You can quickly interact with the application using the codepen.io, please feel free to click here => https://codepen.io/Som23/pen/poOjRYq, and drag & drop a CSV/XLS/XLSX file and Download JSON .
For now, this tool will help you to convert three formats of the datasets to the JSON.
[x] CSV
[x] XLSX
[x] XLS
This conversion of the raw datasets to JSON will allow you to test and improve the load shredding of the APIs and its associated applications.
# Prerequisites for Beginners:
- Install the
sheetJSnpm package
npm i sheetjs- Install the
xlsxnpm package for standalone testing
npm i xlsx# Core Layer
Let’s understand the core of the application:
# excel-to-json-v1.js
// Load the library
const XLSX = require('xlsx');
const fs = require('fs');
// Load the file
const workbook = XLSX.readFile('netflix_sample_dataset.xlsx');
// Choose the sheet to convert
const sheetName = 'Sheet1';
const worksheet = workbook.Sheets[sheetName];
// Convert the sheet data to JSON
const jsonData = XLSX.utils.sheet_to_json(worksheet);
// Output the JSON to the console
console.log(jsonData);
// Write the JSON data to a file
fs.writeFileSync('output.json', JSON.stringify(jsonData, null, 2));
console.log('JSON data written to output.json');In the above code, I’m specifying the required libraries and the file system to give access to the code to create a new output.json file, post converting the CSV data.
This is the easiest way to convert the XLSX file to the JSON format. If you have understood the concept then, the other half will be a piece of cake.
# Outer Layer
As I wanted to promote or use this as a tool and wanted to be more user-friendly instead of specifying/modifying the code, have created the outer layer, i.e. nothing but the HTML/CSS Code along with the Javascript functions.
# Basic HTML with referencing the CSS and JS
#index.html
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>Excel to JSON Converter</title>
<link rel="stylesheet" href="style.css">
<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.17.1/xlsx.full.min.js"></script>
</head>
<body>
<div class="drop-zone" id="dropZone">
<p>Drag and drop your CSV/XLS/XLSX file here</p>
</div>
<div id="downloadZone"></div>
<script src="excel-to-json-v2.js"></script>
</body>
</html>If you take a closer look at the HTML code, we have specified the XLSX package as a <script></script> instead of specifying in the JS code because as it is a web-application/tool, the require method will not be supported.
# CSS pointing to the id’s of the HTML elements
#style.css
body {
font-family: sans-serif;
}
.drop-zone {
display: flex;
justify-content: center;
align-items: center;
width: 400px;
height: 200px;
border: 2px dashed #ccc;
border-radius: 10px;
margin: 0 auto;
}
.drop-zone.active {
border-color: #3c78d8;
}
.drop-zone p {
margin: 0;
font-size: 18px;
font-weight: bold;
color: #999;
}
.download-link {
display: block;
margin-top: 20px;
font-size: 18px;
color: #ffffff;
text-decoration: none;
text-align: center;
}# JavaScript Does The Heavy Load
All that said, the major changes are in the Javascript where we have added the function(s) and the relevant event listeners to carry out application program in a web-browser.
# excel-to-json-v2.js
// Get the drop zone element from html code
const dropZone = document.getElementById('dropZone');
// Add event listeners
dropZone.addEventListener('dragover', handleDragOver);
dropZone.addEventListener('dragleave', handleDragLeave);
dropZone.addEventListener('drop', handleDrop);
// Define event handlers
function handleDragOver(event) {
event.preventDefault();
event.stopPropagation();
dropZone.classList.add('active');
};
function handleDragLeave(event) {
event.preventDefault();
event.stopPropagation();
dropZone.classList.remove('active');
};
function handleDrop(event) {
// Prevent default behavior (opening file in browser)
event.preventDefault();
// Get the file from the event
const file = event.dataTransfer.files[0];
// Check if the file is a valid XLS(X) or CSV file
if (/(\.xls|\.xlsx|\.csv)$/i.test(file.name)) {
// Read the file as a binary string
const reader = new FileReader();
reader.onload = function(event) {
console.log(`file added successfully ${file}`)
handleReaderLoad(event, file);
};
reader.readAsBinaryString(file);
} else {
alert('Please drop a valid Excel or CSV file.');
};
};
function handleReaderLoad(event, file) {
// Get the file contents as a binary string
const data = event.target.result;
// Convert the file contents to JSON format
const workbook = XLSX.read(data, { type: 'binary' });
const worksheet = workbook.Sheets[workbook.SheetNames[0]];
const jsonData = XLSX.utils.sheet_to_json(worksheet);
// Create a downloadable JSON file
const jsonBlob = new Blob([JSON.stringify(jsonData, null, 2)], { type: 'application/json' });
const jsonUrl = URL.createObjectURL(jsonBlob);
// Update the drop zone message to show the file name and the download link
const dropZone = document.getElementById('dropZone');
dropZone.innerHTML = `File '${file.name}' has been converted to JSON. `;
if (file) {
const link = document.createElement('a');
link.style.background = '#3c78d8';
link.innerText = "Download JSON";
link.href = jsonUrl;
link.download = file.name.replace(/\.(xls|xlsx|csv)$/i, '.json');
link.textContent = 'Download JSON';
link.className = 'download-link';
dropZone.appendChild(link);
};
};Voila, we have successfully created a web-application that converts CSV/XLSX/XLS to JSON in seconds.
*Limitations — I have not yet made the tool efficient for handling bigger files more than 10MB, but it works great for the files that is below the 10MB limit. I would say please feel free to test it out with the bigger files and let me know how it goes!! I’m very much eager to see the performance metrics*
# Useful Links
- Kaggle Netflix Datasets
- XLSX Package and its details
- SheetJS Package and its details
- Basics of FileReader
- FileReader Example With Image Uploader Application
Thanks for reading!! If you enjoyed this article, please follow and subscribe for the latest updates. Looking for more? Check out the other posts below:
