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:
1 | $npm install --save express mongodb convert-excel-to-json multer |
-> package.json file:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | { "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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 | 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