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

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:

Excel File:

-> Results:

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

Last updated on May 16, 2021.