Acquire / file · All operations

Read a file of any format

One reader that picks the format from the file.

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

The reader is chosen from the file name

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>/auto.parquet'; COPY orders TO '<your_scratch_dir>/auto.csv';
CREATE TEMP TABLE result AS
SELECT (SELECT count(*) FROM '<your_scratch_dir>/auto.parquet') AS from_parquet, (SELECT count(*) FROM '<your_scratch_dir>/auto.csv') AS from_csv;
SELECT * FROM result;
SELECT CASE WHEN ((SELECT from_parquet = 10 AND from_csv = 10 FROM result)) THEN 'as expected' ELSE error('a file read by name alone did not return 10 rows') 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 auto.csv and auto.parquet 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: “a file read by name alone did not return 10 rows”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

What other tools call it

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

Alteryx Designer (2)
  • Dynamic Inputread 2026-09-27

    Read files or run database queries chosen at run time from incoming records

  • Input Dataread 2026-09-27

    Read from a file or a database; one reader covering every supported file format and data source

Tableau Prep (1)
  • Inputread 2026-09-27

    First step of a flow: read a table from a file, server, extract or published data source

Background

In DuckDB, a query can read a CSV, Parquet or JSON file by naming the file alone, as in SELECT * FROM 'test.parquet'.[1] In DuckDB, a file whose name ends in .parquet is read as Parquet without calling the read_parquet function, since the system infers the format from the name.[2] Alteryx Designer's Input Data tool lists the file types it reads against their file extensions, from .csv and .xlsx to .sas7bdat.[3]

When to use it

Reach for a reader that picks the format when the file's name is honest about its contents and the reader's defaults suit the file. (Meridian's view) In DuckDB, a Parquet file whose name does not end in .parquet, such as test.parq, is read with the read_parquet function instead.[2] Since there is no standardised list of filename extensions, more than one format can use the same extension, which can confuse both users and applications.[4] When a file's format is indicated by its extension, users and applications can be tricked into treating the file as a different format simply by renaming it.[4] Name the format yourself when files arrive with missing, borrowed or misleading extensions. (Meridian's view)

DuckDB's excel extension reads a .xlsx file when a query selects from it directly, but .xls files are not supported.[5] In DuckDB, the read_xlsx function is used instead when an Excel import needs options set, such as the range of cells to read.[5] In Alteryx Designer, a file type the Input Data tool cannot read natively opens a Resolve File Type window, which offers to read the file as a built-in type, as a fixed width text file or as a delimited text file.[3]

In DuckDB, a CSV file read by naming it alone is read with its options inferred automatically.[6] DuckDB's CSV reader uses a CSV sniffer to infer how to read a file, since CSV files are not self-describing and come in many different dialects.[7] In DuckDB, the sniffer's choices can be overridden one option at a time, which is useful when it chooses the wrong delimiter.[7] In Tableau Prep, the first rows of a CSV file are read as the field header row by default, though Excel and text files commonly carry meta-information in their first few rows.[8] When a file read by name alone comes back with the wrong delimiter, header or types, name the reader and set those options rather than trusting the guess. (Meridian's view)

The method

One popular way to indicate a file's format, used by many operating systems, is a suffix of the file name known as the extension, such as .html or .gif.[4] In DuckDB, the compression of a CSV file is detected from the file extension by default, so t.csv.gz is read with gzip.[6] In DuckDB, the json and excel extensions that read JSON and .xlsx files are loaded automatically on first use.[9][5]

A file's format may instead be identified from inside the file, where a small datum at the start that only indicates the format is often called a magic number.[4] Identifying a format by its magic number offers better guarantees that it will be identified correctly, but the data must be read from the file itself.[4] Methods based on the file name need to check only one piece of data against a sorted index, while a magic number test must effectively check each file against every possibility in its database of magic numbers.[4] Many file types, especially plain-text files, are harder to identify by a magic number.[4] A valid magic number does not guarantee that a file is not corrupt or is of the correct type.[4]

Compared with the alternatives

In DuckDB, calling a reader function such as read_csv, rather than naming the file alone, lets a query pass options to the reader.[1]

Sources

  1. [1]Importing Data – DuckDBdocumentation
  2. [2]Reading and Writing Parquet Files – DuckDBdocumentation
  3. [3]Input Data Tooldocumentation
  4. [4]File format - Wikipediasecondary source
  5. [5]Excel Extension – DuckDBdocumentation
  6. [6]CSV Import – DuckDBdocumentation
  7. [7]CSV Auto Detection – DuckDBdocumentation
  8. [8]Configure your Data Set - Tableaudocumentation
  9. [9]JSON Overview – DuckDBdocumentation