Read Excel Files and convert to JSON in Node.js

June 5, 2016
by Rahil Shaikh

Sometimes in an application, you may have a requirement to upload data via an excel file or a job to read data from a bunch of excel files or may be run some analytics. Dealing with excel files is common in web applications. This tutorial aims to make reading excel files using NodeJS easier for developers.

After this tutorial, you will be able to upload an excel file on your application to a particular path, read the file and convert it into json data and then, obviously you can do whatever you want with that programmatically sensible data.

We will be taking care of two formats of excel files. That is converting ‘.xls’ to json and ‘.xlsx’ to json

Roadmap

Here we will do the following things.

  1. Setup Node express server and routes
  2. Upload file, using Multer
  3. Read and convert excel to json

Note: The below section deals with uploading of the files further, if not required you can directly skip to Read and convert excel to json further below.

DOWNLOAD

Setting Up Express server and Multer for upload

We have already done a tutorial on File Upload, which involves setting up node-express along with multer for upload, where we have explained in detail each step. For this tutorial instead of repeating the same thing we will be picking up parts of code from there. Please give a read to the below-mentioned tutorial for a detailed explanation.

File Upload with NodeJS and AngularJS

Navigate into your working directory via your console and run the following commands.

npm init --yes
npm install express --save
npm install multer --save
npm install body-parser --save
mkdir uploads
touch app.js

Now I will be picking up the code from the file upload tutorial and adding it to my app.js. At the same time, we will be removing the unwanted code blocks.

app.js
    var express = require('express');
    var app = express();
    var bodyParser = require('body-parser');
    var multer = require('multer');
    app.use(bodyParser.json());
    var storage = multer.diskStorage({ //multers disk storage settings
        destination: function (req, file, cb) {
            cb(null, './uploads/')
        },
        filename: function (req, file, cb) {
            var datetimestamp = Date.now();
            cb(null, file.fieldname + '-' + datetimestamp + '.' + file.originalname.split('.')[file.originalname.split('.').length -1])
        }
    });
    var upload = multer({ //multer settings
                    storage: storage
                }).single('file');
    /** API path that will upload the files */
    app.post('/upload', function(req, res) {
        upload(req,res,function(err){
            if(err){
                 res.json({error_code:1,err_desc:err});
                 return;
            }
             res.json({error_code:0,err_desc:null});
        });
    });
    app.get('/',function(req,res){
    res.sendFile(__dirname + "/index.html");
    });
    app.listen('3000', function(){
        console.log('running on 3000...');
    });

Just a quick explanation of the above code, firstly we are requiring the different modules, we will need the body-parser to parse the post data. Next, we are setting up the multer’s storage settings, for the storage destination, uploaded file name and also the storage type as disk storage. Below it, are the settings for multer and further, we have added an express route/path where we will be uploading the file.
We also have a simple index.html file rendered at the root path. index.html is nothing but a form where we will be able to upload our files. Finally, we are listening to port 3000 to start a node server.

index.html
<form id        =  "uploadForm"
    enctype   =  "multipart/form-data"
    action    =  "upload"
    method    =  "post"
>
<input type="file" name="file" />Upload
<input type="submit" value="Upload" name="submit">
</form>

Now if you run your app, you will be able to upload any file into our uploads directory. But we need to restrict our app to only allow excel files i.e (.xls, .xlsx)

Adding filetype filter

We will change our upload function to only allow excel file by adding a fileFilter option provided by Multer.

app.js
  var upload = multer({ //multer settings
                    storage: storage,
                    fileFilter : function(req, file, callback) { //file filter
                        if (['xls', 'xlsx'].indexOf(file.originalname.split('.')[file.originalname.split('.').length-1]) === -1) {
                            return callback(new Error('Wrong extension type'));
                        }
                        callback(null, true);
                    }
                }).single('file');

 

Read and convert excel to json

Now, that we are able to upload files on our server and that we have added the extension validation. The next challenge is to read the uploaded file and convert it to json.

To do this we will make use of two node modules, xls-to-json-lc and xlsx-to-json-lc for converting .xls to json and .xlsx to json respectively.

Install them by running the below commands.

npm install xlsx-to-json-lc --save
npm install xls-to-json-lc --save

 

Usage

  var exceltojson = require("xls-to-json-lc");
  exceltojson({
    input: "pass the input excel file here (.xls format)"
    output: "if you want output to be stored in a file"
    sheet: "sheetname",  // specific sheetname inside excel file (if you have multiple sheets)
    lowerCaseHeaders:true //to convert all excel headers to lowr case in json
  }, function(err, result) {
    if(err) {
      console.error(err);
    } else {
      console.log(result);
      //result will contain the overted json data
    }
  });

 

Implementation

Remember we have two different node modules for the two excel extensions. We will first check the extension of the incoming file and then depending on it, we will use the right module. Let us get started.

Start by requiring our modules into our app.js.

app.js
var xlstojson = require("xls-to-json-lc");
var xlsxtojson = require("xlsx-to-json-lc");

Let’s make changes to our /upload path.

app.js (/uploads path)
/** API path that will upload the files */
    app.post('/upload', function(req, res) {
        var exceltojson; //Initialization
        upload(req,res,function(err){
            if(err){
                 res.json({error_code:1,err_desc:err});
                 return;
            }
            /** Multer gives us file info in req.file object */
            if(!req.file){
                res.json({error_code:1,err_desc:"No file passed"});
                return;
            }
            //start convert process
            /** Check the extension of the incoming file and
             *  use the appropriate module
             */

            if(req.file.originalname.split('.')[req.file.originalname.split('.').length-1] === 'xlsx'){
                exceltojson = xlsxtojson;
            } else {
                exceltojson = xlstojson;
            }
            try {
                exceltojson({
                    input: req.file.path, //the same path where we uploaded our file
                    output: null, //since we don't need output.json
                    lowerCaseHeaders:true
                }, function(err,result){
                    if(err) {
                        return res.json({error_code:1,err_desc:err, data: null});
                    }
                    res.json({error_code:0,err_desc:null, data: result});
                });
            } catch (e){
                res.json({error_code:1,err_desc:"Corupted excel file"});
            }
        });
    });

 
We will get our converted data in the results variable (i.e the second parameter). Thats it, now we are ready to run and try our app.

Our final files look like this.

app.js
    var express = require('express');
    var app = express();
    var bodyParser = require('body-parser');
    var multer = require('multer');
    var xlstojson = require("xls-to-json-lc");
    var xlsxtojson = require("xlsx-to-json-lc");
    app.use(bodyParser.json());
    var storage = multer.diskStorage({ //multers disk storage settings
        destination: function (req, file, cb) {
            cb(null, './uploads/')
        },
        filename: function (req, file, cb) {
            var datetimestamp = Date.now();
            cb(null, file.fieldname + '-' + datetimestamp + '.' + file.originalname.split('.')[file.originalname.split('.').length -1])
        }
    });
    var upload = multer({ //multer settings
                    storage: storage,
                    fileFilter : function(req, file, callback) { //file filter
                        if (['xls', 'xlsx'].indexOf(file.originalname.split('.')[file.originalname.split('.').length-1]) === -1) {
                            return callback(new Error('Wrong extension type'));
                        }
                        callback(null, true);
                    }
                }).single('file');
    /** API path that will upload the files */
    app.post('/upload', function(req, res) {
        var exceltojson;
        upload(req,res,function(err){
            if(err){
                 res.json({error_code:1,err_desc:err});
                 return;
            }
            /** Multer gives us file info in req.file object */
            if(!req.file){
                res.json({error_code:1,err_desc:"No file passed"});
                return;
            }
            /** Check the extension of the incoming file and
             *  use the appropriate module
             */

            if(req.file.originalname.split('.')[req.file.originalname.split('.').length-1] === 'xlsx'){
                exceltojson = xlsxtojson;
            } else {
                exceltojson = xlstojson;
            }
            try {
                exceltojson({
                    input: req.file.path,
                    output: null, //since we don't need output.json
                    lowerCaseHeaders:true
                }, function(err,result){
                    if(err) {
                        return res.json({error_code:1,err_desc:err, data: null});
                    }
                    res.json({error_code:0,err_desc:null, data: result});
                });
            } catch (e){
                res.json({error_code:1,err_desc:"Corupted excel file"});
            }
        })
    });
    app.get('/',function(req,res){
        res.sendFile(__dirname + "/index.html");
    });
    app.listen('3000', function(){
        console.log('running on 3000...');
    });
package.json
{
  "name": "excelupload",
  "version": "1.0.0",
  "description": "",
  "main": "app.js",
  "scripts": {
    "test": "echo "Error: no test specified" &amp;&amp; exit 1"
  },
  "keywords": [],
  "author": "Rahil Shaikh",
  "license": "ISC",
  "dependencies": {
    "body-parser": "1.15.1",
    "express": "4.13.4",
    "multer": "1.1.0",
    "xls-to-json-lc": "0.3.4",
    "xlsx-to-json-lc": "0.4.1"
  }
}

 

Update: Versions of the given packages are updated into package.json ([email protected] and [email protected]). This involves fix to dependencies being depreciated in the original packages.

Run the app

To run the app just navigate into your woking directory using command line tool and run.

node app.js

 

Demo and Quick setup

 excel to json demo

excel to json demo

To quickly run the application follow the bellow steps

1) git clone https://github.com/rahil471/excel-to-json-in-Node.js.git
2) cd excel-to-json-in-Node.js
3) npm install
4) node app.js
5) In your browser http://localhost:3000
6) Upload excel file and see result

 

Deleting the uploaded file

In some cases, you may not want to keep the uploaded excel file on the server. Here is a piece of code for deleting the uploaded files as soon as they are converted.

package.json
var fs = require('fs');
try {
    fs.unlinkSync(req.file.path);
} catch(e) {
    //error deleting the file
}

Use this code just below the excel to json conversion. req.file.path would refer to the currently uploaded file.

Conclusion

Excel to json is a regular requirement for web applications. This tutorial explains how easy it is to accomplish in Node.js

About

Engineer. Blogger. Thinker. Loves programming and working with emerging tech. We can also talk on Football, Gaming, World Politics, Monetary Systems.

Free PDF

Subscribe and get AngularJS Tips PDF. We never spam!
First Name:
Email:

31 comments

  1. Abdulaziz
    |

    Good day. I am new in NodeJS world. Could anuone recommend me any web sites, videocourse or books.

    • |

      Hi Abdulaziz,
      CipherTrick has a good collection of NodeJS tutorials, you can browse them on this website. We also have a YouTube Channel where you can find excellent video tutorials, make sure you subscribe to us. 🙂
      Other than that CodeForGeek is a good blog focused mainly on Node.js, you can check that out as well.

  2. Ian Poston
    |

    I am using angular on the front-end to upload a xlsx file. I am trying to integrate your converter code but it’s not working properly. My goal is to upload a xlsx file and parse to json data so I can show the data in a view. I have been able to save the file in my upload directory in my root but it doesn’t show json and I also get error messages sometimes. My form is different than the one you used and I am using a 3rd party module to help with uploading. I have one line in server.js that seems to be throwing error var type = upload.array(‘files[]’); then it keeps saying upload is not a function. I put my controller and server.js in a jsbin file here http://jsbin.com/weniminigo/edit?js if you have time to look at it. If you can give me any suggestions it would be a great help Thank you!

    • |

      Hi at a quick glance, I think your uploads.array seems to be invalid.
      Should be of the form.

      uploads.array('selector', limit)
      uploads.array('photos', 2)

      And your form filed name should match with the selector

       <input type="file" name="photos" multiple />

      Try logging req.files it should be an array of photo file objects

  3. Prasad VS
    |

    Thanks for the wonderful tutorial. I am new to this and learning. my req is to send the json output to a database and return to the home screen with a success message ( an alert would do) . I was able to send the result (json) to a mongodb collection. However, I wasn’t able to stop the json output showing up on the sceen, I commented the below line and did a plain ‘return’, but that doesn’t work. Can you pls guide me on how to do that?
    res.json({error_code:0,err_desc:null, data: result});

    • |

      Hi Prasad,
      I will have to look into your code to answer your question.
      Probably there would be some minor thing that you are missing, you can create a plkr or mail me your code.

  4. Aaron
    |

    Rahil, fantastic job with this, you helped me get over a major hurdle I’d been stuck on for too long. Again, well done.

    • |

      The download link is provided in the article.

  5. |

    i want read properties of a uploaded excle file like author name,when it is modified, bcoz i want to check wheather uploaded xls(or)xlsx file are my own formated file are not, if it is not my formated file i want to intimate to end user

    • |

      The properties are returned as json object, where the headers will be properties!

  6. |

    how to read headen fields data of a xls(or)xlsx file

  7. Gopi Malla
    |

    Hey.. Great articel!! By the way, How to extend this to support multi sheet reading in the same excel file?

    • |

      There is an option that accepts sheet name, by default it reads the 1st sheet.

  8. mohg
    |

    Hi.. I want to upload a file and convert it to json or array without saving that uploaded file in any temp directory. can you help me with the sample code on this one

    • |

      Check-out the Multer memoryStorage option.

      • mohg
        |

        thank you..i will try this. before that, i tried the code which you posted here. Its returning empty result.. i git cloned your code and ran the app.

        • mohg
          |

          your code is working perfectly fine.. discard my above comments

  9. |

    Hi, Thanks for the great example, but the try block throws “code: LIMIT UNEXPECTED FILE”, “field: contactsFile”
    try {
    exceltojson({
    input: req.file.path,
    output: null, //since we don’t need output.json
    lowerCaseHeaders:true
    }, function(err,result){
    if(err) {
    return res.json({error_code:1,err_desc:err, data: null});
    }
    res.json({error_code:0,err_desc:null, data: result});
    });
    } catch (e){

    res.json({error_code:1,err_desc:”Corupted excel file”});
    }

    Is there anything wrong that I’m doing, because I have just copied and pasted your code??..But What I have picked up again is that, it only throws that error when I’m uploading any 97 excel file. Please assist.
    Thanks..

  10. |

    Hi Rahil,
    This example is really helpful….but i am getting

    {“error_code”:1,”err_desc”:”Corrupted excel file”}

    • |

      That may happen if you have saved some other format as .xls or .xlsx

  11. |

    Hi, Am using this module for converting xls file to json format. I have newly created xls file. But when I uploaded this file, its returning corrupted file even though its a valid xls file.

    • |

      Can you send me a sample of your excel file? I’ll try at my end.

  12. |

    Update: Versions for the given packages are updated into package.json ([email protected] [email protected]). This involves fix to dependencies beign depriciated in th original packages.

  13. |

    Update: Versions of the given packages are updated into package.json ([email protected] and [email protected]). This involves fix to dependencies being depreciated in the original packages.

  14. |

    Hi,

    How to save the converted data to the disk? I am using webpack with node

    Regards
    HSA Shankar

  15. |

    Hey !
    Your tutorials were amazing !
    I am a beginner in node.js, and i am currently working on a projectwhich involves the use of highcharts.
    I have made a server using node.js and successfully loaded a csv file onto the server.
    I need to plot graphs in highcharts using the data present in the csv file , and i am stuck with this step.
    Could you help em out with this ?
    Regards
    Tushar Upadhyay

  16. |

    Hi,
    This is how my output is shown in postman, after running your code. Please help me out
    {
    “error_code”: 1,
    “err_desc”: “No file passed”
    }

    • |

      Probably because of improper headers. Why don’t you use the interface provided by the server?

  17. |

    this code create json as rows are blank.
    how to stop blank row conversion or stop iteration as blank row found.

Leave a Comment