Shape / columns · All operations

Promote a row to headers

Promote a row to headers, or demote headers to a row.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

Shape of the result
one row out per row in, with columns added or changed
Level
one clause of SQL

The DuckDB step

Promote the second line of a file to headers

SQL
COPY (SELECT 'exported 2026-09-27' AS line UNION ALL SELECT 'id,name' UNION ALL SELECT '1,a' UNION ALL SELECT '2,b') TO '<your_scratch_dir>/preamble.csv' (HEADER false, QUOTE '');
CREATE TEMP TABLE result AS
SELECT * FROM read_csv('<your_scratch_dir>/preamble.csv', skip = 1, header = true);
SELECT * FROM result;
SELECT CASE WHEN ((SELECT list(column_name) = ['id', 'name'] FROM (DESCRIBE result)) AND (SELECT count(*) = 2 AND list(name ORDER BY id) = ['a', 'b'] FROM result)) THEN 'as expected' ELSE error('the second line did not become the column names') 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 preamble.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 second line did not become the column names”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

Variants

Use the first row as headers

Take the column names from the first data row and remove that row.

What other tools call it

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

Power Query M (2)
  • Table.DemoteHeadersread 2026-09-27

    Move the column names into the first data row and give columns default names

  • Table.PromoteHeadersread 2026-09-27

    Use the first data row as the column names and remove it from the rows

Background

In Power Query, Table.PromoteHeaders promotes the first row of values in a table as the new column headers, or column names.[1] In a comma-separated values (CSV) file, the first line may be an optional header line holding names for the fields.[2] Power Query also has Table.DemoteHeaders, which moves the column names down into the first row of values.[3]

When to use it

DuckDB's skip option sets a number of lines to skip at the start of each file.[4] When an export puts a title or a date above the names, skip those lines first and then promote the line that holds the names. (Meridian's view) In pandas, header=0 denotes the first line of data rather than the first line of the file, because only non-blank, non-commented lines are counted when skip_blank_lines is on.[5] Count the lines a reader actually sees, not the lines in a text editor, before choosing which row to promote. (Meridian's view)

The method

RFC 4180, the October 2005 memo that registered the text/csv type, describes the optional header line as having the same format as normal record lines.[2] RFC 4180 says the header line should contain the same number of fields as the records in the rest of the file.[2] RFC 4180 says the presence or absence of the header line should be indicated by an optional header parameter of the text/csv type.[2] RFC 4180 says implementations that do not use the header parameter must make their own decision as to whether the header is absent or present.[2]

DuckDB's CSV sniffer detects a header by checking whether the candidate header row deviates from the other rows in types, as an all-VARCHAR row does above a column of dates.[6] In DuckDB, a CSV file with a header has its columns named from that header, and the columns of a file without one are named column0, column1 and so on.[4][6] In pandas, header can also take a list of line indices whose rows are combined into a MultiIndex on the columns, and unlisted rows between them are skipped.[5]

Compared with the alternatives

In pandas, names supplies a list of column labels, and a file that has a header row should then be read with header=0 so that the names override it.[5] DuckDB's read_csv takes a names option, a list of column names.[4] Promote when the file's own names are the ones you want, and supply names when they are not, discarding the header row rather than leaving it among the data. (Meridian's view)

Sources

  1. [1]Table.PromoteHeaders - PowerQuery M | Microsoft Learndocumentation
  2. [2]RFC 4180: Common Format and MIME Type for Comma-Separated Values (CSV) Filesstandard
  3. [3]Table.DemoteHeaders - PowerQuery M | Microsoft Learndocumentation
  4. [4]CSV Import – DuckDBdocumentation
  5. [5]pandas.read_csv — pandas 3.1.0 documentationdocumentation
  6. [6]CSV Auto Detection – DuckDBdocumentation