How to check duplicate rows on import

Count the duplicates three ways on one file, then import it twice. node dupes.mjs three orders.csv found 1 duplicate row by whole record, 3 by the declared key and 6 by a normalised key. Importing the same three rows a second time left 6 rows, 3 stale rows or 3 current rows, one per write strategy.

Why check this

A duplicate is a decision the importer makes, not a property the file holds. The 12 rows below carry 1, 3 or 6 duplicates depending on which columns the comparison reads and how it normalises them.

Run this when an importer ships, and after any change to its key or its write statement. The failure it prevents is the second run: a nightly job that fired twice, or a user who pressed the button again.

Step 5 measures that run. Row counts alone are in How to count rows in a csv file, the header guess and the field rules in How to test csv import validation.

Prerequisites

• Node 22. node:sqlite is experimental in 22.23.2 and warns on stderr; --no-warnings silences it.
• A scratch directory you can delete.
• The declared business key here is sku plus name. Edit KEY for your file.
• No accented character is typed by hand. make.mjs builds both spellings from String.fromCodePoint, and every program prints anything above U+007E as a code point.
• Record boundaries come from How to escape quotes in csv.

import { writeFileSync } from 'node:fs';

const LF = String.fromCharCode(10);
const NFC_E = String.fromCodePoint(0x00e9);            // e with acute, one code point
const NFD_E = 'e' + String.fromCodePoint(0x0301);      // e plus combining acute, two
const file = (lines) => lines.join(LF) + LF;
const HEAD = 'sku,name,qty,price';

const fixtures = {
  'orders.csv': file([
    HEAD,
    'A-100,Blue Widget,12,19.99',
    'A-100,Blue Widget,12,19.99',
    'A-100,Blue Widget,7,19.99',
    ' A-100 ,Blue Widget,12,19.99',
    'a-100,BLUE WIDGET,12,19.99',
    'A-100,Blue  Widget,12,19.99',
    'B-200,Caf' + NFC_E + ' Sleeve,5,1.50',
    'B-200,Caf' + NFD_E + ' Sleeve,5,1.50',
    'C-300,Cable,3,1.50',
    'C-300,Cable,3,1.5',
    'D-400,,2,4.00',
    'D-400,NULL,2,4.00',
  ]),
  'day1.csv': file([HEAD, 'A-100,Blue Widget,12,19.99', 'B-200,Cable,3,1.50', 'C-300,Clamp,5,4.00']),
  'day2.csv': file([HEAD, 'A-100,Blue Widget,12,19.99', 'B-200,Cable,3,1.50', 'C-300,Clamp,9,4.00']),
  'dupkey.csv': file([HEAD, 'A-100,Blue Widget,12,19.99', 'A-100,Blue Widget,7,19.99', 'A-100,Blue Widget,30,19.99']),
};

for (const [name, text] of Object.entries(fixtures)) {
  writeFileSync(name, text, 'utf8');
  const marks = [...text]
    .filter((c) => c.codePointAt(0) > 126)
    .map((c) => 'U+' + c.codePointAt(0).toString(16).toUpperCase().padStart(4, '0'));
  console.log(name.padEnd(11) + String(Buffer.byteLength(text, 'utf8')).padStart(4) + ' bytes  '
    + (text.split(LF).length - 1) + ' lines  above U+007E: ' + (marks.join(' ') || 'none'));
}

dupes.mjs holds the parser, the six normalisation rules, the group report and the three write strategies.

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 SP = String.fromCharCode(32);
const US = String.fromCharCode(31); // key separator, cannot occur in the data

// RFC 4180 enough for these fixtures. Where a record ends is a separate check.
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;
}

function load(name) {
  const recs = parse(readFileSync(name, 'utf8'));
  const head = recs[0];
  return {
    name, head,
    rows: recs.slice(1).map((cells, k) => ({ no: k + 2, cells, get: (c) => cells[head.indexOf(c)] ?? '' })),
  };
}

// --- the six normalisation decisions, each one on its own so it can be measured
const collapse = (v) => { let s = v; while (s.includes(SP + SP)) s = s.split(SP + SP).join(SP); return s; };
const isNum = (v) => { const t = v.trim(); return t !== '' && Number.isFinite(Number(t)); };

const RULE = {
  trim: (v) => v.trim(),
  collapse,
  casefold: (v) => v.toLowerCase(),
  unicode: (v) => v.normalize('NFC'),
  numeric: (v) => (isNum(v) ? String(Number(v)) : v),
  nullish: (v) => (v.trim() === '' || v.trim().toUpperCase() === 'NULL' ? '(null)' : v),
};
const ORDER = ['trim', 'collapse', 'casefold', 'unicode', 'numeric', 'nullish'];

function norm(v, rules) {
  let s = v;
  for (const r of ORDER) if (rules.includes(r)) s = RULE[r](s);
  return s;
}

const KEY = ['sku', 'name'];
const NORMSET = ['trim', 'collapse', 'casefold'];

function keyOf(row, cols, rules) { return cols.map((c) => norm(row.get(c), rules)).join(US); }

function groupsOf(file, cols, rules) {
  const map = new Map();
  for (const row of file.rows) {
    const k = keyOf(row, cols, rules);
    if (!map.has(k)) map.set(k, []);
    map.get(k).push(row);
  }
  const groups = [...map.entries()].filter(([, rows]) => rows.length > 1);
  return { groups, dupRows: groups.reduce((n, [, rows]) => n + rows.length - 1, 0) };
}

// every printed field is ASCII, so a code point cannot be rewritten in transit
const vis = (s) => [...s].map((c) => {
  const cp = c.codePointAt(0);
  if (cp === 31) return '|';
  return (cp > 126 || cp < 32) ? '<U+' + cp.toString(16).toUpperCase().padStart(4, '0') + '>' : c;
}).join('');

// --- write strategies
const SQL = {
  plain: 'INSERT INTO product VALUES (?, ?, ?, ?)',
  ignore: 'INSERT OR IGNORE INTO product VALUES (?, ?, ?, ?)',
  upsert: 'INSERT INTO product VALUES (?, ?, ?, ?) ON CONFLICT(sku) '
    + 'DO UPDATE SET name = excluded.name, qty = excluded.qty, price = excluded.price',
};

function open(strategy) {
  const db = new DatabaseSync(':memory:');
  db.exec('CREATE TABLE product (sku TEXT NOT NULL, name TEXT NOT NULL, qty INTEGER NOT NULL, price TEXT NOT NULL)');
  if (strategy !== 'plain') db.exec('CREATE UNIQUE INDEX product_sku ON product (sku)');
  return db;
}

function write(db, strategy, file) {
  const st = db.prepare(SQL[strategy]);
  for (const r of file.rows) st.run(r.get('sku'), r.get('name'), Number(r.get('qty')), r.get('price'));
}

// ---------------------------------------------------------------- modes
const [, , mode, ...args] = process.argv;

if (mode === 'three') {
  const f = load(args[0]);
  const modes = [
    ['whole record, raw', f.head, []],
    ['key sku+name, raw', KEY, []],
    ['key sku+name, normalised', KEY, NORMSET],
  ];
  console.log(f.name + '  ' + f.rows.length + ' data rows  columns ' + f.head.join(','));
  for (const [label, cols, rules] of modes) {
    const g = groupsOf(f, cols, rules);
    console.log('  ' + label.padEnd(25) + ' groups ' + g.groups.length + '  duplicate rows ' + g.dupRows);
    for (const [k, rows] of g.groups) {
      console.log('      ' + (DQ + vis(k) + DQ).padEnd(29) + ' x' + rows.length + '  rows ' + rows.map((r) => r.no).join(', '));
    }
  }
}

if (mode === 'rules') {
  const f = load(args[0]);
  console.log(f.name + '  every column compared, one rule at a time');
  const line = (label, rules) => {
    const g = groupsOf(f, f.head, rules);
    const shape = g.groups.map(([, rows]) => rows.map((r) => r.no).join('+')).join('  ') || 'none';
    console.log('  ' + label.padEnd(13) + ' groups ' + g.groups.length + '  duplicate rows ' + g.dupRows + '   ' + shape);
  };
  line('raw bytes', []);
  for (const r of ORDER) line('+ ' + r, [r]);
  line('all six', ORDER);
}

if (mode === 'report') {
  const f = load(args[0]);
  const g = groupsOf(f, KEY, NORMSET);
  console.log(f.name + '  key ' + KEY.join('+') + '  rules ' + NORMSET.join(','));
  console.log('  key | count | rows | fields that differ inside the group');
  let adjudicate = 0;
  for (const [k, rows] of g.groups) {
    const diff = [];
    for (const c of f.head) {
      const vals = [...new Set(rows.map((r) => r.get(c)))];
      if (vals.length > 1) diff.push(c + ' ' + vals.map((v) => DQ + vis(v) + DQ).join(' / '));
    }
    if (diff.length) adjudicate += 1;
    console.log('  ' + vis(k).padEnd(17) + ' | ' + rows.length + ' | ' + rows.map((r) => r.no).join(',').padEnd(15)
      + ' | ' + (diff.join('  ') || 'nothing, the rows are byte identical'));
  }
  console.log('  ' + g.groups.length + ' groups, ' + g.dupRows + ' rows beyond the first in each, '
    + (f.rows.length - g.dupRows) + ' rows would reach the store');
  console.log('  groups a person has to decide: ' + adjudicate);
}

if (mode === 'import') {
  const files = args.filter((a) => a.endsWith('.csv')).map(load);
  const db0 = new DatabaseSync(':memory:');
  console.log('node:sqlite ' + db0.prepare('SELECT sqlite_version() AS v').get().v);
  db0.close();
  for (const s of ['plain', 'ignore', 'upsert']) {
    console.log('  ' + s + (s === 'plain' ? '   no unique index' : '  UNIQUE INDEX product_sku ON product (sku)'));
    const db = open(s);
    for (const f of files) {
      write(db, s, f);
      const n = db.prepare('SELECT count(*) AS c FROM product').get().c;
      const c3 = db.prepare('SELECT qty FROM product WHERE sku = ? ORDER BY rowid').all('C-300').map((x) => x.qty).join(', ');
      console.log('    after ' + f.name.padEnd(9) + ' rows ' + n + '   C-300 qty ' + c3);
    }
    db.close();
  }
}

if (mode === 'index') {
  const db = new DatabaseSync(':memory:');
  db.exec('CREATE TABLE raw (sku TEXT UNIQUE)');
  db.exec('CREATE TABLE nocase (sku TEXT COLLATE NOCASE UNIQUE)');
  db.exec('CREATE TABLE norm (sku TEXT, sku_key TEXT UNIQUE)');
  const spellings = ['A-100', SP + 'A-100' + SP, 'a-100'];
  for (const t of ['raw', 'nocase', 'norm']) {
    let kept = 0;
    for (const s of spellings) {
      let verdict;
      try {
        if (t === 'norm') db.prepare('INSERT INTO norm VALUES (?, ?)').run(s, s.trim().toLowerCase());
        else db.prepare('INSERT INTO ' + t + ' VALUES (?)').run(s);
        kept += 1; verdict = 'accepted';
      } catch (e) { verdict = 'rejected  ' + e.message; }
      console.log('  ' + t.padEnd(6) + ' ' + (DQ + s + DQ).padEnd(9) + ' ' + verdict);
    }
    console.log('  ' + t.padEnd(6) + ' holds ' + kept + ' of 3');
  }
  db.close();
}

if (mode === 'survive') {
  const f = load(args[0]);
  console.log(f.name + '  the file offers sku A-100 with qty ' + f.rows.map((r) => r.get('qty')).join(', ') + ' in that order');
  for (const s of ['plain', 'ignore', 'upsert']) {
    const db = open(s);
    write(db, s, f);
    const rows = db.prepare('SELECT qty FROM product ORDER BY rowid').all();
    console.log('  ' + s.padEnd(7) + ' rows ' + rows.length + '  qty kept ' + rows.map((x) => x.qty).join(', '));
    db.close();
  }
}

Steps

1. 1

   Step 1.

   Build the fixtures. orders.csv carries the duplicates, the other three feed the import steps.

   node make.mjs
   

   orders.csv  305 bytes  13 lines  above U+007E: U+00E9 U+0301
   day1.csv     84 bytes  4 lines  above U+007E: none
   day2.csv     84 bytes  4 lines  above U+007E: none
   dupkey.csv   99 bytes  4 lines  above U+007E: none

   The two code points above U+007E are the two spellings of one name that step 3 merges.

2. 2

   Step 2.

   Count the duplicates in that one file three ways.

   node --no-warnings dupes.mjs three orders.csv
   

   orders.csv  12 data rows  columns sku,name,qty,price
   whole record, raw         groups 1  duplicate rows 1
       "A-100|Blue Widget|12|19.99"  x2  rows 2, 3
   key sku+name, raw         groups 2  duplicate rows 3
       "A-100|Blue Widget"           x3  rows 2, 3, 4
       "C-300|Cable"                 x2  rows 10, 11
   key sku+name, normalised  groups 2  duplicate rows 6
       "a-100|blue widget"           x6  rows 2, 3, 4, 5, 6, 7
       "c-300|cable"                 x2  rows 10, 11

   One file, three answers: 1, 3 and 6. None is wrong, and the file does not pick between them.

3. 3

   Step 3.

   Turn on one normalisation rule at a time, over every column.

   node --no-warnings dupes.mjs rules orders.csv
   

   orders.csv  every column compared, one rule at a time
   raw bytes     groups 1  duplicate rows 1   2+3
   + trim        groups 1  duplicate rows 2   2+3+5
   + collapse    groups 1  duplicate rows 2   2+3+7
   + casefold    groups 1  duplicate rows 2   2+3+6
   + unicode     groups 2  duplicate rows 2   2+3  8+9
   + numeric     groups 2  duplicate rows 2   2+3  10+11
   + nullish     groups 2  duplicate rows 2   2+3  12+13
   all six       groups 4  duplicate rows 7   2+3+5+6+7  8+9  10+11  12+13

   Each rule on its own moves the count from 1 to 2 and names the pair it merged. Read them as six decisions. trim makes A-100 the same code as A-100, collapse makes Blue Widget one product, casefold makes a-100 that same code again. unicode merges the two spellings of one accented name, numeric merges 1.50 and 1.5, nullish merges an empty field and the text NULL. Each is a rule the importer's owner sets, not one the file states.

4. 4

   Step 4.

   Print the duplicate groups in the source file, before anything is written.

   node --no-warnings dupes.mjs report orders.csv
   

   orders.csv  key sku+name  rules trim,collapse,casefold
   key | count | rows | fields that differ inside the group
   a-100|blue widget | 6 | 2,3,4,5,6,7     | sku "A-100" / " A-100 " / "a-100"  name "Blue Widget" / "BLUE WIDGET" / "Blue  Widget"  qty "12" / "7"
   c-300|cable       | 2 | 10,11           | price "1.50" / "1.5"
   2 groups, 6 rows beyond the first in each, 6 rows would reach the store
   groups a person has to decide: 2

   Both groups differ inside themselves, so no rule picks a winner. The first holds three spellings of the key and two values of qty, the second two spellings of the price.

5. 5

   Step 5.

   Import the same rows twice under three write strategies. day2.csv is day1.csv with qty 5 changed to 9 on C-300.

   node --no-warnings dupes.mjs import day1.csv day2.csv
   

   node:sqlite 3.51.3
   plain   no unique index
     after day1.csv  rows 3   C-300 qty 5
     after day2.csv  rows 6   C-300 qty 5, 9
   ignore  UNIQUE INDEX product_sku ON product (sku)
     after day1.csv  rows 3   C-300 qty 5
     after day2.csv  rows 3   C-300 qty 5
   upsert  UNIQUE INDEX product_sku ON product (sku)
     after day1.csv  rows 3   C-300 qty 5
     after day2.csv  rows 3   C-300 qty 9

   The plain insert doubled the table to 6. INSERT OR IGNORE held 3 rows and kept qty 5, dropping the change with no error. The upsert held 3 and moved qty to 9.

6. 6

   Step 6.

   Offer one unique index three spellings of the same business key.

   node --no-warnings dupes.mjs index
   

     raw    "A-100"   accepted
   raw    " A-100 " accepted
   raw    "a-100"   accepted
   raw    holds 3 of 3
   nocase "A-100"   accepted
   nocase " A-100 " accepted
   nocase "a-100"   rejected  UNIQUE constraint failed: nocase.sku
   nocase holds 2 of 3
   norm   "A-100"   accepted
   norm   " A-100 " rejected  UNIQUE constraint failed: norm.sku_key
   norm   "a-100"   rejected  UNIQUE constraint failed: norm.sku_key
   norm   holds 1 of 3

   The raw index took all three. COLLATE NOCASE caught the case and still took the spaced one. Only the stored normalised key rejected both.

7. 7

   Step 7.

   Ask which of three rows sharing one key survives.

   node --no-warnings dupes.mjs survive dupkey.csv
   

   dupkey.csv  the file offers sku A-100 with qty 12, 7, 30 in that order
   plain   rows 3  qty kept 12, 7, 30
   ignore  rows 1  qty kept 12
   upsert  rows 1  qty kept 30

   INSERT OR IGNORE kept the first value, 12. The upsert kept the last, 30. A reader who assumes last wins is right for one of the three.

How to read the result

| What you see | What it means | What to do | | --- | --- | --- | | Three tools reporting three duplicate counts for one file | Each compares different columns under different rules | Write the rule down. Step 2 gives 1, 3 and 6 on the same 12 rows. | | A row count that doubles after a rerun | A plain insert with no unique index | Step 5. The same 3 rows twice left 6. | | A rerun that changes nothing, including the fields that changed | INSERT OR IGNORE dropped the whole row | Step 5. C-300 kept qty 5 when the file said 9. | | A unique index accepting two rows a person calls the same | The index enforces the column, not the meaning | Step 6. Store a normalised key column and index that. | | Duplicates appearing only after a case change upstream | The comparison is byte exact | Step 3. casefold moved the count from 1 to 2. | | Two identical looking names counted as two rows | One is NFC, the other NFD | Step 3. unicode merged rows 8 and 9. | | 1.50 and 1.5 counted as two prices | The compare reads text, not the number | Step 3. numeric merged rows 10 and 11. | | An empty field and the text NULL counted apart | Absence has two spellings in the file | Step 3. nullish merged rows 12 and 13. | | A duplicate group whose rows differ on a non-key field | No rule can pick a winner | Step 4 names the fields. Send the group to a person. |

Common mistakes

Thresholds

What to check next

• How to test csv import validation: the header guess and field rules assumed here.
• How to count rows in a csv file: the count a deduplication pass changes.
• How to compare two csv files: the same questions between two files.
• How to test a partially applied import: the store after a run dies mid file.
• How to check the encoding of a CSV file: the bytes behind two identical looking names.

FAQ

How to find duplicate rows in csv?

Build a key from the columns that define a record, then count the keys. Step 4 prints the key, its count, the row numbers and the fields that differ inside the group.

How to remove duplicates from csv?

Decide which row wins first. Step 7 offers one sku with qty 12, 7 and 30: INSERT OR IGNORE kept 12, the upsert kept 30, the plain insert kept all three.

Csv import creates duplicate records, what do I check?

The write statement and the index. Step 5 doubled a 3 row table to 6 with a plain insert and no unique index. The file was the same on both runs.

How to check duplicates in csv file before importing?

Run the report in step 4 against the source. It reads no database and names the rows a person has to settle: 2 groups here, each with fields differing inside it.

How to check duplicate records in csv file that differ only in case?

Turn the rules on one at a time, as in step 3. casefold alone moved the count from 1 duplicate row to 2, merging rows 2, 3 and 6.

Verified