Nodejs Express RestAPI – Upload/Import Excel file/data to MongoDB – using Convert-Excel-to-Json + Multer

nodejs-express-restapis-upload-import-excel-files-to-mongodb---feature-image

In the tutorial, Grokonez shows how to upload & import Excel File/Data to MongoDB using convert-excel-to-json and multer libs.

Related posts:
Node.js Import Excel File to MongoDB – using Convert-Excel-To-Json lib

Technologies

  • Node.js
  • Express
  • Convert-Excel-To-Json
  • Multer
  • MongoDB

Goal

We create a Node.js project as below structure:

nodejs-express-restapis-upload-import-excel-files-to-mongodb---project-structure

Excel File:

nodejs-express-restapis-upload-import-excel-files-to-mongodb---excel-file

-> Results:

nodejs-express-restapis-upload-import-excel-files-to-mongodb---client-request

nodejs-express-restapis-upload-import-excel-files-to-mongodb---mongodb-records

Practice

Install Express, Convert-Excel-To-Json, Multer, MongoDB

– Init package.json file by cmd: npm init -> Then install express, mongodb, convert-excel-to-json & multer libs:


$npm install --save express mongodb convert-excel-to-json multer

-> package.json file:


{
  "name": "node.js-express-restapi-upload-import-excel-xlsl-to-mongodb",
  "version": "1.0.0",
  "description": "Nodejs Express Upload Import Excel File/Data to MongoDB",
  "main": "index.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1",
	"start": "node index.js"
  },
  "keywords": [
    "Nodejs",
    "Express",
    "Mongodb",
    "Multer",
    "Convert-Excel-to-Json"
  ],
  "author": "grokonez.com",
  "license": "ISC",
  "dependencies": {
    "convert-excel-to-json": "^1.6.1",
    "express": "^4.16.4",
    "mongodb": "^3.1.13",
    "multer": "^1.4.1"
  }
}

Upload/Import Excel File/Data to MongoDB

-> index.js file:


const fs = require('fs');
const multer = require('multer');
const express = require('express');

let MongoClient = require('mongodb').MongoClient;
let url = "mongodb://localhost:27017/";

const excelToJson = require('convert-excel-to-json');
 
const app = express();
 
global.__basedir = __dirname;
 
// -> Multer Upload Storage
const storage = multer.diskStorage({
    destination: (req, file, cb) => {
        cb(null, __basedir + '/uploads/')
    },
    filename: (req, file, cb) => {
        cb(null, file.fieldname + "-" + Date.now() + "-" + file.originalname)
    }
});
 
const upload = multer({storage: storage});
 
// -> Express Upload RestAPIs
app.post('/api/uploadfile', upload.single("uploadfile"), (req, res) =>{
    importExcelData2MongoDB(__basedir + '/uploads/' + req.file.filename);
    res.json({
        'msg': 'File uploaded/import successfully!', 'file': req.file
    });
});
 
// -> 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
    MongoClient.connect(url, { useNewUrlParser: true }, (err, db) => {
        if (err) throw err;
        let dbo = db.db("gkzdb");
        dbo.collection("customers").insertMany(excelData.Customers, (err, res) => {
            if (err) throw err;
            console.log("Number of documents inserted: " + res.insertedCount);
            /**
                Number of documents inserted: 5
            */
            db.close();
        });
    });
			
    fs.unlinkSync(filePath);
}
 
// Create a Server
let server = app.listen(8080, function () {
 
  let host = server.address().address;
  let port = server.address().port;
 
  console.log("App listening at http://%s:%s", host, port);
})

Sourcecode

Node.js-Express-RestAPI-Upload-Import-Excel-XLSL-to-MongoDB



By grokonez | March 10, 2019.

Last updated on May 16, 2021.



Related Posts


3 thoughts on “Nodejs Express RestAPI – Upload/Import Excel file/data to MongoDB – using Convert-Excel-to-Json + Multer”

  1. // -> Log Excel Data to Console
    console.log(excelData);

    I`m getting here blank array, can anyone please suggest why it is going on, even excel which i`m uploading that also consists of data and also uploading to the “uploads” folder , but in the console.log(exceldata) array showing the, blank array result

    1. const excelData = excelToJson({
      source: fs.readFileSync(filePath),
      header:{
      rows: 1
      },
      columnToKey: {
      A: ‘_id’,
      B: ‘name’,
      C: ‘address’,
      D: ‘age’
      }// fs.readFileSync return a Buffer
      });

      Try this, it’s will be work

  2. How to manage empty cellls, if cells are empty then its not coming in resulting array of objects.
    Is there any way to set default value if cell is blank or empty?

Got Something To Say:

Your email address will not be published. Required fields are marked *

*