How to check the delimiter of a csv file

Count each candidate separator per line with a parser that respects quoting, not with a byte count. The delimiter is the candidate whose count is non-zero and identical on every line. On the tab file below the byte count named comma at 13 hits, and the parser named tab, two per line.

Why check this

Run this on the first file from a new integration partner, and again whenever an import ticket says a load finished and the data is wrong. Nothing inside a file declares its separator.

The failure it prevents is a load that reports success on the wrong shape. Step 6 reproduces it: the row count is 4, matching the file, and two of those rows hold a single field. A loader that logs rows processed sees nothing wrong.

Prerequisites

• Node 22 for the two programs below. Nothing in core parses CSV, so the reader is printed in full.
• LibreOffice at C:\Program Files\LibreOffice\program\soffice.com, exported as SOFFICE. This install runs a Ukrainian UI locale, LCID 1058, whose decimal separator is a comma.
• file 5.44 and xxd.
• The quoting rules used here are on How to escape quotes in csv. Encoding is a separate read: How to check the encoding of a CSV file.

make-fixtures.mjs writes four files. A tab has no glyph in an editor or a diff, so the bytes are built in code.

import { writeFileSync } from 'node:fs';

const TAB = String.fromCharCode(9);
const LF = String.fromCharCode(10);
const Q = String.fromCharCode(34);

const join = (rows, sep) => rows.map((r) => r.join(sep)).join(LF) + LF;

// shipments.tsv: tab delimited, commas inside plain fields
const shipments = [
  ['id', 'address', 'amount'],
  ['1', '12 Main St, Apt 4, Springfield, IL, 62704', '1,299.00'],
  ['2', '9 Oak Ave, Portland, OR, 97205', '845.50'],
  ['3', '4 Elm Rd, Suite 9, Austin, TX, 78701', '2,430.75'],
];
writeFileSync('shipments.tsv', join(shipments, TAB), 'latin1');

// orders.csv: comma delimited, commas and semicolons inside quoted fields
const orders =
  'id,customer,note,total' + LF +
  '1,Acme Ltd,' + Q + 'Springfield, IL; call first' + Q + ',19.99' + LF +
  '2,Globex Corp,' + Q + 'Portland, OR; ship Tue' + Q + ',5.00' + LF +
  '3,Initech LLC,' + Q + 'Austin, TX; hold; call; then ship' + Q + ',42.00' + LF;
writeFileSync('orders.csv', orders, 'latin1');

// invoices.csv: semicolon delimited, sep= declaration on line 1
const invoices = [
  ['id', 'customer', 'total'],
  ['1', 'Acme Ltd', '1234,50'],
  ['2', 'Globex Corp', '845,00'],
];
writeFileSync('invoices.csv', 'sep=;' + LF + join(invoices, ';'), 'latin1');

// same rows, semicolons, no declaration line
writeFileSync('plain-semicolon.csv', join(invoices, ';'), 'latin1');

csvtool.mjs counts and parses.

import { readFileSync } from 'node:fs';

const NAMES = { ',': 'comma', ';': 'semicolon', '\t': 'tab', '|': 'pipe' };
const CANDIDATES = Object.keys(NAMES);

/** RFC 4180 reader. Quotes protect the separator and the line break. */
export function parse(text, sep) {
  const rows = [];
  let row = [], field = '', quoted = false;
  for (let i = 0; i < text.length; i += 1) {
    const c = text[i];
    if (quoted) {
      if (c !== '"') field += c;
      else if (text[i + 1] === '"') { field += '"'; i += 1; }
      else quoted = false;
    } else if (c === '"') quoted = true;
    else if (c === sep) { row.push(field); field = ''; }
    else if (c === '\n') { row.push(field); rows.push(row); row = []; field = ''; }
    else if (c !== '\r') field += c;
  }
  if (field !== '' || row.length) { row.push(field); rows.push(row); }
  return rows;
}

/** Separators per line, counting every byte, quotes ignored. */
const rawCounts = (lines, sep) => lines.map((l) => l.split(sep).length - 1);

/** Separators per line, counting only the ones a parser would act on. */
const fieldCounts = (text, sep) => parse(text, sep).map((r) => r.length - 1);

const constant = (a) => a.length > 0 && a.every((n) => n === a[0]) && a[0] > 0;
const pad = (s, w) => String(s).padEnd(w);

function report(label, counts) {
  const total = counts.reduce((a, b) => a + b, 0);
  console.log(pad(label, 12) + pad(counts.join(' '), 20) + pad(total, 7) + (constant(counts) ? 'yes' : 'no'));
}

const [mode, file, sepArg] = process.argv.slice(2);
const text = readFileSync(file, 'latin1');
const lines = text.replace(/\n$/, '').split('\n');

if (mode === 'raw' || mode === 'fields') {
  const quoteAware = mode === 'fields';
  console.log(pad('candidate', 12) + pad(quoteAware ? 'per line (parsed)' : 'per line (bytes)', 20) + pad('total', 7) + 'constant');
  for (const sep of CANDIDATES) {
    report(NAMES[sep], quoteAware ? fieldCounts(text, sep) : rawCounts(lines, sep));
  }
  const score = (sep) => (quoteAware ? fieldCounts(text, sep) : rawCounts(lines, sep));
  const totals = CANDIDATES.map((s) => [s, score(s).reduce((a, b) => a + b, 0)]).sort((a, b) => b[1] - a[1]);
  const stable = CANDIDATES.filter((s) => constant(score(s)));
  console.log('most frequent       ' + NAMES[totals[0][0]] + ' (' + totals[0][1] + ')');
  console.log('non-zero, constant  ' + (stable.length === 1 ? NAMES[stable[0]] + ' (' + score(stable[0])[0] + ' per line)' : stable.map((s) => NAMES[s]).join(', ') || 'none'));
} else if (mode === 'rows') {
  const sep = { comma: ',', semicolon: ';', tab: '\t', pipe: '|' }[sepArg];
  const rows = parse(text, sep);
  console.log('delimiter ' + sepArg + '   rows ' + rows.length);
  rows.forEach((r, i) => console.log('row ' + i + '  fields ' + r.length + '  ' + r.map((f) => '[' + f + ']').join(' ')));
}

Write the fixtures and point SOFFICE at the binary.

node make-fixtures.mjs && export SOFFICE="/c/Program Files/LibreOffice/program/soffice.com"

Steps

1. 1

   Step 1.

   Ask the type database what the files are.

   file shipments.tsv orders.csv invoices.csv plain-semicolon.csv
   

   shipments.tsv:       ASCII text
   orders.csv:          CSV text
   invoices.csv:        ASCII text
   plain-semicolon.csv: ASCII text

   One file matched the CSV rule and three did not, and none names a separator.

2. 2

   Step 2.

   Count every candidate byte, per line, in the tab file.

   node csvtool.mjs raw shipments.tsv
   

   candidate   per line (bytes)    total  constant
   comma       0 5 3 5             13     no
   semicolon   0 0 0 0             0      no
   tab         2 2 2 2             8      yes
   pipe        0 0 0 0             0      no
   most frequent       comma (13)
   non-zero, constant  tab (2 per line)

   Comma wins on volume, 13 against 8, with per-line counts 0, 5, 3 and 5. Tab appears exactly twice on every line. Frequency picks comma, constancy picks tab, and constancy is the rule: a delimiter produces the same column count on every row.

3. 3

   Step 3.

   Confirm which byte is constant.

   xxd shipments.tsv | head -3
   

   00000000: 6964 0961 6464 7265 7373 0961 6d6f 756e  id.address.amoun
   00000010: 740a 3109 3132 204d 6169 6e20 5374 2c20  t.1.12 Main St, 
   00000020: 4170 7420 342c 2053 7072 696e 6766 6965  Apt 4, Springfie

   09 sits between id, address and amount, 0a ends the line, and the 2c bytes are inside the address. Read the hex whenever the answer renders as blank space.

4. 4

   Step 4.

   Count bytes on a file with quoted fields.

   node csvtool.mjs raw orders.csv
   

   candidate   per line (bytes)    total  constant
   comma       3 4 4 4             15     no
   semicolon   0 1 1 3             5      no
   tab         0 0 0 0             0      no
   pipe        0 0 0 0             0      no
   most frequent       comma (15)
   non-zero, constant  none

   Comma reads 3, 4, 4, 4, so the constancy test rejects it and the run ends with no answer. The extra comma on each data line is inside a quoted address.

5. 5

   Step 5.

   Count the same file through the parser.

   node csvtool.mjs fields orders.csv
   

   candidate   per line (parsed)   total  constant
   comma       3 3 3 3             12     yes
   semicolon   0 0 0 0             0      no
   tab         0 0 0 0             0      no
   pipe        0 0 0 0             0      no
   most frequent       comma (12)
   non-zero, constant  comma (3 per line)

   Same bytes, one difference: a separator inside quotes is not counted. Comma drops from 15 to 12, becomes constant at 3, and wins. The three that disappeared were address text. Semicolon fell from 5 to 0, so the rival candidate was never a separator.

6. 6

   Step 6.

   Parse a semicolon file with a comma parser.

   node csvtool.mjs rows invoices.csv comma
   

   delimiter comma   rows 4
   row 0  fields 1  [sep=;]
   row 1  fields 1  [id;customer;total]
   row 2  fields 2  [1;Acme Ltd;1234] [50]
   row 3  fields 2  [2;Globex Corp;845] [00]

   The row count is 4, matching the file, so a count check passes. Row 0 is the declaration line held as one field. Row 1 is the whole header in one column. Rows 2 and 3 split at the decimal comma inside the total, putting 1234 and 50 in different columns.

7. 7

   Step 7.

   Hand the same bytes to the spreadsheet with no separator named.

   "$SOFFICE" --headless --convert-to html --outdir out invoices.csv >/dev/null && grep -o '<td[^>]*>[^<]*</td>' out/invoices.html | sed 's/ data-sheets-value="[^"]*"//'
   

   <td height="21" align="left">sep=;</td>
   <td height="21" align="left">id;customer;total</td>
   <td height="21" align="left">1;Acme Ltd;1234</td>
   <td align="right" sdval="50" sdnum="1058;">50</td>
   <td height="21" align="left">2;Globex Corp;845</td>
   <td align="right" sdval="0" sdnum="1058;">0</td>

   The text node is what the cell displays and sdval is what Calc stored. This conversion put sep=; in the first cell as content and split the rest on commas, matching step 6 cell for cell. sdnum="1058;" is the Ukrainian locale identifier.

8. 8

   Step 8.

   Read the same rows with the separator stated.

   "$SOFFICE" --headless --infilter="Text - txt - csv (StarCalc):59,34,76,1" --convert-to html --outdir semi plain-semicolon.csv >/dev/null && grep -o '<td[^>]*>[^<]*</td>' semi/plain-semicolon.html | sed 's/ data-sheets-value="[^"]*"//'
   

   <td height="21" align="left">id</td>
   <td align="left">customer</td>
   <td align="left">total</td>
   <td height="21" align="right" sdval="1" sdnum="1058;">1</td>
   <td align="left">Acme Ltd</td>
   <td align="right" sdval="1234,5" sdnum="1058;">1234,5</td>
   <td height="21" align="right" sdval="2" sdnum="1058;">2</td>
   <td align="left">Globex Corp</td>
   <td align="right" sdval="845" sdnum="1058;">845</td>

   59 is the semicolon, 34 the quote, 76 the UTF-8 charset, 1 the first row. Three columns now, and 1234,50 arrives as the number 1234,5, right aligned. On this locale the comma is the decimal separator, so it cannot also separate fields without the ambiguity step 6 printed.

How to read the result

| What you see | What it means | What to do | | --- | --- | --- | | One candidate non-zero and constant | That is the delimiter | Use it. Step 5 is the run that settles it. | | The highest total is not the constant one | A character inside the data outnumbers the separator | Trust constancy. In step 2 comma led 13 to 8 and tab was the answer. | | No candidate is constant on raw bytes | Quoting is hiding the pattern | Re-count through a parser, as step 5 does, before calling the file broken. | | Two candidates are both constant | The sample is too small to decide, or a column is fixed width | Take more lines, or read the producer's export settings. | | Row count right, field count 1 | The parser is using the wrong separator | Compare field counts per row, not row totals. Step 6 is the reproduction. | | A number split across two columns | The decimal separator is also the field separator | Change the export separator, or quote the numeric fields. | | A cell displaying sep=; | The reader treated the declaration as data | Configure the separator on import rather than relying on the line. |

Common mistakes

Thresholds

What to check next

• How to escape quotes in csv: why steps 4 and 5 differ.
• How to count rows in a csv file: the count step 6 got right.
• How to test csv import validation: rejecting a one field row on load.
• How to check the encoding of a CSV file: the other property no file declares.
• Csv scientific notation: what Calc does to those columns next.

FAQ

What is a csv delimiter?

The character that ends one field and starts the next. RFC 4180 names the comma. Semicolon, tab and pipe are also in use.

Which csv delimiter types are in use?

The four this page counts: comma, semicolon, tab and pipe. Semicolon is the usual choice where the decimal mark is a comma, as step 8 measures.

Why does a csv file open with the wrong delimiter?

The reader chose one and the producer wrote another. Step 7 is a conversion that chose comma for a semicolon file, and the whole header landed in one cell. The reader there is LibreOffice Calc 24.2.3.2. Excel is not installed on the machine that produced this page, so nothing here states what Excel does with any of these files.

Does a sep= line set the delimiter?

Not for a parser. Step 6 shows it arriving as an ordinary first row, and the headless conversion in step 7 puts it in a cell of LibreOffice Calc 24.2.3.2.

Verified