Import excel file to mongodb using node js + express; Through this tutorial, you will learn how to import excel file data into MongoDB in Node js + Express.
Import excel file to mongodb using node js + express tutorial, you will also learn how to upload excel file into Node js + express app. Then read excel file data using npm convert-excel-to-json package and import it in mongodb database with node js + express app.
Import Excel File to MongoDB using Node js + Express
- Step 1 – Create Node Express js App
- Step 2 – Install Required Node Modules
- Step 3 – Create Model
- Step 4 – Create Excel File Upload HTML Markup Form
- Step 5 – Import Modules in App.js
- Step 6 – Start App Server
Step 1 – Create Node Express js App
Execute the following command on terminal to create node js app:
mkdir my-app cd my-app npm init -y
Step 2 – Install Required Node Modules
Execute the following command on the terminal to express ejs body-parser mongoose convert-excel-to-json dependencies:
npm install -g express-generator npx express --view=ejs npm install mongoose multer body-parser npm install convert-excel-to-json
body-parser – Node.js request body parsing middleware which parses the incoming request body before your handlers, and make it available under req.body property. In other words, it simplifies the incoming request.
Express-EJS– EJS is a simple templating language which is used to generate HTML markup with plain JavaScript. It also helps to embed JavaScript to HTML pages
Mongoose – Mongoose is a MongoDB object modeling tool designed to work in an asynchronous environment. Mongoose supports both promises and callbacks.
Multer – Multer is a node.js middleware for handling multipart/form-data , which is primarily used for uploading files. It is written on top of busboy for maximum efficiency.
convert-excel-to-json – Convert Excel to JSON, mapping sheet columns to object keys.
Step 3 – Create Model
Create Models directory and inside this directory create userModel.js file; Then add following code into it:
var mongoose = require('mongoose');
var excelSchema = new mongoose.Schema({
name:{
type:String
},
email:{
type:String
},
age:{
type:Number
}
});
module.exports = mongoose.model('userModel',excelSchema);
Step 4 – Create Excel File Upload HTML Markup Form
Create a form with a `file input` element that allows us to choose the Excel file and a button to submit the form; So create index.html file and add the following code into it:
<!DOCTYPE html>
<html lang="en">
<head>
<title>Node js upload/Import excel file to Mongodb database - Tutsmake.com</title>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
</head>
<body>
<h1>Node js upload Excel file to Mongodb database - Tutsmake.com</h1>
<form action="/uploadfile" enctype="multipart/form-data" method="post">
<input type="file" name="uploadfile" accept='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, application/vnd.ms-excel' >
<input type="submit" value="Upload Excel">
</form>
</body>
</html>
Step 5 – Import Modules in App.js
Import express, body-parser, mongoose, multer, convert-excel-to-json dependencies in app.js; as shown below:
var express = require('express');
var mongoose = require('mongoose');
var multer = require('multer');
var path = require('path');
var userModel = require('./models/userModel');
var excelToJson = require('convert-excel-to-json');
var bodyParser = require('body-parser');
var storage = multer.diskStorage({
destination:(req,file,cb)=>{
cb(null,'./public/uploads');
},
filename:(req,file,cb)=>{
cb(null,file.originalname);
}
});
var uploads = multer({storage:storage});
//connect to db
mongoose.connect('mongodb://localhost:27017/exceldemo',{useNewUrlParser:true})
.then(()=>console.log('connected to db'))
.catch((err)=>console.log(err))
//init app
var app = express();
//set the template engine
app.set('view engine','ejs');
//fetch data from the request
app.use(bodyParser.urlencoded({extended:false}));
//static folder
app.use(express.static(path.resolve(__dirname,'public')));
//route for Home page
app.get('/', (req, res) => {
res.sendFile(__dirname + '/index.html');
});
// Upload excel file and import to mongodb
app.post('/uploadfile', upload.single("uploadfile"), (req, res) =>{
importExcelData2MongoDB(__dirname + '/uploads/' + req.file.filename);
console.log(res);
});
// Import Excel File to MongoDB database
function importExcelData2MongoDB(filePath){
// -> Read Excel File to Json Data
const excelData = excelToJson({
sourceFile: filePath,
sheets:[{
// Excel Sheet Name
name: 'Customers',
// Header Row -> be skipped and will not be present at our result object.
header:{
rows: 1
},
// Mapping columns to keys
columnToKey: {
A: '_id',
B: 'name',
C: 'address',
D: 'age'
}
}]
});
// -> Log Excel Data to Console
console.log(excelData);
/**
{
Customers:
[
{ _id: 1, name: 'Jack Smith', address: 'Massachusetts', age: 23 },
{ _id: 2, name: 'Adam Johnson', address: 'New York', age: 27 },
{ _id: 3, name: 'Katherin Carter', address: 'Washington DC', age: 26 },
{ _id: 4, name: 'Jack London', address: 'Nevada', age: 33 },
{ _id: 5, name: 'Jason Bourne', address: 'California', age: 36 }
]
}
*/
// Insert Json-Object to MongoDB
userModel.insertMany(jsonObj,(err,data)=>{
if(err){
console.log(err);
}else{
res.redirect('/');
}
});
fs.unlinkSync(filePath);
}
//assign port
var port = process.env.PORT || 3000;
app.listen(port,()=>console.log('server run at port '+port));
Step 6 – Start App Server
You can use the following command to start node js app server:
//run the below command npm start after run this command open your browser and hit http://127.0.0.1:3000/
Conclusion
Import excel file to mongodb using node js + express; Through this tutorial, you have learned how to import excel file data into MongoDB in Node js + Express.
