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

node.js-import-excel-file-to-mongodb-using-convert-excel-to-json-lib---feature-image

In the tutorial, Grokonez shows how to import data from Excel File to MongoDB with Node.js using Convert-Excel-To-Json lib.

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

Technologies

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

Goal

– We create a Node.js project as below structure:

node.js-import-excel-file-to-mongodb-using-convert-excel-to-json-lib-project-structure-1

– Excel File:

node.js-import-excel-file-to-mongodb-using-convert-excel-to-json-lib-excel-file-1

-> Results:

node.js-import-excel-file-to-mongodb-using-convert-excel-to-json-lib-mongodb-documents

Practice

Install MongoDB & Convert-Excel-To-Json

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

$npm install mongodb
$npm install convert-excel-to-json

-> package.json file:

{
  "name": "node.js-import-excel-file-to-mongodb",
  "version": "1.0.0",
  "description": "Node.js Import Excel File to MongoDB with Convert-Excel-To-Json lib",
  "main": "index.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1",
	"start": "node index.js"
  },
  "keywords": [
    "Node.js",
    "Excel",
    "MongoDB",
    "Convert-Excel-To-JSON"
  ],
  "author": "grokonez.com",
  "license": "ISC",
  "dependencies": {
    "convert-excel-to-json": "^1.6.1",
    "mongodb": "^3.1.13"
  }
}

Import Excel File to MongoDB

-> index.js file:

'use strict';
const excelToJson = require('convert-excel-to-json');

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

// -> Read Excel File to Json Data

const excelData = excelToJson({
    sourceFile: 'customers.xlsx',
    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;
  
  var 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();
  });
});

SourceCode

Node.js-Import-Excel-File-to-MongoDB


By grokonez | March 2, 2019.

Last updated on May 16, 2021.



Related Posts


Got Something To Say:

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

*