How to create a MongoDB schema for excel file which can have arbitrary fields uploaded by user?

Question

I am creating a rest api for excel. How can I create a MongoDB schema for excel file which can have arbitrary fields uploaded by user?

Answer 1

First of all we can define a model called excelData.model.js which has a data field that accepts array of values from the excel file:

const mongoose = require("mongoose")
const idvalidator = require("mongoose-id-validator");

const excelDataSchema = new mongoose.Schema({
    Data:{
      type: Array,
      required: true
    }
})
excelDataSchema.plugin(idvalidator);
const ExcelData = mongoose.model('excelData',excelDataSchema)
exports.ExcelData = ExcelData

Then we can define excelData.controller.js which is a controller that has a function to read the excel file and extract data from it then calls the model to save the data

const readExcelFile = require('read-excel-file/node')
const { ExcelData } = require("../models/excelData.model");

exports.excelDataExtractor = async (req, res) => {
    try {
        if (req.file == undefined) {
            return res.status(400).send("Please upload an excel file!");
        }
        readExcelFile(req.file.path)
            .then(async (rows) => {
                var data = []
                for (let i = 0; i < rows.length; i++) {
                    data.push(rows[i])
                }
                for (let i = 0; i < data.length; i++) {
                    try {
                        let excelData = new ExcelData({ data: data[i] })
                        await excelData.save()
                    } catch (ex) {
                        res.status(400).send(ex.message);
                    }
                }
                return res.status(200).send({
                    message: "Excel Data extracted successfully",
                    data
                })
            })
            .catch(error => {
                return res.status(400).send({
                    message: "Failed to read the uploaded excel file!",
                    error: error.message
                });
            })
    } catch (ex) {
        res.status(500).send(ex.message);
    }
}

Answer 2

"Schema" is pretty much antonym of "arbitrary fields" by definition. If your input data is indeed completely arbitrary you very obviously can't describe or validate it with schema.