Acquire / file · All operations

Detect a CSV file's settings

A variant of Read a CSV or delimited file

Read the start of a CSV file and report the delimiter, quoting, header and column types it uses.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Report the delimiter, quoting, header and column types of two files

It reads the orders table the proofs share. Create it first:

SQL
CREATE TABLE orders AS SELECT * FROM (VALUES
  (1,  'Alice Ng',      'north', DATE '2026-01-03', 120.00, 'widget', 'a|b',   '{"channel":"web","items":[1,2]}'),
  (2,  'alice ng',      'north', DATE '2026-01-05',  80.50, 'gadget', 'b',     '{"channel":"shop","items":[3]}'),
  (3,  'Bob  Okafor',   'south', DATE '2026-01-05', 300.00, 'widget', 'a|c',   '{"channel":"web","items":[4,5,6]}'),
  (4,  'Carla Díaz',    'east',  DATE '2026-01-09',  NULL,  'gizmo',  NULL,    '{"channel":"web","items":[]}'),
  (5,  'Bob Okafor',    'south', DATE '2026-01-12',  45.25, 'gadget', 'c',     '{"channel":"phone","items":[7]}'),
  (6,  'Dev Patel',     'west',  DATE '2026-01-12', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (7,  'Carla Diaz',    'east',  DATE '2026-01-20',  99.99, 'gizmo',  'b|c',   '{"channel":"shop","items":[10]}'),
  (8,  'Dev Patel',     'west',  DATE '2026-02-02', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (9,  'Eve Larsen',    NULL,    DATE '2026-02-03',  15.00, 'gadget', NULL,    '{"channel":"web","items":[11]}'),
  (10, 'Alice Ng',      'north', DATE '2026-02-10', 220.00, 'gizmo',  'a|b|c', '{"channel":"phone","items":[12,13]}')
) t(order_id, customer, region, order_date, amount, product, tags, meta);

Then run the statement:

SQL
COPY (SELECT order_id, customer, order_date, amount FROM orders) TO '<your_scratch_dir>/semi.csv' (DELIMITER ';', QUOTE '"', FORCE_QUOTE (customer), HEADER);
COPY (SELECT * FROM (VALUES (1, DATE '2026-01-03', 2.5), (2, DATE '2026-01-04', 3.5), (3, DATE '2026-01-05', 4.5)) t(a, b, c))
  TO '<your_scratch_dir>/tabbed.csv' (DELIMITER '\t', HEADER false);
CREATE TEMP TABLE result AS
SELECT 'semi' AS file, Delimiter AS delim, Quote AS quote, HasHeader AS has_header, list_transform(Columns, lambda c: c.name) AS names, list_transform(Columns, lambda c: c.type) AS types FROM sniff_csv('<your_scratch_dir>/semi.csv')
UNION ALL
SELECT 'tabbed', Delimiter, Quote, HasHeader, list_transform(Columns, lambda c: c.name), list_transform(Columns, lambda c: c.type) FROM sniff_csv('<your_scratch_dir>/tabbed.csv');
SELECT * FROM result ORDER BY file;
SELECT CASE WHEN ((SELECT count(*) FROM result) = 2 AND (SELECT delim = ';' AND quote = '"' AND has_header AND names = ['order_id', 'customer', 'order_date', 'amount'] AND types = ['BIGINT', 'VARCHAR', 'DATE', 'DOUBLE'] FROM result WHERE file = 'semi') AND (SELECT delim = '	' AND NOT has_header AND types = ['BIGINT', 'DATE', 'DOUBLE'] FROM result WHERE file = 'tabbed')) THEN 'as expected' ELSE error('sniffing did not report one row per file with the semicolon file delimiter, quote, header and types and the tab file lack of a header') END AS verdict;

Replace <your_scratch_dir> with a directory of your own that you can write to before running the statement.

  • The statement writes semi.csv and tabbed.csv itself, in <your_scratch_dir>.
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “sniffing did not report one row per file with the semicolon file delimiter, quote, header and types and the tab file lack of a header”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

DuckDB (1)
  • sniff_csvread 2026-09-27

    sniff_csv table function

Background

In DuckDB, sniff_csv runs the sniffer for comma-separated values (CSV) files as a separate step, returning the CSV properties it detects as a single-row table.[1][2] DuckDB's CSV sniffer detects a file's dialect (delimiter, quoting rule and escape), the type of each column, and whether the file has a header row.[1] In Python, the csv module's Sniffer class is used to deduce the format of a CSV file from a sample of its text.[3]

When to use it

DuckDB detects a CSV file's settings because CSV files are not self-describing and come in many different dialects.[1] DuckDB lists, for each column of the sniff_csv result, the corresponding read_csv parameter, so the sniffed values can be passed directly to read_csv.[1] In DuckDB, the Prompt column of the sniff_csv result contains a command with the configurations the sniffer detected.[1] Sniff a file once, check what it reports, and then pin those settings in the read, so the next delivery is read the same way rather than guessed afresh. (Meridian's view)

DuckDB's detection works on a sample of the file, 20,480 rows by default, and setting sample_size to -1 reads the entire file for sampling.[1] DuckDB cannot detect a header correctly when every column is of type VARCHAR, and in that case assumes the file has a header.[1] A date such as 01-02-2000 is ambiguous to DuckDB's sniffer, and when the data does not resolve it the sniffer falls back on a list of preferred date formats.[1] In Python, Sniffer.has_header is a rough heuristic and may produce both false positives and negatives.[3]

The method

DuckDB's dialect detection parses the samples with each candidate dialect and picks the one giving a consistent number of columns for each row and the highest number of columns.[1] The delimiters DuckDB considers for automatic dialect detection are the comma, the vertical bar, the semicolon and the tab.[1] DuckDB's header detection checks whether the candidate header row deviates from the other rows in the file in terms of types.[1] In DuckDB, any parameter passed to sniff_csv is treated as fixed, and the sniffer detects only the properties not specified.[1]

In Python, Sniffer.sniff analyses a sample and returns a Dialect subclass reflecting the parameters found, and when several delimiters fit the sample equally well, those in the preferred attribute win in order.[3] Python's Sniffer.has_header samples twenty-one rows after the header and judges each column by whether its values are numeric or differ in length from the putative header.[3]

Sources

  1. [1]CSV Auto Detection – DuckDBdocumentation
  2. [2]Comma-separated values - Wikipediasecondary source
  3. [3]csv — CSV file reading and writing — Python 3.15.2 documentationdocumentation