CSVCheck

“Expected N fields, saw M”: fixing CSV import errors in pandas, PostgreSQL and MySQL

Updated October 17, 2026

Every CSV importer has its own wording for the same problem:

# pandas
ParserError: Error tokenizing data. C error: Expected 3 fields in line 4, saw 5

# PostgreSQL COPY
ERROR:  extra data after last expected column
ERROR:  missing data for column "email"

# MySQL LOAD DATA (warnings 1262 / 1261)
Row 3 was truncated; it contained more data than there were input columns
Row 2 doesn't contain data for all columns

Short answer: one or more rows split into a different number of fields than the header. Almost always the cause is a comma, quote or line break inside a value that isn’t quoted properly. Find the rows (the line number in the error is your starting point), fix the quoting, and import again. Skipping the bad rows makes the error go away, but you lose data.

What actually breaks

A CSV row is split on the delimiter, except inside double quotes. So "Main St, Apt 4" is one field, but Main St, Apt 4 without quotes is two. The usual culprits:

In the fileWhat the parser sees
3,Main St, Apt 4,BostonA comma inside an unquoted value: one extra field.
4,TV 55" screen,largeA stray quote. Some parsers keep it, some start a quoted field that never ends.
5,"Blue mug,8.99An opening quote with no closing quote: the rest of the file becomes one field.
6,"say "hi"",okQuotes inside a quoted value must be doubled: "say ""hi""".
A line break inside an unquoted valueThe row is cut in two: one short row, then another short row.
1;Ann;ann@x.com read with a comma delimiterThe wrong delimiter: one field per row, or random counts if some values contain commas. See comma vs semicolon.

An unclosed quote is the sneakiest one: the error often points to a line far below the real problem, or the importer complains about a giant field (Python’s csv module raises field larger than field limit (131072); pandas says EOF inside string starting at row N; Postgres says unterminated CSV quoted field).

Find the bad rows

The line number in the error is the first row the parser couldn’t handle, counting the header as line 1 in pandas. Open the file in a text editor (not Excel, which hides quotes) and look at that line and the one above it. To list every row with the wrong field count in one go, with Python:

import csv

with open("data.csv", newline="", encoding="utf-8") as f:
    reader = csv.reader(f)
    header = next(reader)
    for row in reader:
        if len(row) != len(header):
            print(reader.line_num, len(row), row[:3])

Or drop the file into CSVCheck: it lists every row with the wrong column count, unclosed or stray quotes with their line numbers, and runs in your browser.

Fix the file

  • Values with commas, quotes or line breaks must be wrapped in double quotes, with inner quotes doubled. If you generate the file in code, use a real CSV writer (Python’s csv.writer, pandas to_csv, Postgres COPY … TO … CSV) instead of joining strings with commas.
  • A one-off file: open it in Google Sheets or LibreOffice and export it again as CSV. Both quote values properly on export. Check the broken rows first, because they were already split wrongly on import.
  • Wrong delimiter: tell the importer which one the file uses (below).

Importer options that help

pandas

import pandas as pd

pd.read_csv("data.csv", sep=";")                 # file uses semicolons
pd.read_csv("data.csv", sep=None, engine="python")  # let pandas sniff the delimiter
pd.read_csv("data.csv", on_bad_lines="warn")     # report bad rows and skip them (pandas 1.3+)
pd.read_csv("data.csv", encoding="cp1252")       # Excel/Windows file that isn't UTF-8

Careful: pandas only errors on rows with too many fields. Rows with too few are filled with NaN silently, so a clean import doesn’t mean a clean file.

PostgreSQL

COPY customers FROM '/path/data.csv'
  WITH (FORMAT csv, HEADER true, DELIMITER ';', ENCODING 'WIN1252');

Use \copy in psql for a file on your own machine. COPY stops at the first bad row and imports nothing, which is safe but means fixing errors one by one, so validate the whole file first.

MySQL

LOAD DATA LOCAL INFILE 'data.csv' INTO TABLE customers
  CHARACTER SET utf8mb4
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  IGNORE 1 LINES;
SHOW WARNINGS;

MySQL often imports broken rows anyway and only reports them as warnings (truncated or padded rows), so always run SHOW WARNINGS afterwards. If the file came from Windows, use LINES TERMINATED BY '\r\n'.

Check before you import

CSVCheck shows every structural problem at once (column counts, quotes, encoding, delimiter, empty and duplicate rows) so you fix the file in one pass instead of one error at a time. Nothing is uploaded.

More CSV guides