Uploading CSV files as MongoDB documents using node.js

Question

I am trying to save multiple CSV files into different MongoDB collections in JSON format using node.js express and mongoose, and then compare these files, currently, I managed to upload the CSV files to the MongoDB database but as documents, so every time I upload a new one it just gets added along with the other documents. which will make comparing the CSV files impossible cause you can't tell which documents will belong to what file.

A solution that seemed logical to me was to upload the CSV files as separate Collections and then compare the collections is this the best possible solution if so how could I implement that.

The mongoose model used :

const mongoose = require("mongoose");

const csvSchema = new mongoose.Schema({
  Sr: {
    type: String,
    required: true,
  },
  Cheque_No: {
    type: String,
    required: true,
  },
  Amount: {
    type: String,
    required: true,
  },
});

module.exports = mongoose.model("csvRecords", csvSchema);

The node.js snippet used to insert the CSV file in JSON format :

app.post("/upload-csv", uploads.single("csv"), (req, res) => {
  //convert csvfile to jsonArray
  csv()
    .fromFile(req.file.path)
    .then((jsonObj) => {
      //insertmany is used to save bulk data in database.
      //saving the data in collection(table)
      sheetModel.insertMany(jsonObj, (err, data) => {
        if (err) {
          res.status(400).json({
            message: "Something went wrong!",
          });
        } else {
          res.status(200).json({
            message: "File Uploaded Successfully!",
            result: data,
          });
        }
      });
    });
});

Have you looked at the option of using `mongoimport` command-line tool to import CSV data in to the database? — prasad_, Feb 10 '21 at 14:17
I should have clarified that this is a rest API that will be used by a front end application, so a command-line tool is sadly not the solution. — , Feb 10 '21 at 14:20
You can still run it from within the NodeJS application - as a child process.. — prasad_, Feb 10 '21 at 14:22
Each CSV file (with an upload) maps to _one_ collection? And, 10 CSV files map to 10 different collections. — prasad_, Feb 10 '21 at 14:36

sks147 · Accepted Answer · 2021-02-11T10:54:44.517

I think the problem you have right now is that you don't have an identifier in the document which can help you differentiate which two files you're comparing.

Since your use case is to just compare two documents(json) in a collection using a filename (unique identifier).

Sr	Cheque_No	Amount
1	3141341243	1234
2	3141341244	4000
3	3141341245	2000

If your csv file looks like above

You should model data in this way

{
    "filename": "xyz...", //save from the filename you're importing
    "csvData": [
        {
            Sr: 1,
            Cheque_No: 3141341243,
            Amount: 1234,
        },
        {
            Sr: 2,
            Cheque_No: 3141341244,
            Amount: 4000,
        },
        {
            Sr: 3,
            Cheque_No: 3141341244,
            Amount: 2000,
        },
        ...
    ]
}

Then you can query mongodb collection using filename as a query parameter, and write a custom logic or use a 3rd party library to compare two json documents (csvData)

const mongoose = require("mongoose");

const rowSchema = new mongoose.Schema({
  Sr: {
    type: String,
    required: true,
  },
  Cheque_No: {
    type: String,
    required: true,
  },
  Amount: {
    type: String,
    required: true,
  },
});

const csvSchema = new mongoose.Schema({
  fileName: { type: String, required: true },
  csvData: { type: [rowSchema], required: true },
});

module.exports = mongoose.model("csvRecords", csvSchema);

app.post("/upload-csv", uploads.single("csv"), (req, res) => {
  //convert csvfile to jsonArray
  const fileName = req.body.fileName
 
  csv()
    .fromFile(req.file.path)
    .then((jsonObj) => {
      //finding the document using fileName and setting csvData as the jsonObj
      sheetModel.findOneAndUpdate({ fileName: fileName }, {$set: { csvData: jsonObj, fileName: fileName}, { upsert: true }}, (err, data) => {
        if (err) {
          res.status(400).json({
            message: "Something went wrong!",
          });
        } else {
          res.status(200).json({
            message: "File Uploaded Successfully!",
            result: data,
          });
        }
      });
    });
});

Please check for syntax, implementation would be something similar like this.

How do I insert the file name also? as you can see in the code I added am converting everything to JSON and using insert many to store everything... — , Feb 11 '21 at 09:25
You can use this `uploads.single("csv")` middleware function to modify the `req` object and add a key for filename in the req object and then you can use that as req.body.filename. Don't do insertMany here, instead use a 3rd party npm package to convert csv to json and store that json value in **csvData** key — sks147, Feb 11 '21 at 09:30
And the schema you have defined is wrong, as it denotes a single record/row of a csv file. But csv contains a lot of rows which needs to be converted to json. Check this out : https://www.npmjs.com/package/csvtojson — sks147, Feb 11 '21 at 09:33
Yeah I am using an npm package before the insertMany, do you mind editing your original answer to what is exactly wrong with the mongoose schema along with the changes that need to be added — , Feb 11 '21 at 09:45
For some reason the data does not get added, does it have to do findOneAndUpdate nothing finding any collection with that file name? — , Feb 11 '21 at 10:51
use { upsert: true } with findOneAndUpdate, then it will insert the document if it is a new one. Check this for reference : https://stackoverflow.com/questions/7267102/how-do-i-update-upsert-a-document-in-mongoose — sks147, Feb 11 '21 at 10:53

Uploading CSV files as MongoDB documents using node.js

1 Answers1