Skip to content

All topics  /  Node.js

How to Excel Import To MongoDB Database in Node JS?

Node.js ·

For teams turning “How to Excel Import To MongoDB Database in Node JS?” into a repeatable delivery task, job dissatisfaction can document setup, troubleshooting and review time; managers should interpret those records alongside reliability and completed work, not as a stand-alone score.

Package and operating-system behaviour can change, so verify the commands and supported versions through the Node.js project before running them on a production host.

Hello Friends,

This article will give an example of node js excel import to MongoDB database. This article will give a simple example of importing excel data to the MongoDB database. This post will give you a simple example of node.js: upload/import excel file data into the database. This tutorial will give you a simple example of importing CSV file records in MongoDB with node js. follow the below step to upload the excel file to the MongoDB collection.

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.

Step 1: Create Node Express js App


Execute the following command on the terminal to create the 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 makes it available under req.body property. In other words, it simplifies incoming requests.

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 the 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 an index.html file and add the following code into it:

<!DOCTYPE html>
<html lang="en">
    <head>
        <title>How to Excel Import To MongoDB Database in Node JS? -Nicesnippets.com</title>
        <meta charset="UTF-8">
        <meta name="viewport" content="width=device-width, initial-scale=1">
    </head>
    <body>
        <h1>How to Excel Import To MongoDB Database in Node JS? -Nicesnippets.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 the 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/

More on this topic