2. Ingest and Validate Bharat NCAP Assessment CSV Files

Why ingestion deserves its own validation layer

Bharat NCAP assessment exports arrive as flat CSV files produced by different test houses and internal tooling. They share a nominal column set, but in practice they drift: a column may be renamed, a star rating may be written as "5" or "5★", a sub-score may be blank, and a numeric field may contain a stray unit suffix. If the dashboard reads these files directly, a single malformed row can throw during aggregation and take the whole page down. The ingestion pipeline in this case is therefore a defensive boundary: it parses the file, checks the contract, collects row-level problems, and returns a structured result that the dashboard can render — including a partial-success state where valid rows load and invalid rows are reported.

The pipeline has three responsibilities and one hard rule:

• Parse — read the CSV with a header row, tolerate a UTF-8 BOM, and preserve the original row number for error reporting.
• Validate — enforce required columns, per-field types, and value ranges against the canonical record contract.
• Report — return { records, errors, summary } where errors is a list of { row, column, code, message, value } objects.
• Never throw on bad data — a malformed row is a reported error, not an exception. Only a structurally unreadable file (e.g. empty input) produces a top-level failure.

The canonical column contract

The dashboard consumes a single record shape. The CSV columns map onto it as follows; the ranges are the ones the validator enforces.

Pipeline structure

The diagram below shows how a raw export flows through the stages and where the two outcomes diverge: a row either becomes a validated record or an error entry, and both are returned together so the dashboard can render a partial load.

Implementation

The validator is a pure function over the parsed rows. It returns a result object rather than throwing, so the caller decides how to present partial loads. The code below is self-contained and runnable in Node.js 18+ with no dependencies.

const REQUIRED_COLUMNS = [
  "test_id", "vehicle_model", "test_type",
  "adult_occupant_score", "child_occupant_score", "safety_assist_score",
  "star_rating", "test_date",
];

const TEST_TYPES = new Set([
  "frontal_offset", "side_impact", "pole_impact", "pedestrian", "safety_assist",
]);

function parseCsv(text) {
  // Strip UTF-8 BOM if present
  if (text.charCodeAt(0) === 0xfeff) text = text.slice(1);
  const lines = text.split(/\r?\n/).filter((l) => l.trim() !== "");
  if (lines.length === 0) return { header: [], rows: [] };
  const header = lines[0].split(",").map((h) => h.trim());
  const rows = lines.slice(1).map((line, i) => ({
    rowNumber: i + 2, // 1-based, header is row 1
    cells: line.split(",").map((c) => c.trim()),
  }));
  return { header, rows };
}

function validateRow(cells, header, rowNumber, seenIds) {
  const errors = [];
  const get = (col) => {
    const idx = header.indexOf(col);
    return idx === -1 ? undefined : cells[idx];
  };
  const push = (column, code, message, value) =>
    errors.push({ row: rowNumber, column, code, message, value });

  const testId = get("test_id");
  if (!testId) push("test_id", "REQUIRED", "test_id is empty", testId);
  else if (seenIds.has(testId)) push("test_id", "DUPLICATE", "duplicate test_id", testId);
  else seenIds.add(testId);

  if (!get("vehicle_model")) push("vehicle_model", "REQUIRED", "vehicle_model is empty", get("vehicle_model"));

  const testType = get("test_type");
  if (!TEST_TYPES.has(testType)) push("test_type", "ENUM", "unknown test_type", testType);

  for (const col of ["adult_occupant_score", "child_occupant_score", "safety_assist_score"]) {
    const raw = get(col);
    const num = Number(raw);
    if (raw === "" || raw === undefined || Number.isNaN(num)) {
      push(col, "TYPE", "not a number", raw);
    } else if (num < 0 || num > 100) {
      push(col, "RANGE", "score outside 0–100", raw);
    }
  }

  const star = Number(get("star_rating"));
  if (!Number.isInteger(star) || star < 1 || star > 5) {
    push("star_rating", "RANGE", "star_rating must be integer 1–5", get("star_rating"));
  }

  const date = get("test_date");
  if (!/^\d{4}-\d{2}-\d{2}$/.test(date || "") || Number.isNaN(Date.parse(date))) {
    push("test_date", "FORMAT", "expected YYYY-MM-DD", date);
  }

  return errors;
}

function ingestCsv(text) {
  const { header, rows } = parseCsv(text);
  const missing = REQUIRED_COLUMNS.filter((c) => !header.includes(c));
  if (missing.length > 0) {
    return { records: [], errors: [], fatal: { code: "MISSING_COLUMNS", missing } };
  }

  const records = [];
  const errors = [];
  const seenIds = new Set();

  for (const { rowNumber, cells } of rows) {
    const rowErrors = validateRow(cells, header, rowNumber, seenIds);
    if (rowErrors.length > 0) {
      errors.push(...rowErrors);
      continue;
    }
    const idx = (c) => header.indexOf(c);
    records.push({
      testId: cells[idx("test_id")],
      vehicleModel: cells[idx("vehicle_model")],
      testType: cells[idx("test_type")],
      adultOccupantScore: Number(cells[idx("adult_occupant_score")]),
      childOccupantScore: Number(cells[idx("child_occupant_score")]),
      safetyAssistScore: Number(cells[idx("safety_assist_score")]),
      starRating: Number(cells[idx("star_rating")]),
      testDate: cells[idx("test_date")],
    });
  }

  return {
    records,
    errors,
    fatal: null,
    summary: { total: rows.length, valid: records.length, invalid: rows.length - records.length },
  };
}

module.exports = { ingestCsv };

Verifying against a mixed-quality export

Feed the pipeline a small file that deliberately contains one clean row, one out-of-range score, one bad enum, and one duplicate ID. The expected result is two valid records and three error entries — the dashboard should render the two records and surface the three errors in a banner.

const { ingestCsv } = require("./ingest");

const csv = `test_id,vehicle_model,test_type,adult_occupant_score,child_occupant_score,safety_assist_score,star_rating,test_date
T-001,Model A,frontal_offset,88,82,74,5,2024-03-11
T-002,Model B,side_impact,120,70,60,4,2024-04-02
T-003,Model C,rollover,75,68,55,3,2024-05-19
T-001,Model D,frontal_offset,80,79,70,4,2024-06-01`;

const result = ingestCsv(csv);
console.log(result.summary); // { total: 4, valid: 1, invalid: 3 }
console.log(result.errors.map((e) => `${e.row}:${e.column}:${e.code}`));
// [ '3:adult_occupant_score:RANGE', '4:test_type:ENUM', '5:test_id:DUPLICATE' ]

Failure symptoms and how to read them

• Every row rejected with TYPE on scores — the export likely uses a decimal comma (88,5) or a unit suffix (88%). Inspect the raw cell value in the error entry; if it is a comma decimal, normalize before validation rather than loosening the range check.
• fatal: MISSING_COLUMNS on a file that looks correct — usually a BOM or a trailing space in the header. The parser strips the BOM and trims headers, so a remaining miss points to a genuinely renamed column; compare missing against the export's header line.
• Valid rows silently missing — check summary.invalid. A row that fails any single field is excluded entirely; the error list tells you which field, and the row number maps back to the original file line (header is line 1).
• Duplicate IDs across files — the uniqueness check is per-file by design. Cross-file deduplication belongs to the loader that merges multiple exports, not to this validator.

Ingestion result contract

The dashboard consumes exactly this shape, so downstream code never needs to inspect raw CSV again:

With this boundary in place, the dashboard can render a partial load, show a precise error list, and keep the scoring layer downstream working only with clean, typed records.