How to Import Excel Files to MongoDB Using Node.js and Express

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

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

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</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/

This tutorial, you have learned how to import excel file data into MongoDB in Node js + Express.

#nodejs #mongodb