Acquire / file · All operations

Read a CSV or delimited file

Read CSV, TSV, fixed-width, other delimited text.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

Shape of the result
a new table, from no input table
Level
one clause of SQL

The DuckDB step

Read a CSV with types sniffed

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 orders TO '<your_scratch_dir>/orders.csv' (HEADER);
CREATE TEMP TABLE result AS
SELECT count(*) AS n, count(amount) AS amounts, any_value(typeof(order_date)) AS date_type FROM read_csv('<your_scratch_dir>/orders.csv');
SELECT * FROM result;
SELECT CASE WHEN ((SELECT n = 10 AND amounts = 9 AND date_type = 'DATE' FROM result)) THEN 'as expected' ELSE error('the CSV did not read back as 10 rows, 9 amounts and a DATE column') 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 orders.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: “the CSV did not read back as 10 rows, 9 amounts and a DATE column”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

Variants

Read delimited text

Read text whose fields are split by a comma, tab, pipe or other separator into rows and columns.

Split lines with a pattern

Read a text file line by line and cut each line into fields with a regular expression.

Read in batches or as a stream

Read delimited text a batch at a time, or continuously as new data arrives.

Read each line as one field

Read a text file line by line, keeping each whole line as a single field.

What other tools call it

Meridian's classification; an independent blind rating agreed on 86.2% of 312 rows (exact class, version 2).

Apache DataFusion (1)
  • CSVread 2026-09-27

    Read CSV files as a table

Apache Hop (4)
  • Beam file inputread 2026-09-27

    Read delimited text files described by a file definition on the Beam engine

  • CSV file inputread 2026-09-27

    Read a delimited text file, with optional lazy conversion and parallel reading

  • Spark file inputread 2026-09-27

    Read CSV, Parquet, JSON, ORC or text files as a Spark Dataset

  • Text File Inputread 2026-09-27

    Read delimited or fixed-width text files, with headers, filters and error handling

Apache NiFi (1)
  • CSVReaderread 2026-09-27

    Read CSV data into records, one per row, with optional schema inference

AWS Glue (1)
  • CSV (read)read 2026-09-27

    Read CSV data from Amazon S3 and other supported connection types

Azure Data Factory (1)
Bruin (2)
  • Seed assets (family)read 2026-09-27

    15 platform types (bq.seed, duckdb.seed, …) loading a CSV, Parquet, JSON, JSONL, NDJSON or Avro file

  • ingestr: csv sourceread 2026-09-27

    Read a local CSV file as an ingestion source

dbt-utils (1)
  • seedread 2026-09-27

    Load a CSV file kept in the project into the warehouse as a table

DuckDB (3)
  • read_csvread 2026-09-27

    read_csv table function

  • read_csv_autoread 2026-09-27

    read_csv_auto table function

  • sniff_csvread 2026-09-27

    sniff_csv table function

Duckle (3)
  • CSVread 2026-09-25

    read delimited text with read_csv; declared types; bad rows to a reject port

  • Fixed-widthread 2026-09-25

    read fixed-width text as SUBSTR projections over one-column lines

  • TSVread 2026-09-25

    read tab-separated text with read_csv; bad rows to a reject port

EasyMorph (2)
KNIME Analytics Platform (4)
mlpack (4)
  • ArmaASCIIread 2026-09-27

    Load or save space-separated numeric text in Armadillo's arma_ascii format

  • CSVread 2026-09-27

    Load or save numeric and categorical data as .csv; a 3-column file loads as a sparse coordinate list

  • RawASCIIread 2026-09-27

    Load or save space-separated numeric text with no header

  • TSVread 2026-09-27

    Load or save numeric and categorical data as .tsv

OpenRefine (2)
Pentaho Data Integration (7)
Polars (4)
  • read_clipboardread 2026-09-27

    Read delimited text from the system clipboard through the CSV reader

  • read_csvread 2026-09-27

    Read a CSV file into a frame

  • read_csv_batchedread 2026-09-27

    Read a CSV file in batches through a reader object

  • scan_csvread 2026-09-27

    Lazily read one or many CSV files, by glob pattern

Power Query M (1)
  • Csv.Documentread 2026-09-27

    Read a CSV document into a table, with options for delimiter, columns, encoding and extra values

RapidMiner Studio (1)
  • Read CSVread 2026-09-27

    Read a CSV file into a table

SQLMesh (1)
  • SEEDread 2026-09-27

    Load a static CSV file in the project into a physical table

Talend (10)
  • tFileInputDelimitedread 2026-09-27

    Read a delimited file row by row into fields

  • tFileInputFullRowread 2026-09-27

    Read a file row by row, each whole row as one field

  • tFileInputMSDelimitedread 2026-09-27

    Read a delimited file holding several record structures, one output schema per structure

  • tFileInputMSPositionalread 2026-09-27

    Read a fixed-width file holding several record structures, one output schema per structure

  • tFileInputPositionalread 2026-09-27

    Read a fixed-width file row by row into fields by a position pattern

  • tFileInputRegexread 2026-09-27

    Read a file row by row, splitting each row into fields with a regular expression

  • tFileStreamInputDelimitedread 2026-09-27

    Read delimited data continuously, row by row, into fields in a Spark Streaming Job

  • tFileStreamInputFullRowread 2026-09-27

    Read newly created files row by row, each whole row as one field

  • tFileStreamInputPositionalread 2026-09-27

    Watch a directory and read new fixed-width files row by row into fields

  • tFileStreamInputRegexread 2026-09-27

    Watch a directory and read new files row by row, splitting fields with a regular expression

VisiData (6)
  • csv (loader)read 2026-09-23

    Load comma-separated values

  • fixed (loader)read 2026-09-23

    Load fixed-width text, columns detected from the first rows

  • lsv (loader)read 2026-09-23

    Load awk-like key-value line-separated values

  • psv (loader)read 2026-09-23

    Load pipe-separated values

  • tsv (loader)read 2026-09-23

    Load tab-separated values

  • usv (loader)read 2026-09-23

    Load Unicode-separated values

Yggdrasil Decision Forests (1)
  • csv: (read)read 2026-09-27

    Read RFC 4180 CSV by typed path, with sharding, globs and comma lists; no multi-dimensional columns

Background

A delimited text file stores a table as text, separating the fields of each row with a delimiter character such as a comma, tab or vertical bar.[1] Comma-separated values (CSV) and tab-separated values (TSV) are two commonly used kinds, supported by many spreadsheet and statistical applications.[1] A plain-text table can also be fixed-width, with each field given a fixed length and extra spaces added as needed.[2]

When to use it

CSV is often used to move tabular data between programs, and most database systems can export CSV while most spreadsheet programs can import it.[3] CSV carries no type information to tell the string 1 from the number 1.[3] CSV makes no distinction between null and empty values.[3] Treat the column types a reader settles on as its guess, and declare the types of the columns that matter yourself. (Meridian's view)

Because there is no single specification, there are considerable differences among CSV implementations.[4] A CSV file cannot be parsed correctly by splitting the text into lines and then splitting each line at commas, because a quoted field can hold commas and line breaks.[3][4] Common problems with CSV include programs that confuse a header line with data, and programs that do not support line breaks inside a quoted field.[3] Read a delimited file with a real CSV parser rather than a string split, even when the file looks simple. (Meridian's view)

In DuckDB, a structural error such as a line with too many or too few columns stops a CSV read by default, and the ignore_errors option skips such lines instead.[5] DuckDB can also keep faulty lines in rejects tables, which record each faulty line and the error found on it.[5] Prefer keeping rejected lines where they can be counted over skipping them silently, so a short load is noticed. (Meridian's view)

The method

Request for Comments 4180 (RFC 4180), published in October 2005, documents the CSV format that seems to be followed by most implementations.[4] Under RFC 4180, each record sits on its own line, fields are separated by commas, and each line should contain the same number of fields.[4] RFC 4180 says a field containing a line break, a double quote or a comma should be enclosed in double quotes, and a double quote inside a quoted field is escaped by another double quote.[4] RFC 4180 states that it does not specify an Internet standard of any kind.[4]

Any character may separate the fields of delimiter-separated values, and the more commonly used include the comma, tab, colon, vertical bar and space.[1] Delimiter collision happens when the delimiter character is embedded in a field value without accommodation for it, so one logical value is split into two.[1] Enclosing a field value in double quotes is a commonly used way to avoid delimiter collision.[1]

In DuckDB, read_csv detects each column's type by trying to convert its values to candidate types and choosing the highest-priority candidate that remains, with VARCHAR as the fallback.[6] DuckDB's all_varchar option turns type detection off, so every column remains VARCHAR as it occurs in the file.[6] DuckDB's columns option gives the column names and types as a struct and disables auto detection of the schema.[7]

The CSV format predates personal computers by more than a decade, and in 1972 an IBM Fortran compiler supported list-directed input and output with commas between values.[3] The term comma-separated value and the CSV abbreviation were in use by 1983.[3]

Compared with the alternatives

Delimiter-separated values support field values of any length, whereas fixed-width text limits the length of each field value.[1] CSV does not support more complex data relations, and applications that need them, or a distinction between null and empty values, prefer other formats.[3]

Sources

  1. [1]Delimiter-separated values - Wikipediasecondary source
  2. [2]Flat-file database - Wikipediasecondary source
  3. [3]Comma-separated values - Wikipediasecondary source
  4. [4]Common Format and MIME Type for Comma-Separated Values (CSV) Filesstandard
  5. [5]Reading Faulty CSV Files – DuckDBdocumentation
  6. [6]CSV Auto Detection – DuckDBdocumentation
  7. [7]CSV Import – DuckDBdocumentation

Used in

7 Protocols use this operation: