Publish / file · All operations

Write a CSV or delimited file

Write CSV, TSV, fixed-width.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

Shape of the result
the table sent out, with nothing new to look at
Level
one clause of SQL

The DuckDB step

Write CSV

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 * FROM orders) TO '<your_scratch_dir>/out.csv' (HEADER, DELIMITER ',');
CREATE TEMP TABLE result AS
SELECT count(*) AS n, sum(amount) AS total FROM read_csv('<your_scratch_dir>/out.csv');
SELECT * FROM result;
SELECT CASE WHEN ((SELECT n = 10 AND total = 1900.74 FROM result)) THEN 'as expected' ELSE error('the CSV written does not read back as 10 rows totalling 1900.74') 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 out.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 written does not read back as 10 rows totalling 1900.74”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

Variants

Write a delimited text file

Write rows as text with a separator between fields, such as commas, tabs or pipes.

Write a fixed-width file

Write rows as text where each field takes a set number of characters, padded to its width.

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 (2)
  • Data Insert: CSVread 2026-09-27

    Write results as CSV

  • write_csvread 2026-09-27

    DataFrame method: Execute and write the results to CSV files

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

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

  • Neo4j generate CSVsread 2026-09-27

    Write node and relationship CSV files for the Neo4j import tool

  • Spark file outputread 2026-09-27

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

  • Text File Outputread 2026-09-27

    Write rows to delimited or fixed-width text files, optionally split or compressed

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

    Write CSV files and folders to Amazon S3

Azure Data Factory (1)
Duckle (2)
  • CSVread 2026-09-25

    write CSV with COPY

  • TSVread 2026-09-25

    write tab-separated text with COPY

EasyMorph (1)
KNIME Analytics Platform (1)
  • CSV Writerread 2026-09-27

    Write a table to a CSV file

OpenRefine (2)
Pentaho Data Integration (4)
Polars (3)
RapidMiner Studio (1)
  • Write CSVread 2026-09-27

    Write a table to a CSV file

Tableau Prep (1)
Talend (4)
VisiData (6)
  • csv (saver)read 2026-09-23

    Save as comma-separated values

  • fixed (saver)read 2026-09-23

    Save as fixed-width text

  • lsv (saver)read 2026-09-23

    Save as key-value line-separated values

  • psv (saver)read 2026-09-23

    Save as pipe-separated values

  • tsv (saver)read 2026-09-23

    Save as tab-separated values

  • usv (saver)read 2026-09-23

    Save as Unicode-separated values

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

    Write examples or predictions as CSV

Background

Writing a comma-separated values (CSV) or other delimited file stores a table as plain text, one record per line, with a delimiter between the fields.[1][2] In a fixed-width text file, each field instead has a fixed length, with extra spaces added as needed.[3] CSV is often used to move tabular data between programs, such as from a database to a spreadsheet.[1]

When to use it

Most database systems can export as CSV and most spreadsheet programs can import CSV, so CSV serves as an intermediate format between them.[1] CSV carries no type information to distinguish the string 1 from the number 1.[1] CSV also makes no distinction between null and empty values, and where that distinction is needed other formats are preferred.[1] Reach for a delimited file when the reader is a person or a program you do not control, and keep a typed format for passing data between tools that can read one. (Meridian's view)

DuckDB's COPY ... TO statement writes the contents of a table, or the result of a query, to an external file such as a CSV file.[4] DuckDB's COPY ... TO has a NULLSTR option, the string written to represent a NULL value.[4] In Azure Data Factory's delimited text format, the nullValue property gives the string representation of a null value, and its default is an empty string.[5] Where a reader must tell a missing value from an empty one, write missing values as a marker that cannot appear in the data, and send that marker with the file. (Meridian's view)

Request for Comments 4180 (RFC 4180), published in October 2005, allows an optional header line as the first line of a CSV file, in the same format as the records.[6] A CSV file gives no way to test whether its first line is in fact a header, so care is required when importing one.[1] In DuckDB's COPY ... TO, the HEADER option, which sets whether a header line is written, defaults to true.[4] In Azure Data Factory's delimited text format, the firstRowAsHeader property, which makes the first row a header line of column names, defaults to false.[5] Set the header option explicitly rather than relying on a writer's default. (Meridian's view)

Delimiter collision happens when the delimiter character is embedded in a field value without accommodation, so one logical value is split into two.[2] A commonly used way to avoid delimiter collision is to enclose a field value in double quotes.[2] Programs reading CSV may not support line terminator characters within a field, even when the field is properly quoted.[1] When the file's reader is unknown, look for line breaks in text columns before writing, and decide whether to keep them quoted or replace them. (Meridian's view)

CSV is not limited to a particular character encoding, but is commonly used with UTF-8, particularly because CSV gives no way to indicate the encoding.[1] Write UTF-8 and say so wherever the file is described. (Meridian's view)

Programs that work with CSV may limit the number of rows a CSV file can have, and Microsoft Excel's limit is 1,048,576 rows.[1] Before writing a file meant for a spreadsheet, check its row count against that program's limit. (Meridian's view)

DuckDB's COPY ... TO overwrites the destination file if it already exists, without raising an error.[4] Write to a new path, or check the target first, when an older file there still matters. (Meridian's view)

In a fixed-width format with predefined lengths, each field is limited to a maximum length.[3] In Apache Hop's Text File Output transform, short values are padded out to a field's set length, and values longer than that length are shortened.[7] Before writing a fixed-width file, compare each field's longest value with its width, so no value is cut short unseen. (Meridian's view)

The method

The CSV format predates personal computers by more than a decade.[1] IBM's Fortran (level H extended) compiler under OS/360 supported list-directed input and output, with commas between values, in 1972.[1] The term comma-separated value and the CSV abbreviation were in use by 1983.[1] Comma-separated value lists are easier to type, for example into punched cards, than data aligned in fixed columns.[1]

RFC 4180 documents the CSV format that seems to be followed by most implementations, since no formal specification existed when it was written.[6] RFC 4180 also registers text/csv as the media type for CSV files.[6] Under RFC 4180, each record sits on its own line, ended by a carriage return and line feed (CRLF), and the last record may or may not have an ending line break.[6] Under RFC 4180, fields are separated by commas, each line should contain the same number of fields, and spaces are part of a field and should not be ignored.[6] Under RFC 4180, fields containing line breaks, double quotes or commas should be enclosed in double quotes, and a double quote inside a quoted field is escaped by another double quote before it.[6]

In DuckDB's COPY ... TO, the DELIM option sets the character written to separate columns within each row, and defaults to a comma.[4] In DuckDB's COPY ... TO, the FORCE_QUOTE option lists the columns to always quote, even where quoting is not required.[4] In DuckDB's COPY ... TO, the NEW_LINE option sets the character written to separate each row, and its default is \n.[4] In Azure Data Factory's delimited text format, the escapeChar property, the character that escapes quotes inside a quoted value, defaults to a backslash.[5] Name the quote and escape characters wherever the file is handed over, so its reader need not guess them. (Meridian's view)

In delimiter-separated values (DSV), any character may separate field values, and the more commonly used include the comma, tab, colon, vertical bar (or pipe) and space.[2] The ASCII and Unicode character sets include control characters intended for use as delimiters, the File, Group, Record and Unit Separators, though their use in DSV data is relatively uncommon.[2] Tab-separated values (TSV) and CSV are two commonly used sub-categories of DSV, supported by many spreadsheet and statistical applications.[2]

In 2013 the World Wide Web Consortium (W3C) CSV on the Web working group began to specify technologies for higher interoperability of web applications using CSV or similar formats.[1] The W3C CSV on the Web working group completed its work in February 2016, with recommendations for modelling tabular data and enhancing CSV with metadata and semantics.[1] The Frictionless Data initiative provides a CSV Dialect Description Format for describing dialects of CSV, such as their field separator or quoting rules.[1]

Compared with the alternatives

DSV supports field values of any length, whereas a fixed-width format limits the length of each field value.[2] Finding field boundaries in a delimiter-separated file means finding the delimiters, a computational overhead that fixed-width formats do not need.[3] Fixed-width formats can lead to unnecessarily large files when fields tend to be shorter than the lengths reserved for them.[3]

The Tabular Data Package, from the Frictionless Data initiative, uses CSV as its main data transport format and adds basic type and schema metadata.[1] Testing the validity and canonical form of CSV is less well developed than for more precise formats such as Extensible Markup Language (XML) and Structured Query Language (SQL), which offer richer types and rules-based validation.[1] Keep data in a typed format while it moves between tools that read one, and write the delimited file at the edge, where people or unknown programs take it. (Meridian's view)

Sources

  1. [1]Comma-separated valuessecondary source
  2. [2]Delimiter-separated valuessecondary source
  3. [3]Flat-file databasesecondary source
  4. [4]COPY Statement – DuckDBdocumentation
  5. [5]Delimited text format in Azure Data Factory - Azure Data Factory & Azure Synapse | Microsoft Learndocumentation
  6. [6]Common Format and MIME Type for Comma-Separated Values (CSV) Filesstandard
  7. [7]Text File Output | Apache Hopdocumentation