Easily parse an excel spreadsheet into JSON

Did you know that you can easily parse an excel spreadsheet using Node.js? Keep reading to speed up your information retrieval processes.

Why do I need this?

I learned this a few months ago and came back to using it ever since. I've found that there are several use cases in my daily life that can profit from this little script - in my job as well as in my private life or side projects. You can use it for nearly every scenario where you need to transform your spreadsheet into JSON.

Here are some examples:

• your working with a client and need an easy way for them to maintain the data your using for the project, most people are familiar with working with excel
• your collaborating with a few people and want a safe place for everybody to collect data
• you like planning your life in excel but want to process the data further - Maybe you want to create a beautiful website about your carefully planned road trip
• and many more, get creative!

How do I use it?

You only need two Node.js modules for this: xlsx-stream-reader for parsing the data and fs for saving the created JSON file.

xlsx-stream-reader parses each row of your spreadsheet and lets you process the data however you want. I usually save my data in an array, where each row represents one element:

Code example of the parsing process

let currentRow = {};

// iterating the rows 
row.values.forEach(function (rowVal, colNum) {

    if (rowVal && rowVal !== "") {
        // parsing all columns 
        if (colNum == "1") {

            currentRow.id = rowVal; // ID
        } else if (colNum == "2") {

            currentRow.name = rowVal; // name
        } else if (colNum == "3") {

            currentRow.img = rowVal; // img
        } 
    }
});

if (currentRow.name) {

    console.log(currentRow);

    // push the current row into your array
    dataInRows.push(currentRow);
}

The stream reader also emits events, e.g. when it reaches the end of the spreadsheet or an error occurs.

Overall the script is pretty lightweight and short. The longest part is usually the one where you define the rows (see above).

Your input

I hope you could learn something new from this little post. Feel free to add your thoughts or questions in the comments below! :)

Comments

[wald-tq]

you can simplify the code like this and get rid of the loop and ifs:

let currentRow = {id: row.values[0], name: row.values[1], img: row.values[2]}

[Nena]

You're right, thanks! I can't believe I didn't think of that before. :D

[Ilê Caian]

Awesome post @nena !

Just some notes:
1 - If you use the syntax highlight at your code block it makes it more readable! For JavaScript, you just need to place javascript after your opening 'triple `'

2 - You should avoid using == and prefer ===. I wrote an article here about it 😄 : dev.to/caiangums/the-js-equality-c...

3 - You could test just for rowVal, because the empty string counts as false on boolean checks


if (!rowVal) {
// code
}


Another point is that if you know that rowVal will always be a String, you could check for empty string instead:


if (rowVal.length > 0) {
// code
}


4 - For you JS code, instead of using multiple else if's you could use switch-case:


// switch-case
const COLUMN = {
ID: 1,
NAME: 2,
IMG: 3,
// and so on...
};
switch(colNum) {
case COLUMN.ID:
currentRow.id = rowVal;
break;
case COLUMN.NAME:
currentRow.name = rowVal;
break;
case COLUMN.IMG:
currentRow.img = rowVal;
break;
default:
break;
}


5 - For this specific case, you could use Array.reduce() instead of Array.forEach(). The Array.reduce() is always a good option to be used when you want to convert a List into another type of object (String, Number, Object...)

Edit: Apparently, the "code block" at responses doesn't work properly... 😅

[Nena]

Wow, thanks for your great feedback!

And a special thanks for telling me how to highlight the code block. This bugged me as well but since it was my first post I didn't know how to highlight it yet. So thanks for enlightening me, I just edited it! :)

Regarding 4: I used to love switch-case but when I started coding in JavaScript I often heard that it's not as performant as if-else unless you have a looooot of elses. Do you know more about that?

As for the rest: Thanks for pointing it out, it's mostly stuff I definitely know but most of the times just forget about or are too lazy to be consistent with. 😅

[Ademir Terin Junior]

I made a tool thats dynamicly convert Google's Spreadsheet JSON endpoint to a worthily readable (not judging the original complexity but common guys, know you looking for a free BaaS solution for your garage project, u have no time and money to spend spreadsheet-json-beaut.glitch.me/

[Andrew Baisden]

Nice post don't forget the code block syntax highlighting it makes it more readable 😉

[Nena]

Yes, this bugged me as well. :D It was my first post, so I didn't know how to do it yet. But I just learned how and edited it! :)

[Ben Holmes]

Good stuff! Thanks for presenting the need for it at the top of the post as well. My gears are turning on replacing crazy CMS setups with some straight Google sheets 😛

[Nena]

Haha, glad I was able to inspire you! :D

[Ampla Network]

Excellent !!

[Nena]

Thanks for your feedback!