Does a CSV File Need a Header Row and What If It Has None

Whether the first line of a CSV is a header or the first record is a fact about the file that the file does not contain. Every reader has to decide, some by asking you and some by guessing, and the guess fails in the least visible way possible: a headerless file loses its first row, or a header ends up as a row of data. This article tests four small files, shows the explicit setting in the tools I use, and covers the duplicate and empty column names that headers bring with them. It is one of the rules in the CSV format guide that readers handle least consistently.

What you need before you start

Know your file, or at least look at its first line. head -1 file.csv is enough to see whether you are dealing with names or values. If you do not know which tool will read the file, decide the convention for the data and write it in the documentation, as the article on data contracts suggests. The rest of this article assumes the file is small enough to look at by eye.

The format does not need one, and does not say

RFC 4180 says a header line may appear as the first line, with the same format as a record, and that whether it is there "should be indicated via the optional header parameter" of the media type. Almost no file travels with a media type parameter. A file on disk has a name and bytes, so the signal is lost. That is the reason the RFC's own registration says implementors choosing not to use the parameter "must make their own decisions".

In practice I treat a header as required when I write and optional when I read. A file without one is a file whose column order lives in somebody's head, and that is the source of the oldest bug in the cluster: the export gets a new column in the middle and nothing complains.

Four files that make readers disagree

The test is two questions asked of two kinds of data. Does the file have a header or not, and are all the columns text or is there a numeric column that gives the header away?

The easy files have a numeric column: a header id above numbers gives it away, and a first row of numbers is clearly data. The hard files are the all-text ones, because a row of words looks like a header and also looks like a row of words.

Python's csv.Sniffer.has_header is the standard library's attempt. It returned False for every one of the four, including the two with headers:

python - <<'PY'
import csv
for f in ("h-typed.csv", "h-text.csv", "n-typed.csv", "n-text.csv"):
    sample = open(f).read()
    print(f"{f:12} Sniffer.has_header -> {csv.Sniffer().has_header(sample)}")
PY

h-typed.csv  Sniffer.has_header -> False
h-text.csv   Sniffer.has_header -> False
n-typed.csv  Sniffer.has_header -> False
n-text.csv   Sniffer.has_header -> False

The documentation describes a heuristic that inspects the rows after the first for numeric values or differing string lengths, and with two or three rows there is not enough to go on. It is wrong in the dangerous direction, since it reports no header on files that have one.

DuckDB's sniff_csv did better, three right out of four, and failed on the hardest case. The all-text file with no header was reported as having one, and its first row, Ana,Lyon,France, became the column names:

for f in h-typed.csv h-text.csv n-typed.csv n-text.csv; do
  printf '%-12s' "$f"; duckdb -list -noheader -c "SELECT 'HasHeader=' || HasHeader || '  columns=' || list_transform(Columns, lambda c: c.name)::VARCHAR FROM sniff_csv('$f')"
done

h-typed.csv HasHeader=true  columns=[id, name, city]
h-text.csv  HasHeader=true  columns=[name, city, country]
n-typed.csv HasHeader=false  columns=[column0, column1, column2]
n-text.csv  HasHeader=true  columns=[Ana, Lyon, France]

That result is not a bug in the sniffer. A row of three words above two rows of three words has no signal either way. It is a reminder that a sniffer on a text-only file is guessing, however good the guess usually is.

pandas does not guess at all. With the default header="infer" and no names, it treats the first row as the header, always. On the headerless numeric file, that meant the column labels became 1, Ana and Lyon, and the first record vanished:

python - <<'PY'
import pandas as pd
print(pd.read_csv("n-typed.csv").columns.tolist(), "<- pandas default eats the first data row")
print(pd.read_csv("n-typed.csv", header=None, names=["id", "name", "city"]).shape, "<- header=None with names")
PY

['1', 'Ana', 'Lyon'] <- pandas default eats the first data row
(2, 3) <- header=None with names

Telling each tool explicitly

The fix in all three of the commonly used readers is to state the answer, along with the column names if the file has none. Every one of these returns the right table for n-text.csv, the file DuckDB got wrong.

In DuckDB, set header=false and give names:

duckdb -box -c "SELECT * FROM read_csv('n-text.csv', header=false, names=['name','city','country'])"

┌──────┬──────┬─────────┐
│ name │ city │ country │
├──────┼──────┼─────────┤
│ Ana  │ Lyon │ France  │
│ Bo   │ Oslo │ Norway  │
└──────┴──────┴─────────┘

In pandas, pass header=None with names:

python -c "
import pandas as pd
df = pd.read_csv('n-text.csv', header=None, names=['name', 'city', 'country'])
print(df)"

  name  city country
0  Ana  Lyon  France
1   Bo  Oslo  Norway

In SQLite, create the table with the columns you want first, so that .import does not treat the first line as column names. When the table already exists, every line is data, and --skip 1 is how you drop a header you do not want:

sqlite3 :memory: "CREATE TABLE t(name, city, country)" ".mode csv" ".import --skip 0 n-text.csv t" ".mode box" "SELECT * FROM t;" "SELECT count(*) AS rows FROM t;"

╭──────┬──────┬─────────╮
│ name │ city │ country │
╞══════╪══════╪═════════╡
│ Ana  │ Lyon │ France  │
│ Bo   │ Oslo │ Norway  │
╰──────┴──────┴─────────╯
╭──────╮
│ rows │
╞══════╡
│    2 │
╰──────╯

For the two server databases, the header is part of the load command. PostgreSQL's COPY has HEADER true to skip the first line and HEADER match to check it, which is the form I prefer for files I do not trust. MySQL uses IGNORE 1 LINES, which skips without checking.

Make the load check the header

HEADER match is a small feature with a large payoff. It compares the file's header with the target table's column names, in order, and aborts if they differ. Loaded into a table whose columns are name, city, country, a file with the matching header went in:

 count 
-------
     2
(1 row)

and the same command pointed at a file with a different header stopped before inserting anything:

CONTEXT:  COPY cities, line 1: "id,name,city"

This protects against the quiet case where an export starts with a new column or the columns get reordered. Without it, the data would land in the wrong columns, as long as the types happened to fit. For text columns they always do. The PostgreSQL loading guide and the article on loading with a header row build on it.

Duplicate and empty column names

A header is also a list of names, and CSV does not require them to be unique or non-empty. I made a file with name twice and an empty name at the end:

id,name,name,
1,Ana,Lyon,x

Each reader repairs it differently, and one does not repair it at all. pandas renames the second name to name.1 and the blank to Unnamed: 3. DuckDB uses name_1 and column3. SQLite prints a notice and renames to name_2, name_3 and ?:

['id', 'name', 'name.1', 'Unnamed: 3']

┌────┬──────┬────────┬─────────┐
│ id │ name │ name_1 │ column3 │
├────┼──────┼────────┼─────────┤
│ 1  │ Ana  │ Lyon   │ x       │
└────┴──────┴────────┴─────────┘

Columns renamed during .import dup-header.csv due to duplicates:
"name" to "name_2",
"name" to "name_3"
CREATE TABLE "t"(
"id", "name_2", "name_3", "?");
1,Ana,Lyon,x

Python's csv.DictReader does not rename anything. It builds a dictionary per row, so the second name overwrites the first. The record 1,Ana,Lyon,x came back as name: Lyon with an empty key, and Ana was lost without any message:

[{'id': '1', 'name': 'Lyon', '': 'x'}]

That is the one I would lose sleep over. PostgreSQL, with HEADER match, refused the file because the third field did not match the table:

DROP TABLE
CREATE TABLE
ERROR:  column name mismatch in header line field 3: got "name", expected "city"
CONTEXT:  COPY dup, line 1: "id,name,name,"

When names matter, rename the columns yourself on the way in, and check uniqueness before the load. A one line check on the header, len(set(names)) == len(names), is enough to catch the problem in a script. Header whitespace and case deserve the same attention: " Name" and "name" are different columns to a program and the same to a person.

How to tell it worked

Three quick checks. First, the number of rows in the table should equal the number of records in the file, minus one if there is a header. Second, print the first row of data and confirm it is a record and not the header. Third, look at the column types: a numeric column loaded as text often means the header ended up as a data row, because the word id cannot be an integer.

When the header is not on line one

Exports from reporting tools and spreadsheets often put a title, a date range or a blank line above the header, or repeat the header every few hundred rows when files are concatenated. None of the explicit settings above handle that. You need skiprows in pandas, skip in DuckDB or a pre-processing step to cut the preamble, and a check afterwards that no data row equals the header. Two header rows, one with groups and one with names, are a spreadsheet idea that CSV cannot carry; flatten them into single names like sales_q1 first. If the file is really a worksheet, the Excel articles are the better starting point than any CSV setting.