Do you need to get data from a spreadsheet and turn it into a JSON ? That's not π science and I'm gonna prove it !
First, install the xlsx package.
With npm :
// NPM
npm install xlsx
// Yarn
yarn add xlsx
In the app.js file, import xlsx and fs to read the excel file, and declare a variable that we will use later.
const XLSX = require('xlsx');
const fs = require('fs');
const finalObject = {};
To read and get the content of the file with the Buffer type :
const data = XLSX.read(myFile, { type: 'buffer' });
N.B: The different types are "string", "buffer", "base64", "binary", "file", "array"
If you write console.log(data.Sheet) you will see your spreadsheets and the cells content.
Then you have to write the process for each row of each spreadsheet.
data.SheetNames.forEach(sheetName => {
let rowObject = XLSX.utils.sheet_to_json(data.Sheets[sheetName]);
finalObject[sheetName] = rowObject;
});
The sheet_to_json function allows to convert a spreadsheet into an array of objects.
It takes differents optionnal parameters that you can find here
Here we won't need anything
If you do a console.log(rowObject), you will see that it contains an array, and each row of the spreadsheet is turned into an object like so :
[
{ "ID": 1, "Last name": "Doe", "First name": "John" },
{ "ID": 2, "Last Name": "Doe", "First name": "Jane" }
]
Do you remember the variable we declared at the beginning ? It is time to use it. We are going to create a key for each spreadsheet and assign rowObject to it :
data.SheetNames.forEach(sheetName => {
let rowObject = XLSX.utils.sheet_to_json(data.Sheets[sheetName]);
finalObject[sheetName] = rowObject;
});
If you console.log(finalObject) :
"Utilisateurs": [
{ "ID": 1, "Last name": "Doe", "First name": "John" },
{ "ID": 2, "Last name": "Doe", "First name": "Jane" }
]
If you want to write the output into a file, simply add this line :
fs.writeFileSync('./target.json', JSON.stringify(dataObject));
VoilΓ π
Now you know how to convert an Excel spreadsheet into JSON !
Originally posted on my blog. Check out my instagram account to learn more about web development.
Top comments (7)
You could do it in two steps in Python.
Pandas to json is very powerful and can do a lot of customizations. Check it out!
pandas.pydata.org/docs/reference/a...
Hi ! Thank you for your feedback. That's really useful !
I'm assuming your choice of type: "buffer" was due to the fact that it won't load all in memory at once?
It would be nice to be able to write the JSON without having to store the entire file in RAM as well. Because in multi-user applications that would end creating a scalability issue.
Hi ! Thank you for your feedback !
When I wrote that article, I needed a method to convert one file once in a while and I didn't think about scalability.
But you're right, this method can certainly be improved greatly !
I will definitly look into that π€
thanks buddy, much useful one. thanks a lot
Thank you ! Glad I could help !
Thank you for your feedback it's really interesting ! I'll definitely try that out !