MongoDB is an open-source NoSQL document-oriented database, that stores the data in JSON format. It is a distributed database that is designed to store a large scale of data.
In this article, we will look into how excel data can be imported to the MongoDB database and stored as a document in the specified collection. The implementation of MongoDB is done with Node.js + Express.js and it will read the data from the excel sheet and import it straight to your database.
Setting up the project
Create a project from the terminal
mkdir my-app
cd my-app
npm init
Install npm modules
npm install express csvtojson mongoose multer ejs
Multer: Multer is a node.js middleware for handling multipart/form-data, primarily used for uploading files.
csvtojson : This module is used for converting CSV file to JSON file.
Create Node.js express app
Create a file named app.js and import the modules.
const express = require('express')
const mongoose = require('mongoose')
const multer = require('multer')
const Student = require('./studentSchema')
const csvtojson = require('csvtojson')
const app = express()
mongoose.connect('mongodb://localhost:27017/MongoExcelDemo').then(() => { // MongoDB connection
console.log('database connected')
});
app.use(express.static('public')) // static folder
app.set('view engine','ejs') // set the template engine
app.listen(3000, () => {
console.log('server started at port 3000')
})
Student module in the database
Here, we are going to create a student module by creating a student.js file to store the student data in the Student named collection.
const mongoose = require('mongoose')
const studentSchema = mongoose.schema({
name : {type : String, required : true},
email : {type: String, required : true},
standard : {type: String, required: true}
})
module.exports = mongoose.model('Student', studentSchema)
Create form markup to upload the excel file in index.ejs file
Here, the form will take input from an excel file, this file must be in CSV format.
<HTML>
<body>
<form action="/uploadExcelFile" 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" class="p-10 w-100" value="Upload Excel">
</form>
</body>
</html>
Uploading the file to the database
Multer is quite popular for its efficiency and elementary for handling the files in an express server.
For the express app to accept the files, we will use Multer storage to handle the file uploads. This storage will be used to access the uploaded file.
App.js
var excelStorage = multer.diskStorage({
destination:(req,file,cb)=>{
cb(null,'./public/excelUploads'); // file added to the public folder of the root directory
},
filename:(req,file,cb)=>{
cb(null,file.originalname);
}
});
var excelUploads = multer({storage:excelStorage});
app.get('/',(req,res) => {
res.render('index.ejs');
})
// upload excel file and import in mongodb
app.post('/uploadExcelFile', excelUploads.single("uploadfile"), (req, res) =>{
importFile('./public' + '/excelUploads/' + req.file.filename);
function importFile(filePath){
// Read Excel File to Json Data
var arrayToInsert = [];
csvtojson().fromFile(filePath).then(source => {
// Fetching the all data from each row
for (var i = 0; i < source.length; i++) {
console.log(source[i]["name"])
var singleRow = {
name: source[i]["name"],
email: source[i]["email"],
standard: source[i]["standard"],
};
arrayToInsert.push(singleRow);
}
//inserting into the table student
Student.insertMany(arrayToInsert, (err, result) => {
if (err) console.log(err);
if(result){
console.log("File imported successfully.");
res.redirect('/')
}
});
});
}
})
The above code will create disk storage to store the file in the public folder of the root directory. By clicking on the submit button, data is fetched from each row of the uploaded file and will be imported to the student collection using insertMany method.
You can find the whole source code here Github