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
Contents
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
// -> 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 im uploading that also consists of data and also uploading to the “uploads” folder , but in the console.log(exceldata) array showing the, blank array resultconst 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