Python CSV Handling in Depth

CSV looks simple, but fields can contain the delimiter. The format handles that by quoting: a field wrapped in double quotes may contain commas and line breaks, and a literal double quote inside it is written twice:

crew,club,seconds
Harbor Herons,"Riverside, North",83
"The ""Fast"" Four",Lakeside,79

line.split(",") on the second line gives four pieces instead of three, and leaves the quotes in place. The csv module parses it correctly:

import csv, io
text = 'crew,club\nHarbor Herons,"Riverside, North"\n'
print(list(csv.reader(io.StringIO(text))))
# [['crew', 'club'], ['Harbor Herons', 'Riverside, North']]

csv.reader returns each row as a list of strings. Not every "CSV" uses commas: European spreadsheet exports often use semicolons, and tab-separated files are common. Pass delimiter=";" or delimiter="\t", or let csv.Sniffer().sniff(sample) guess the dialect from a sample of the file.

csv.DictReader uses the header row as keys and returns each data row as a dictionary, so you refer to columns by name instead of position:

for row in csv.DictReader(file):
    seconds = int(row["seconds"])     # every value arrives as a string

Code that uses names keeps working if someone reorders the columns. Two details matter in practice:

• Every value is a string, even if it looks like a number. Convert with int(), float() or date parsing before calculating, and handle ValueError for bad cells.
• Missing and extra fields don't raise errors. If a row is short, the missing keys get the value None (or restval); if it has extra fields, they're collected in a list under the key None (or restkey). Check for these when the data matters.

Headers sometimes carry stray spaces or a byte-order mark from Excel. Opening the file with encoding="utf-8-sig" removes the BOM, and skipinitialspace=True ignores spaces after delimiters.

csv.writer writes lists; csv.DictWriter writes dictionaries and needs an explicit fieldnames list, which fixes the column order:

import csv
results = [{"crew": "Harbor Herons", "seconds": 85},
           {"crew": "Canal Otters", "seconds": 81}]

with open("adjusted.csv", "w", newline="", encoding="utf-8") as file:
    writer = csv.DictWriter(file, fieldnames=["crew", "seconds"])
    writer.writeheader()
    writer.writerows(results)

Values are converted with str() and quoted automatically when they contain the delimiter, quotes or newlines. quoting=csv.QUOTE_ALL quotes every field, which some systems require.

If a dictionary has a key that isn't in fieldnames, DictWriter raises ValueError by default. That's deliberate: silently dropping data is rarely what you want. Pass extrasaction="ignore" only when you've decided extra keys should be discarded.

Two open() settings prevent the most common CSV problems:

• newline="" lets the csv module handle line endings itself. Without it, quoted fields containing line breaks can be misread, and on Windows writing produces a blank line between every row. The Python documentation recommends newline="" for both reading and writing.
• encoding must match the file. Use "utf-8" for files you create. Files exported from Excel may be "utf-8-sig" (UTF-8 with a BOM) or a legacy code page such as "cp1252"; a UnicodeDecodeError or odd characters like é mean the encoding is wrong.

For quick tests and examples, io.StringIO gives a file-like object over a string, so you can run CSV code without creating files. For large analytical work, pandas reads CSV with pd.read_csv(), which converts types automatically. The csv module remains the right tool for streaming rows one by one with minimal dependencies.

Task: import race rows, validate missing fields and export adjusted times with an explicit header.

import csv, io

data = io.StringIO("crew,seconds\nHerons,83\nOtters,\nKites,77\n")
rows = [r for r in csv.DictReader(data) if r["seconds"].strip()]
for r in rows:
    r["seconds"] = int(r["seconds"]) + 2

out = io.StringIO()
w = csv.DictWriter(out, fieldnames=["crew", "seconds"], lineterminator="\n")
w.writeheader()
w.writerows(rows)
print(out.getvalue())

This prints a header and two rows, Herons,85 and Kites,79; the Otters row is skipped because its time is blank. For the worked check, a numeric field returned by DictReader has type str until you convert it.

The csv module documentation describes dialects, quoting constants and every reader and writer option. Continue with JSON handling and data cleaning. Practise with the Python exercises and Python quiz.