Validate Uploaded CSV Data In Node Express

Confirm existence and data type of all columns within each row

Continuing from earlier post Node Express Endpoint to Upload and Process CSV File, in this post we will add validation to the uploaded CSV data.

With the same index.js file and settings as the earlier post, we only need to add moment library for date validation. So install and save it: npm install moment --save. The file should look like this:

'use strict';

const http = require('http');
const fs = require('fs');

const express = require('express');
const multer = require('multer');
const csv = require('fast-csv');
const moment = require('moment');

const Router = express.Router;
const upload = multer({ dest: 'tmp/csv/' });
const app = express();
const router = new Router();
const server = http.createServer(app);
const port = 9000

router.post('/', upload.single('file'), function (req, res) {
  const fileRows = [];

  // open uploaded file
  csv.fromPath(req.file.path)
    .on("data", function (data) {
      fileRows.push(data); // push each row
    })
    .on("end", function () {
      console.log(fileRows); //contains array of arrays.
      fs.unlinkSync(req.file.path);   // remove temp file
      //process "fileRows" and respond
    })
});

app.use('/upload-csv', router);

// Start server
function startServer() {
  server.listen(port, function () {
    console.log('Express server listening on ', port);
  });
}

setImmediate(startServer);

Let’s create a csv file of students. It has three fields for each student: name, roll number and date of birth. Rows of sample student csv look like this:

Now, with the addition of two helper validation functions we validate the data within the uploaded CSV file.

First is validateCsvData. It receives array of arrays (the result of file reading), goes through each row except for the header (first), calls our second helper function validateCsvRow with each row, and returns error if there’s any, with the row number.

function validateCsvData(rows) {
  const dataRows = rows.slice(1, rows.length); //ignore header at 0 and get rest of the rows
  for (let i = 0; i < dataRows.length; i++) {
    const rowError = validateCsvRow(dataRows[i]);
    if (rowError) {
      return `${rowError} on row ${i + 1}`
    }
  }
  return;
}

And validateCsvRow validates each column within a row. Here we’re confirming if name exists, roll number is an integer value and date of birth is a valid date.

function validateCsvRow(row) {
  if (!row[0]) {
    return "invalid name"
  }
  else if (!Number.isInteger(Number(row[1]))) {
    return "invalid roll number"
  }
  else if (!moment(row[2], "YYYY-MM-DD").isValid()) {
    return "invalid date of birth"
  }
  return;
}

Finally we use validateCsvData in upload end point by passing it the rows decoded from csv. If nothing is returned the csv data is valid and we respond a success message, else we respond with RESTful status code 403 and error message that is received:

router.post('/', upload.single('file'), function (req, res) {
  const fileRows = [];

  // open uploaded file
  csv.fromPath(req.file.path)
    .on("data", function (data) {
      fileRows.push(data); // push each row
    })
    .on("end", function () {
      console.log(fileRows);
      fs.unlinkSync(req.file.path);   // remove temp file

      const validationError = validateCsvData(fileRows);
      if (validationError) {
        return res.status(403).json({ error: validationError });
      }
      //else process "fileRows" and respond
      return res.json({ message: "valid csv" })
    })
});

Now start your server: node index.js

And call the end point with a CSV file using curl:

curl -X POST -F "file=@students.csv" http://localhost:9000/upload-csv

The response for valid CSV will be:

{"message":"valid csv"}

Remove the name from row 1 and call again, to get the response:

{"error":"invalid name on row 1"}

Similarly for invalid date like 1995/13/12:

{"error":"invalid date on row 1"}

Also check Export Mongodb Collection Data In CSV From Node Express Mongoose Application