CSV import error: unclosed quote or wrong field count
Your import stops with a message such as unclosed quote, unterminated quoted field or expected 5 fields but found 6 (or 4). The file may have 50,000 good lines and one bad one. Finding that line is most of the work, and the rules below tell you what to look for.
The rules (RFC 4180, simplified)
RFC 4180 is the closest thing CSV has to a standard. The parts that cause most errors:
- Every row has the same number of fields as the header.
- A field that contains the separator, a double quote or a line break must be wrapped in double quotes.
- A double quote inside a quoted field is written twice:
"He said ""hi""". - A quoted field can span several lines. That's valid, and the usual reason why line numbers and record numbers don't match.
good: 1,"Smith, Ana","12"" pipe" bad: 1,Smith, Ana,12" pipe
What usually breaks them
- A stray quote: an inch mark (
12") inside an unquoted field. The parser thinks a quoted field has started and never ends, swallowing the following lines. - An unescaped separator: a comma in an address or company name that wasn't quoted. That row gets an extra field.
- A line break inside a field from a multi-line comment box, in a file that doesn't quote it.
- A truncated file that ends in the middle of a quoted field.
Find the bad row
In a text editor, search for the row count the error mentions. Better, a short Python check that reports which records have the wrong width:
import csv rows = list(csv.reader(open('file.csv', newline='', encoding='utf-8'))) n = len(rows[0]) print([i+1 for i, r in enumerate(rows) if len(r) != n])
The numbers printed are record numbers (not necessarily line numbers if some fields span lines). For an unclosed quote, look at the first record that is much longer than the rest: its last field will contain a large chunk of the file.
Fix it
Open the file in a plain-text editor (not Excel; it can silently rewrite
what it considers wrong). For an inch mark, double it (12"")
and wrap the field in quotes. For an unquoted comma, wrap the whole field
in quotes. For a stray line break, either join the lines or wrap the field
in quotes. Re-run the import.
Prevent it at the source
If you generate the CSV from your own code, use a CSV library instead of joining strings with commas. Libraries apply the quoting rules for you. If you export from a spreadsheet, Excel and Sheets already quote correctly; problems more often come from hand-edited files, concatenated exports, or tools that write CSV with plain string formatting. A file that was cut off during download will also end in the middle of a record, so check that the file size matches what was sent.
CSV Fixer reads your file with a forgiving parser and writes it back out with every field quoted correctly. Short rows get padded with empty cells. The limits to know: a row with more fields than the header has its extra cells dropped, and an unclosed quote is not repaired automatically, so you'll see one huge cell in the preview. If you see that, fix the quote first, then run the file through. Nothing is uploaded; it all runs in your browser.
Fix my CSV