Read Excel Files and convert to JSON in Node.js
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 maybe 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. In short, we will learn how to convert excel to JSON in Node.js.
After this Node.js 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.
- Setup Node express server and routes
- Upload file, using Multer
- 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.
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 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.
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.
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.
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 xls-to-json-lc --save
Usage
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.
var xlsxtojson = require("xlsx-to-json-lc");
Let’s make changes to our /upload path.
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.
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...');
});
"name": "excelupload",
"version": "1.0.0",
"description": "",
"main": "app.js",
"scripts": {
"test": "echo "Error: no test specified" && 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.5.0"
}
}
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 working directory using the command line tool and run.
Demo and Quick setup
excel to json demo
To quickly run the application follow the below steps
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.
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 excel to JSON in Node.js
Keep Learning…
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.
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.
And your form filed name should match with the selector
Try logging req.files it should be an array of photo file objects
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.
Rahil, fantastic job with this, you helped me get over a major hurdle I’d been stuck on for too long. Again, well done.
Thanks for stopping by, Aaron.
send the download code in node.js
The download link is provided in the article.
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!
how to read headen fields data of a xls(or)xlsx file
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.
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.
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.
your code is working perfectly fine.. discard my above comments
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..
Why my question is it not answered??
Maybe oversight. I hope by now, your issue has been resolved.
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
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.
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.
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.
Hi,
How to save the converted data to the disk? I am using webpack with node
Regards
HSA Shankar
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
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?
this code create json as rows are blank.
how to stop blank row conversion or stop iteration as blank row found.
How to save that converted excel datas to mysql db with sequelize ORM?
Hi, the code works fine for me . But how do u insert the uloaded excel data into mysql db
Hi Rahil,
i have tried this solution am getting issue while installing module xlsx-to-json-lc.
I have tried to execute the command npm install -g [email protected] and also tried npm install -g xlsx-to-json-lc still am getting same error
npm ERR! argv "C:\\Program Files\\nodejs\\node.exe" "C:\\Program Files\\nodejs\\
node_modules\\npm\\bin\\npm-cli.js" "install" "-g" "[email protected]"
npm ERR! node v6.9.4
npm ERR! npm v3.10.10
npm ERR! code ETARGET
npm ERR! notarget No compatible version found: [email protected]
npm ERR! notarget Valid install targets:
npm ERR! notarget 0.11.3, 0.11.2, 0.11.1, 0.11.0, 0.10.9, 0.10.8, 0.10.7, 0.10.6
, 0.10.5, 0.10.3, 0.10.1, 0.10.0, 0.9.13, 0.9.12, 0.9.11, 0.9.10, 0.9.9, 0.9.8,
0.9.6, 0.9.4, 0.9.3, 0.9.2, 0.9.0, 0.8.8, 0.8.0, 0.7.12, 0.7.0, 0.6.1, 0.6.0, 0.
5.17, 0.5.8, 0.5.7, 0.5.6, 0.5.0, 0.4.3, 0.3.10, 0.3.3
npm ERR! notarget
npm ERR! notarget This is most likely not a problem with npm itself.
npm ERR! notarget In most cases you or one of your dependencies are requesting
npm ERR! notarget a package version that doesn't exist.
npm ERR! notarget
npm ERR! notarget It was specified as a dependency of 'xlsx-to-json-lc'
npm ERR! notarget
npm ERR! Please include the following file with any support request:
npm ERR! C:\New Node JS Workspace\npm-debug.log
Could you pls let me how can i resolve the issue
Thank you
[email protected].4 is not available, I’ll have to republish the package with updated dependency.
Same here!
Try clearing the cache and reinstall v 0.5.0 again.
Updated! Upgrade version to 0.5.0, see package.json
i have tried to install [email protected] ,am still facing the same problem
Try clearing the cache and reinstall v 0.5.0 again.
The [email protected] package seems to be working now.
I have noticed one thing that in the output, it brings the empty rows of the excel rows also.
For e.g. If I have only 20 records and in the excel sheet I have 50 blank rows after the 20 records, then it brings a total of 70 objects.
Ideally, it should bring only 20 records.
Any idea how to fix this?
Hi Rahil,
thank you for the great tutorial.. i have a question..
in one of my scope, i have an Array of list .. which i put in a single Cell of Excel sheet.
how do i read the single cell of Excel sheet that read something like this : [ a,b,c,d,] now this is a single Cell of excel sheet. i want to read it and put it in an Array element in my Angular object.
before reading your tutorial, i started using angular-js-xlsx npm.
thanks,
Muddassir Khan
hI,
Is there any option to convert all sheets in excel file to json???
Thanks for all details. How reliable this solution is for a environment where you expect multi requests for same conversion? as NodeJs single threaded on a core with some level of concurrency allowed?
If 10 concurrent request of big file conversion came what will happen?
Rahil, this is a superb tutorial. I spent three days trying to get dropzone and excel parsing to work in my angular app. After figuring out how to configure dropzone properly, I stumbled here. Problem solved! thank you for this. Have you done any posts about using csurf to implement csrf with node/express? I would be super interested in that.
Hi Rahil,
Thanks for the wonderful solution. However the code doesn’t skip the empty rows. Could you please provide a solution. Looks like this question was previously asked but not answered. Really appreciate if you could help us here.
Thanks,
Mounika
Your tutorial is great but if i want to create download button on html to convert json on server to our pc, how can i do that ?
Hi ,
this was very helpful.But can you tell me what is the format for the req body as i want to test the API in postman?
Well done!
Hi ,
My requirement is bit different , i have check the uploaded files is correct or not with its headers values …
i am unable to read headers using this modules xlsx-to-json-lc
can you please help me on this
Hi,
I have a few doubt regarding this case,please let me know how to print the convert excel JSON in the nodejs console , Please kindly advise me
Use console.log
Hi Rahil Shaikh,
May I know about
Up to how many rows we can read from excel
Thanks
Elumalai Govindan
If you have large excel files I would recommend dividing it int chunks of 1000 and reading them.
Hii
Your tutorial is great but if i want to create download button on html to convert json on server to our pc, how can i do that ?
I want to save the json data into a file how to do it. Please send me code as im not familiar in coding
Thanks for your tutorial . I noticed that the npm xlsx-to-json-lc is picking up the last column first and later starting from the start.. Can you fix the package? ex:
“conversion “:”Yes”,
“cust. name”:”XXXX”,
“plant name”:”YYY”
conversion is my last column in my excel but it got in converted in the json first, can you help me with this?
Hi Rahil,
Greetings!!
I have one issue that on update button in your html page,you can’t write anything. I browse my .xls file but when i click on update button then nothing happen.
Getting this error–
{“error_code”:1,”err_desc”:{“name”:”MulterError”,”message”:”Unexpected field”,”code”:”LIMIT_UNEXPECTED_FILE”,”field”:”uploadCsv”,”storageErrors”:[]}}
how to resolve thie error. Please help.
Hi, got this error: No matching version found for [email protected].4
Solution:
Go to package.json and replace
“xlsx-to-json-lc”: “^0.4.1”
With
“xlsx-to-json-lc”: “^0.5.0”
Great job on this tutorial, thanks a lot!
I use it and works well!
But if file it’s 50K rows, doesn’t work.
Any clue to fixed?
Regards