I want to build an API "/upload/excel" that will allow users to import an excel file and inside it after receiving an excel file, it will read its field and save it into database.
How to achieve this?
2 Answers
You can upload a file through multer or formidable
https://www.npmjs.com/package/multer
https://www.npmjs.com/package/formidable
And you can read xl files though any one of these below npm packges
I am using multer for uploading the file and xlsx to process it and mongodb as a database (mongoose for the model):
Lead is the data model, you have to add the Excel sheet columns name. See mongoose documentation for more information
const express = require("express");
const multer = require("multer");
const connectDB = require("./config/db");
const Lead = require("./models/Lead");
connectDB();
const uploadXLSX = async (req, res, next) => {
try {
let path = req.file.path;
var workbook = XLSX.readFile(path);
var sheet_name_list = workbook.SheetNames;
let jsonData = XLSX.utils.sheet_to_json(
workbook.Sheets[sheet_name_list[0]]
);
if (jsonData.length === 0) {
return res.status(400).json({
success: false,
message: "xml sheet has no data",
});
}
let savedData = await Lead.create(jsonData);
return res.status(201).json({
success: true,
message: savedData.length + " rows added to the database",
});
} catch (err) {
return res.status(500).json({ success: false, message: err.message });
}
};
var storage = multer.diskStorage({
destination: function (req, file, cb) {
cb(null, "uploads");
},
filename: function (req, file, cb) {
cb(null, Date.now() + "-" + file.originalname);
},
});
const upload = multer({ storage: storage });
app.post("/upload", upload.single("xlsx"), uploadXLSX);
const port = process.env.PORT || 5000;
const server = app.listen(port, () => {
console.log("app running on port", port);
});
So from here when you make a call to localhost:5000/upload with postman see picture below
Related