Shape / columns · All operations

Demote headers to a row

A variant of Promote a row to headers

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

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Demote the header line to a first data row and name the columns by default

SQL
COPY (SELECT 1 AS id, 'a' AS name UNION ALL SELECT 2, 'b') TO '<your_scratch_dir>/named.csv' (HEADER);
CREATE TEMP TABLE result AS
SELECT * FROM read_csv('<your_scratch_dir>/named.csv', header = false, all_varchar = true);
SELECT * FROM result;
SELECT CASE WHEN ((SELECT list(column_name) FROM (DESCRIBE result)) = ['column0', 'column1'] AND (SELECT count(*) FROM result) = 3 AND (SELECT list(column0 ORDER BY rowid) FROM result) = ['id', '1', '2'] AND (SELECT list(column1 ORDER BY rowid) FROM result) = ['name', 'a', 'b']) THEN 'as expected' ELSE error('the header line did not become the first data row under the default names column0 and column1, ahead of the two data 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 named.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 header line did not become the first data row under the default names column0 and column1, ahead of the two data rows”.
  • 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).

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

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

Background

In Power Query, Table.DemoteHeaders demotes the column headers, or column names, to the first row of values.[1] In Power Query, Table.DemoteHeaders names the demoted columns Column1, Column2 and so on.[1]

When to use it

Demote when the names a table carries are not the names you want but are worth keeping as data, or when the real names sit further down and the first line must be handled like any other row. (Meridian's view) After demoting, treat every column as text until the row of old names has been used or removed, since a column mixing a name with its values is not ready for numbers or dates. (Meridian's view) DuckDB's all_varchar option skips type detection and assumes every column is of type VARCHAR.[2]

The method

In DuckDB, the columns of a CSV file read without a header are named column0, column1, column2 and so on.[2] In pandas, a file read with header=None has no row interpreted as column labels, and its columns are labelled by integer position unless names are passed.[3]

Compared with the alternatives

In Power Query, Table.PromoteHeaders turns the first row of values into the column headers, while Table.DemoteHeaders turns the headers into the first row of values.[4][1]

Sources

  1. [1]Table.DemoteHeaders - PowerQuery M | Microsoft Learndocumentation
  2. [2]CSV Import – DuckDBdocumentation
  3. [3]pandas.read_csv — pandas 3.1.0 documentationdocumentation
  4. [4]Table.PromoteHeaders - PowerQuery M | Microsoft Learndocumentation