How to test csv import validation

Feed the importer ten fixtures that each break one rule, then read the row count it stored. node importer.mjs import mixed.csv policy=skip-bad put 3 of 5 rows in the store, and the same file under policy=all-or-nothing left 0. A file with no header lost a row and reported no error.

Why check this

Run this when an importer is built, and again whenever its files change shape: a new partner export, a renamed column.

A CSV carries no schema. Nothing in the file says which row is the header, what type a column holds, or which fields are required. Each of those is a guess that can be wrong without raising an error.

Step 2 prevents silent loss. A file with no header lost its first record to the header guess and reported zero errors: two rows landed where three were sent. Volume is separate, in How to test a large data export.

Prerequisites

• Node 22. node:sqlite is experimental in 22.23.2 and prints a warning on stderr; --no-warnings silences it so the blocks reproduce.
• A scratch directory you can delete.
• The rules: sku and name required text, qty a required integer 0 to 1000, added a required date as YYYY-MM-DD. Edit SCHEMA for your file.
• Header detection is a guess: RFC 4180 section 2 makes the header line optional and adds no marker.
• No escape is typed by hand. make.mjs builds all ten and takes the line feed from String.fromCharCode.

import { writeFileSync } from 'node:fs';

const LF = String.fromCharCode(10);
const file = (lines) => lines.join(LF) + (lines.length ? LF : '');
const HEAD = 'sku,name,qty,added';

const fixtures = {
  'good.csv': file([
    HEAD,
    'A-100,Widget,12,2026-01-04',
    'A-101,Bolt,0,2026-02-11',
    'A-102,Clamp,340,2026-03-02',
  ]),
  'noheader.csv': file([
    'B-200,Hinge,n/a,2026-01-09',
    'B-201,Rivet,8,2026-01-10',
    'B-202,Screw,15,2026-02-01',
  ]),
  'reordered.csv': file([
    'name,added,sku,qty',
    'Widget,2026-01-04,A-100,12',
    'Bolt,2026-02-11,A-101,0',
  ]),
  'casing.csv': file([
    ' SKU , Name , QTY , Added ',
    'A-100,Widget,12,2026-01-04',
  ]),
  'dupcol.csv': file([
    'sku,name,qty,qty',
    'A-100,Widget,12,7',
  ]),
  'struct.csv': file([
    HEAD,
    'A-100,Widget,12,2026-01-04',
    'A-101,Bolt,0',
    'A-102,Clamp,340,2026-03-02,extra',
    '',
    'A-103,Nut,5,2026-03-09',
  ]),
  'empty.csv': '',
  'headeronly.csv': file([HEAD]),
  'fields.csv': file([
    HEAD,
    'A-100,Widget,12abc,2026-01-04',
    'A-101,Bolt,-5,2026-02-11',
    'A-102,   ,7,2026-03-02',
    ',Clamp,7,2026-03-02',
    'A-104,Nut,7,31/02/2026',
    'A-106,Cap,7,2026-02-31',
    '0012,9788,7,2026-03-05',
  ]),
  'mixed.csv': file([
    HEAD,
    'A-100,Widget,12,2026-01-04',
    'A-101,,4,2026-02-11',
    'A-102,Clamp,340,2026-03-02',
    'A-103,Nut,abc,2026-03-09',
    'A-104,Pin,9,2026-04-01',
  ]),
};

for (const [name, text] of Object.entries(fixtures)) {
  writeFileSync(name, text, 'utf8');
  const lf = [...text].filter((c) => c === LF).length;
  console.log(name.padEnd(15) + String(Buffer.byteLength(text, 'utf8')).padStart(4) + ' bytes  ' + lf + ' LF');
}

importer.mjs holds the parser, the detection, the mapping, the rules, both policies and both report shapes.

import { readFileSync } from 'node:fs';
import { DatabaseSync } from 'node:sqlite';

const DQ = String.fromCharCode(34);
const CR = String.fromCharCode(13);
const LF = String.fromCharCode(10);

const SCHEMA = [
  { name: 'sku', type: 'text', required: true },
  { name: 'name', type: 'text', required: true },
  { name: 'qty', type: 'int', required: true, min: 0, max: 1000 },
  { name: 'added', type: 'date', required: true },
];

// --- parse: RFC 4180 enough for this page. A blank line becomes one empty field.
function parse(text) {
  const recs = [];
  let row = [], f = '', quoted = false, i = 0;
  const endF = () => { row.push(f); f = ''; quoted = false; };
  const endR = () => { endF(); recs.push(row); row = []; };
  while (i < text.length) {
    const c = text[i];
    if (quoted) {
      if (c === DQ) {
        if (text[i + 1] === DQ) { f += DQ; i += 2; continue; }
        quoted = false; i += 1; continue;
      }
      f += c; i += 1; continue;
    }
    if (c === DQ && f === '') { quoted = true; i += 1; continue; }
    if (c === ',') { endF(); i += 1; continue; }
    if (c === CR && text[i + 1] === LF) { endR(); i += 2; continue; }
    if (c === LF || c === CR) { endR(); i += 1; continue; }
    f += c; i += 1;
  }
  if (f !== '' || row.length > 0) endR();
  return recs;
}

// --- one field against one column rule
function checkField(col, raw) {
  const v = raw === undefined ? '' : raw;
  const t = v.trim();
  if (t === '' && col.required) {
    return { ok: false, rule: v === '' ? 'required, the field is empty' : 'required, the field is whitespace only' };
  }
  if (t === '') return { ok: true, value: null };
  if (col.type === 'int') {
    if (!/^-?[0-9]+$/.test(t)) return { ok: false, rule: 'an integer is expected' };
    const n = Number(t);
    if (n < col.min || n > col.max) return { ok: false, rule: 'out of the range ' + col.min + ' to ' + col.max };
    return { ok: true, value: n };
  }
  if (col.type === 'date') {
    if (!/^[0-9]{4}-[0-9]{2}-[0-9]{2}$/.test(t)) return { ok: false, rule: 'a date is expected as YYYY-MM-DD' };
    const d = new Date(t + 'T00:00:00Z');
    if (Number.isNaN(d.getTime()) || d.toISOString().slice(0, 10) !== t) {
      return { ok: false, rule: 'not a day that exists in the calendar' };
    }
    return { ok: true, value: t };
  }
  return { ok: true, value: t };
}

// what a lenient importer would put in the store instead of refusing
function lenient(col, raw) {
  const v = raw === undefined ? '' : raw;
  if (col.type === 'int') return parseInt(v, 10);
  if (col.type === 'date') { const d = new Date(v); return Number.isNaN(d.getTime()) ? 'Invalid Date' : d.toDateString(); }
  return v;
}

const show = (v) => (typeof v === 'string' ? DQ + v + DQ + ' (' + v.length + ' chars)' : String(v));

// --- header
function fits(rec) {
  if (rec.length !== SCHEMA.length) return false;
  return SCHEMA.every((c, i) => checkField(c, rec[i]).ok);
}

function detectHeader(recs) {
  if (recs.length === 0) return { header: false, why: 'the file holds no records' };
  if (fits(recs[0])) return { header: false, why: 'record 1 validates as data' };
  if (recs.length === 1) return { header: true, why: 'record 1 does not validate and there is no record 2' };
  if (fits(recs[1])) return { header: true, why: 'record 1 does not validate as data and record 2 does' };
  return { header: true, why: 'neither record 1 nor record 2 validates as data, so this is a guess' };
}

// --- column mapping
function mapColumns(headerRec, mode) {
  const idx = {}, dup = [], missing = [];
  if (mode === 'position') {
    SCHEMA.forEach((c, i) => { idx[c.name] = i < headerRec.length ? i : -1; });
  } else {
    const keys = headerRec.map((h) => (mode === 'exact' ? h : h.trim().toLowerCase()));
    const at = {};
    keys.forEach((k, i) => {
      if (k in at) dup.push(k + ' at columns ' + (at[k] + 1) + ' and ' + (i + 1) + ', last one wins');
      at[k] = i;
    });
    SCHEMA.forEach((c) => { idx[c.name] = c.name in at ? at[c.name] : -1; });
  }
  SCHEMA.forEach((c) => { if (idx[c.name] === -1) missing.push(c.name); });
  return { idx, dup, missing };
}

// --- structure of one data record
function checkStructure(rec) {
  if (rec.length === 1 && rec[0].trim() === '') {
    return { ok: false, strict: 'blank line', lenient: 'skipped, no error raised' };
  }
  if (rec.length < SCHEMA.length) {
    return { ok: false, strict: rec.length + ' fields, ' + SCHEMA.length + ' expected', lenient: 'padded with empty fields, becomes a field error or a null' };
  }
  if (rec.length > SCHEMA.length) {
    return { ok: false, strict: rec.length + ' fields, ' + SCHEMA.length + ' expected', lenient: 'truncated, field ' + (SCHEMA.length + 1) + ' is dropped in silence' };
  }
  return { ok: true, strict: 'ok', lenient: 'ok' };
}

// --- validate a whole file into rows and located errors
function run(recs, opt) {
  const det = opt.header === 'auto' ? detectHeader(recs) : { header: opt.header === 'yes', why: 'set by the caller' };
  const headerRec = det.header ? recs[0] : null;
  const data = det.header ? recs.slice(1) : recs;
  const map = mapColumns(headerRec || SCHEMA.map((c) => c.name), opt.map);
  const rows = [], errors = [];
  data.forEach((rec, k) => {
    const rowNo = k + (det.header ? 2 : 1);
    const st = checkStructure(rec);
    if (!st.ok) { errors.push({ row: rowNo, column: '-', value: rec.join(','), rule: st.strict }); return; }
    const out = {};
    let bad = false;
    for (const c of SCHEMA) {
      const raw = map.idx[c.name] === -1 ? undefined : rec[map.idx[c.name]];
      const r = checkField(c, raw);
      if (r.ok) out[c.name] = r.value;
      else { bad = true; errors.push({ row: rowNo, column: c.name, value: raw === undefined ? '' : raw, rule: r.rule }); }
    }
    if (!bad) rows.push(out); else rows.push(null);
  });
  return { det, headerRec, data, map, rows, errors };
}

// --- store
function store(db, table, decl) {
  db.exec('CREATE TABLE ' + table + ' (' + decl + ')');
  return db.prepare('INSERT INTO ' + table + ' VALUES (?, ?, ?, ?)');
}

// ---------------------------------------------------------------- modes
const [, script, mode, fileName, ...rest] = process.argv;
const opt = { header: 'auto', map: 'normalized', policy: 'skip-bad', report: 'located' };
for (const a of rest) { const [k, v] = a.split('='); opt[k] = v; }
const text = fileName ? readFileSync(fileName, 'utf8') : '';
const recs = parse(text);

if (mode === 'header') {
  const det = detectHeader(recs);
  console.log(fileName + '  records ' + recs.length);
  console.log('  header: ' + (det.header ? 'yes' : 'no') + '  (' + det.why + ')');
  if (det.header) console.log('  taken as the header row: ' + recs[0].join(','));
  console.log('  data rows: ' + (recs.length - (det.header ? 1 : 0)) + ' of ' + recs.length + ' records');
  for (const m of ['normalized', 'position']) {
    const v = run(recs, { header: det.header ? 'yes' : 'no', map: m });
    console.log('  with map=' + m.padEnd(10) + ' ' + v.rows.filter(Boolean).length + ' of ' + v.data.length + ' data rows valid, errors ' + v.errors.length);
  }
}

if (mode === 'map') {
  const map = mapColumns(recs[0], opt.map);
  console.log(fileName + '  map=' + opt.map + '  header row: ' + recs[0].join(','));
  for (const c of SCHEMA) {
    const i = map.idx[c.name];
    console.log('  ' + c.name.padEnd(6) + ' <- ' + (i === -1 ? 'not found' : 'column ' + (i + 1) + ' value ' + DQ + (recs[1] ? recs[1][i] : '') + DQ));
  }
  for (const d of map.dup) console.log('  duplicate header name: ' + d);
  if (map.missing.length) console.log('  columns with no source: ' + map.missing.join(', '));
  const r = run(recs, { ...opt, header: 'yes' });
  console.log('  result: ' + r.rows.filter(Boolean).length + ' of ' + r.data.length + ' data rows valid, errors ' + r.errors.length);
}

if (mode === 'struct') {
  const det = opt.header === 'auto' ? detectHeader(recs) : { header: opt.header === 'yes' };
  const data = det.header ? recs.slice(1) : recs;
  console.log(fileName + '  records ' + recs.length + '  header ' + (det.header ? 'yes' : 'no') + '  data records ' + data.length);
  data.forEach((rec, k) => {
    const st = checkStructure(rec);
    console.log('  row ' + (k + 2) + '  fields ' + rec.length + '  strict: ' + st.strict + '  lenient: ' + st.lenient);
  });
  if (data.length === 0) console.log('  nothing to validate, an importer that only counts errors reports success');
}

if (mode === 'fields') {
  const r = run(recs, { ...opt, header: 'yes' });
  for (const e of r.errors) {
    const col = SCHEMA.find((c) => c.name === e.column);
    console.log('  row ' + e.row + '  ' + e.column.padEnd(6) + ' ' + (DQ + e.value + DQ).padEnd(14)
      + ' reject: ' + e.rule.padEnd(38) + ' lenient would store ' + show(lenient(col, e.value)));
  }
  const okRows = r.rows.map((v, k) => (v ? k + 2 : 0)).filter(Boolean);
  console.log('  rows that passed every rule: ' + (okRows.join(', ') || 'none'));
  console.log('  ' + r.data.length + ' data rows, ' + okRows.length + ' valid, ' + (r.data.length - okRows.length) + ' rejected, ' + r.errors.length + ' field errors');
}

if (mode === 'loose') {
  const db = new DatabaseSync(':memory:');
  const ins = store(db, 'loose', 'sku INTEGER, name TEXT, qty INTEGER, added TEXT');
  const data = recs.slice(1);
  for (const rec of data) ins.run(rec[0], rec[1], rec[2], rec[3]);
  console.log(fileName + '  ' + data.length + ' rows inserted with no validation into');
  console.log('  loose(sku INTEGER, name TEXT, qty INTEGER, added TEXT)');
  for (const r of db.prepare('SELECT rowid, typeof(sku) ts, sku, typeof(qty) tq, qty, name, added FROM loose').all()) {
    const cell = (t, v) => t + ' ' + (t === 'text' ? DQ + v + DQ : String(v));
    console.log('  row ' + (r.rowid + 1) + '  sku ' + cell(r.ts, r.sku).padEnd(14)
      + ' qty ' + cell(r.tq, r.qty).padEnd(14) + ' name ' + (DQ + r.name + DQ).padEnd(10) + ' added ' + r.added);
  }
}

if (mode === 'import') {
  const r = run(recs, { ...opt, header: 'yes' });
  const db = new DatabaseSync(':memory:');
  const ins = store(db, 'product', 'sku TEXT NOT NULL, name TEXT NOT NULL, qty INTEGER NOT NULL, added TEXT NOT NULL');
  const valid = r.rows.filter(Boolean);
  console.log(fileName + '  policy=' + opt.policy);
  console.log('  ' + r.data.length + ' data rows, ' + valid.length + ' valid, ' + (r.data.length - valid.length) + ' invalid');
  db.exec('BEGIN');
  for (const row of valid) ins.run(row.sku, row.name, row.qty, row.added);
  if (opt.policy === 'all-or-nothing' && r.errors.length > 0) { db.exec('ROLLBACK'); console.log('  rolled back, the file is refused as one unit'); }
  else { db.exec('COMMIT'); console.log('  committed'); }
  const held = db.prepare('SELECT * FROM product').all();
  console.log('  the store now holds ' + held.length + ' rows');
  for (const h of held) console.log('    ' + [h.sku, h.name, h.qty, h.added].join('|'));
  console.log('  rows the caller must resend: ' + (opt.policy === 'all-or-nothing' ? r.data.length : r.data.length - valid.length));
}

if (mode === 'report') {
  const r = run(recs, { ...opt, header: 'yes' });
  if (opt.report === 'count') {
    console.log('  Import failed: ' + r.errors.length + ' of ' + r.data.length + ' rows are invalid.');
  } else {
    console.log('  row | column | value      | rule');
    for (const e of r.errors) {
      console.log('  ' + String(e.row).padStart(3) + ' | ' + e.column.padEnd(6) + ' | '
        + (DQ + e.value + DQ).padEnd(10) + ' | ' + e.rule);
    }
    console.log('  ' + r.errors.length + ' errors in ' + r.data.length + ' data rows. Fix them in the source file at the rows named above.');
  }
}

void script;

Steps

1. 1

   Step 1.

   Build the fixtures. Each breaks one rule and is small enough to read whole.

   node make.mjs
   

   good.csv         97 bytes  4 LF
   noheader.csv     78 bytes  3 LF
   reordered.csv    70 bytes  3 LF
   casing.csv       54 bytes  2 LF
   dupcol.csv       35 bytes  2 LF
   struct.csv      116 bytes  6 LF
   empty.csv         0 bytes  0 LF
   headeronly.csv   19 bytes  1 LF
   fields.csv      186 bytes  8 LF
   mixed.csv       141 bytes  6 LF

   empty.csv is 0 bytes and step 5 needs it.

2. 2

   Step 2.

   Ask the importer to guess, on a file with a header and one without.

   for f in good.csv noheader.csv; do node --no-warnings importer.mjs header $f; done
   

   good.csv  records 4
   header: yes  (record 1 does not validate as data and record 2 does)
   taken as the header row: sku,name,qty,added
   data rows: 3 of 4 records
   with map=normalized 3 of 3 data rows valid, errors 0
   with map=position   3 of 3 data rows valid, errors 0
   noheader.csv  records 3
   header: yes  (record 1 does not validate as data and record 2 does)
   taken as the header row: B-200,Hinge,n/a,2026-01-09
   data rows: 2 of 3 records
   with map=normalized 0 of 2 data rows valid, errors 8
   with map=position   2 of 2 data rows valid, errors 0

   Both answers are yes and the second is wrong. Mapped by position, noheader.csv stored 2 of its 3 records, errors 0.

3. 3

   Step 3.

   Reorder the header columns, then map by position and by name.

   for m in position normalized; do node --no-warnings importer.mjs map reordered.csv map=$m; done
   

   reordered.csv  map=position  header row: name,added,sku,qty
   sku    <- column 1 value "Widget"
   name   <- column 2 value "2026-01-04"
   qty    <- column 3 value "A-100"
   added  <- column 4 value "12"
   result: 0 of 2 data rows valid, errors 4
   reordered.csv  map=normalized  header row: name,added,sku,qty
   sku    <- column 3 value "A-100"
   name   <- column 1 value "Widget"
   qty    <- column 4 value "12"
   added  <- column 2 value "2026-01-04"
   result: 2 of 2 data rows valid, errors 0

   Positional mapping put Widget in sku and A-100 in qty: 0 of 2 rows valid. By name, 2 of 2.

4. 4

   Step 4.

   Change the spelling of the header, then repeat a column name.

   for a in "casing.csv map=exact" "casing.csv map=normalized" "dupcol.csv map=normalized"; do node --no-warnings importer.mjs map $a; done
   

   casing.csv  map=exact  header row:  SKU , Name , QTY , Added 
   sku    <- not found
   name   <- not found
   qty    <- not found
   added  <- not found
   columns with no source: sku, name, qty, added
   result: 0 of 1 data rows valid, errors 4
   casing.csv  map=normalized  header row:  SKU , Name , QTY , Added 
   sku    <- column 1 value "A-100"
   name   <- column 2 value "Widget"
   qty    <- column 3 value "12"
   added  <- column 4 value "2026-01-04"
   result: 1 of 1 data rows valid, errors 0
   dupcol.csv  map=normalized  header row: sku,name,qty,qty
   sku    <- column 1 value "A-100"
   name   <- column 2 value "Widget"
   qty    <- column 4 value "7"
   added  <- not found
   duplicate header name: qty at columns 3 and 4, last one wins
   columns with no source: added
   result: 0 of 1 data rows valid, errors 1

   One space and one capital cost exact matching all four columns. A repeated qty left added with no source.

5. 5

   Step 5.

   Read the structure of every record, then the two files that hold no rows.

   for f in struct.csv empty.csv headeronly.csv; do node --no-warnings importer.mjs struct $f header=yes; done
   

   struct.csv  records 6  header yes  data records 5
   row 2  fields 4  strict: ok  lenient: ok
   row 3  fields 3  strict: 3 fields, 4 expected  lenient: padded with empty fields, becomes a field error or a null
   row 4  fields 5  strict: 5 fields, 4 expected  lenient: truncated, field 5 is dropped in silence
   row 5  fields 1  strict: blank line  lenient: skipped, no error raised
   row 6  fields 4  strict: ok  lenient: ok
   empty.csv  records 0  header yes  data records 0
   nothing to validate, an importer that only counts errors reports success
   headeronly.csv  records 1  header yes  data records 0
   nothing to validate, an importer that only counts errors reports success

   Three of five records are structurally wrong and a lenient reader repairs all three. Both empty files report success.

6. 6

   Step 6.

   Validate field by field, printing what a lenient importer would have stored.

   node --no-warnings importer.mjs fields fields.csv
   

     row 2  qty    "12abc"        reject: an integer is expected                 lenient would store 12
   row 3  qty    "-5"           reject: out of the range 0 to 1000             lenient would store -5
   row 4  name   "   "          reject: required, the field is whitespace only lenient would store "   " (3 chars)
   row 5  sku    ""             reject: required, the field is empty           lenient would store "" (0 chars)
   row 6  added  "31/02/2026"   reject: a date is expected as YYYY-MM-DD       lenient would store "Invalid Date" (12 chars)
   row 7  added  "2026-02-31"   reject: not a day that exists in the calendar  lenient would store "Tue Mar 03 2026" (15 chars)
   rows that passed every rule: 8
   7 data rows, 1 valid, 6 rejected, 6 field errors

   Six rows rejected, one accepted. 2026-02-31 has the right shape and is not a day: new Date returned 3 March.

7. 7

   Step 7.

   Insert the same seven rows with no validation and read the store.

   node --no-warnings importer.mjs loose fields.csv
   

   fields.csv  7 rows inserted with no validation into
   loose(sku INTEGER, name TEXT, qty INTEGER, added TEXT)
   row 2  sku text "A-100"   qty text "12abc"   name "Widget"   added 2026-01-04
   row 3  sku text "A-101"   qty integer -5     name "Bolt"     added 2026-02-11
   row 4  sku text "A-102"   qty integer 7      name "   "      added 2026-03-02
   row 5  sku text ""        qty integer 7      name "Clamp"    added 2026-03-02
   row 6  sku text "A-104"   qty integer 7      name "Nut"      added 31/02/2026
   row 7  sku text "A-106"   qty integer 7      name "Cap"      added 2026-02-31
   row 8  sku integer 12     qty integer 7      name "9788"     added 2026-03-05

   qty is INTEGER and holds the text 12abc on row 2. sku is INTEGER too, and turned 0012 into 12 on row 8.

8. 8

   Step 8.

   Run the same bad file under both failure policies and read the store after each.

   for p in all-or-nothing skip-bad; do node --no-warnings importer.mjs import mixed.csv policy=$p; done
   

   mixed.csv  policy=all-or-nothing
   5 data rows, 3 valid, 2 invalid
   rolled back, the file is refused as one unit
   the store now holds 0 rows
   rows the caller must resend: 5
   mixed.csv  policy=skip-bad
   5 data rows, 3 valid, 2 invalid
   committed
   the store now holds 3 rows
     A-100|Widget|12|2026-01-04
     A-102|Clamp|340|2026-03-02
     A-104|Pin|9|2026-04-01
   rows the caller must resend: 2

   All-or-nothing rolled back to 0 rows and the caller resends 5. Skip-bad committed 3 and the caller resends 2.

9. 9

   Step 9.

   Print the report for that run in both shapes.

   for r in count located; do node --no-warnings importer.mjs report mixed.csv report=$r; done
   

     Import failed: 2 of 5 rows are invalid.
   row | column | value      | rule
     3 | name   | ""         | required, the field is empty
     5 | qty    | "abc"      | an integer is expected
   2 errors in 5 data rows. Fix them in the source file at the rows named above.

   The second names row 3, column name, the value and the rule. The first names a number.

How to read the result

| What you see | What it means | What to do | | --- | --- | --- | | A headerless file importing one row fewer than it holds | Record 1 was read as the header | Take the answer from the caller. Step 2: 2 of 3 records stored, errors 0. | | Every row failing on every column | Header names were matched exactly | Trim and lowercase the header cells. Step 4 went from 0 of 1 to 1 of 1. | | A column arriving with another column's values | Columns are mapped by position and the export reordered them | Map by name. Step 3 put A-100 in qty. | | One schema column silently empty | Two header cells share a name | Reject duplicate names. Step 4 left added with no source. | | A record whose field count differs from the header | A structural error, or a silent pad or truncate | Step 5. The five field row loses field 5 under a lenient reader. | | An import of an empty file reporting success | Zero rows is not an error to a counter | Assert a minimum row count. Step 5 gives empty and header only the same verdict. | | An INTEGER column holding the text 12abc | Nothing validated, so storage decided | Step 7. The other INTEGER column turned 0012 into 12. | | 2026-02-31 stored as 3 March | The date was parsed rather than validated | Step 6. Compare the parsed date back against the text. | | An error report that is a count | The report says how many, not which | Step 9 prints both shapes of the same two errors. |

Common mistakes

Thresholds

What to check next

• How to test a partially applied import: the store after an import dies halfway.
• How to check duplicate rows on import: the rule left out here, it needs stored rows.
• How to check the delimiter of a csv file: a wrong separator gives every record one field.
• How to check the encoding of a CSV file: the bytes behind a header cell that will not match.
• How to escape quotes in csv: why a record can outnumber the header.

FAQ

How to validate a csv file?

In three passes, because they fail differently: structure in step 5, the header in steps 2 to 4, the fields in step 6. One pass that stops at the first error hides the other two.

How to check if a csv file is valid?

There is no valid CSV in the abstract. A file is valid against one schema. Step 6 rejected 6 of 7 rows against the four rules here; an importer with no rules takes the same file.

How to check if csv file has header?

You cannot read it off the file. RFC 4180 makes the header line optional and adds no marker. Step 2 shows the guess going wrong: 3 records in, 2 rows stored, zero errors.

What does csv import validation cover?

Four things, in the order they fail: whether row 1 is a header, how header names reach columns, the field count per record, and each field against its rule. The form equivalent is How to check required field validation.

Why is my csv file not importing?

Read the error report first. If it is a count with no row numbers, as in step 9, rerun with a located report. The three causes measured here: a header that does not match by name, a wrong field count, a field holding only spaces.

Verified