Checking a CSV File's Structure in Python
Find missing fields and repeated column names in a CSV export. This checker reports where the structure breaks without changing the file.
You'll need Python 3, a plain-text editor and a command window. No extra packages are required.
1. Make a practice file
CSV means comma-separated values. Commas separate fields, while double quotes let a field contain a comma or a line break. Save this as sample.csv using UTF-8, the text encoding this checker reads:
Name,Note
Ada,"one,two"
Ben
Cara,"first line
second line"
There are two columns. Ada's note contains a comma inside quotes. Ben's record has only one field. Cara's quoted note spans two physical lines but is still one CSV record.
2. Write the checker
Save this as CheckCsv.py beside the practice file:
import csv as Csv
import io as Io
import sys as Sys
from pathlib import Path
def CheckCsv(FileName):
with Path(FileName).open("rb") as Input:
Data = Input.read(1048577)
if len(Data) > 1048576:
raise ValueError("input exceeds 1 MiB")
Text = Data.decode("utf-8-sig")
Csv.field_size_limit(65536)
Reader = Csv.reader(Io.StringIO(Text, newline=""), strict=True)
try:
Header = next(Reader)
except StopIteration:
raise ValueError("empty input") from None
if not Header or any(not Name.strip() for Name in Header):
raise ValueError("header contains a blank name")
if len(Header) != len(set(Header)):
raise ValueError("header contains repeated names")
Good = 0
Bad = 0
for Number, Row in enumerate(Reader, start=1):
if Number > 1000:
raise ValueError("more than 1000 data records")
if len(Row) != len(Header):
Bad += 1
print(f"Record {Number}, ending at line {Reader.line_num}: expected {len(Header)} fields, found {len(Row)}")
else:
Good += 1
print(f"Columns: {len(Header)}; matching records: {Good}; mismatched records: {Bad}")
return 1 if Bad else 0
def Main():
if len(Sys.argv) != 2:
print("Usage: python3 CheckCsv.py input.csv", file=Sys.stderr)
return 2
try:
return CheckCsv(Sys.argv[1])
except (OSError, UnicodeError, ValueError, Csv.Error) as Error:
print(f"Check stopped: {Error}", file=Sys.stderr)
return 2
if __name__ == "__main__":
raise SystemExit(Main())
The program reads at most 1 MiB (1,048,576 bytes, about one million), plus one extra byte to detect a file over the limit. It accepts UTF-8 with or without a byte-order mark (BOM), an optional marker at the beginning of a text file. It then gives Python's CSV reader the text with newline="", so the CSV reader handles line endings and quoted line breaks.
Each parsed field is limited to 65,536 characters. A longer field stops the check even if the whole file is under 1 MiB. That separate limit applies to header names and data fields.
The first record is the header. Each name must contain something other than whitespace, and repeated names stop the check. Matching is exact: Name, name and Name are different names. The checker does not trim or rename columns.
For each later record, it compares the number of fields with the header's count. It handles at most 1,000 data records. A blank record has zero fields and is reported as a mismatch, rather than silently skipped. strict=True makes the reader stop on CSV syntax problems it detects, such as an unfinished quoted field; it uses comma-separated fields and double quotes, not every system's export rules. A semicolon-separated file can appear to be one valid column, so check the intended separator too.
3. Run it and read the result
Open a command window in the folder and run:
python3 CheckCsv.py sample.csv
If your installation uses python or py for Python 3, use that command instead. The sample prints:
Record 2, ending at line 3: expected 2 fields, found 1
Columns: 2; matching records: 2; mismatched records: 1
Record 2 is Ben's record. Its physical ending line is line 3 because the header occupies line 1. Cara's two-line note is accepted as one record with two fields.
An exit code is the small number a program returns when it ends. This checker returns 0 when every data record has the expected field count, 1 when it finishes and finds mismatches, and 2 when it stops because of an input or parsing error. A file containing only a valid header finishes with zero data records. An empty file stops because there is no header.
A parsing error or record limit can occur after some mismatch messages have printed. If the final message says Check stopped, the earlier lines are partial findings, not a finished check.
4. Tell an empty field from a missing field
Save a separate file as empty-note.csv:
Name,Note
Ben,""
Run:
python3 CheckCsv.py empty-note.csv
An empty quoted note still supplies the second field. The output is:
Columns: 2; matching records: 1; mismatched records: 0
The checker returns exit code 0. Now save missing-note.csv with no comma after Ben:
Name,Note
Ben
Run:
python3 CheckCsv.py missing-note.csv
This time the second field is absent, so the checker returns exit code 1:
Record 1, ending at line 2: expected 2 fields, found 1
Columns: 2; matching records: 0; mismatched records: 1
These are finished checks. To see a check that cannot finish, save repeated-header.csv:
Name,Name
Ada,hello
Run:
python3 CheckCsv.py repeated-header.csv
It returns exit code 2 and writes this error:
Check stopped: header contains repeated names
Finally, save unfinished.csv:
Name,Note
Ada,"unfinished
Run:
python3 CheckCsv.py unfinished.csv
The unclosed quoted field also returns exit code 2:
Check stopped: unexpected end of data
A blank header name, invalid UTF-8, missing file or file over the size limit also stops the check. The input is only opened for reading; save any output under a different filename so shell redirection does not overwrite the input before the program runs.
5. Use the result for the next check
A matching field count says nothing about whether an amount, date or name is correct. A record such as Ada,not-a-date still has two fields. Add value checks only after you know the intended meaning of each column.
Return to sample.csv, fix Ben's record to Ben,hello and run again. The two-line note should still count as one record, and all three data records should now have two fields.
The program passed 19 command-line checks on Python 3.10.12, including quoted multiline fields, malformed records, header errors and the field/record/file limits. Each file test confirmed unchanged input bytes. Use a saved file that will not change during reading.