In this tutorial provides a step-by-step guide on how to import Excel files to MongoDB using Node.js and Express. Node.js is a JavaScript runtime environment that allows you to build and run JavaScript applications outside of a web browser. Express is a popular web framework for Node.js that makes it easy to build and run web applications. MongoDB is a NoSQL database that is known for its scalability and flexibility.
To import Excel files into MongoDB using Node.js and Express, follow these steps
Execute the following command on terminal to create node js app:
mkdir my-app
cd my-app
npm init -y
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
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);
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</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>
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));
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/
This tutorial, you have learned how to import excel file data into MongoDB in Node js + Express.