Reading files and CSV in Python: paths, encodings and DictReader
Open files safely with pathlib and with, choose the right encoding, handle Excel's byte order mark and decimal commas, and read CSV exports with csv.DictReader.
Sooner or later every business system imports a file somebody exported from Excel. Those files are never as clean as you'd like. This guide covers the Python you need to read them reliably.
Opening files
Use pathlib.Path for paths and a with block, so the file is closed even if something fails:
from pathlib import Path
path = Path("data") / "shipments.csv" # works on Windows, macOS and Linux
with path.open(encoding="utf-8") as stream:
for line in stream:
...Path(__file__).parentis the folder of the current Python file: handy in tests to find a data file next to them.- Always pass
encoding=. Without it, Python uses the operating system's default, which on Windows is often not UTF-8, and "Bäckerei" turns into "Bäckerei" or an error.
Encodings and the byte order mark
Text files are bytes; the encoding says how bytes map to characters. Today almost everything is
UTF-8. One quirk: Excel (and some Windows tools) put three invisible bytes, a byte order mark,
at the start of UTF-8 files. Read with plain utf-8, it shows up as glued to your first
column name, so row["customer_id"] mysteriously raises KeyError.
with path.open(encoding="utf-8-sig", newline="") as stream: # removes the BOM if present
...utf-8-sig works for files with and without a BOM, so it's the safe choice for anything that
might come from Excel. If your function receives an already-open stream, strip it defensively:
name.lstrip("").
Reading CSV with DictReader
CSV looks simple until a cell contains a comma, a quote or a line break. The csv module handles
all of it:
import csv
with path.open(encoding="utf-8-sig", newline="") as stream:
reader = csv.DictReader(stream)
print(reader.fieldnames) # the header: ['customer_id', 'service', ...]
for row in reader: # each row is a dict: column name -> text
print(row["customer_id"], row["weight_kg"])- Open with
newline="": the csv module handles line endings itself, including line breaks inside quoted cells. - Every value is a string, even numbers. Converting them is your job, with validation.
- Missing cells at the end of a short row come back as
None:row.get(column) or ""gives you a string either way. - In tests you don't need a file at all:
csv.DictReader(io.StringIO("a,b\n1,2\n"))reads from a string.
Line numbers people understand
Users look at the file in Excel, where the header is line 1. Number data rows from 2:
for line, row in enumerate(reader, start=2):
...(If cells can contain line breaks, reader.line_num gives the physical line instead.)
Real-world spreadsheet quirks
| Quirk | What to do |
|---|---|
Decimal commas (2,5) from German, French or Polish Excel | Accept both , and . when parsing numbers |
Empty rows at the end (,,,,) | Skip rows where every cell is blank |
| Spaces around values or headers | .strip() before checking |
| A column renamed or missing | Check the header first and report missing columns |
| Semicolons instead of commas | csv.DictReader(stream, delimiter=";"), or csv.Sniffer |
Don't stop at the first bad row
An import of 500 rows shouldn't fail because of one typo on row 312. Import the good rows, collect the bad ones with their line number and column, and report them together. The person who owns the file can then fix everything in one pass. See Validating input for how to collect errors and write messages people can act on.
Questions and answers
- Why does my CSV header start with \ufeff?
- Excel saves UTF-8 CSV files with a byte order mark (BOM) at the start. Open the file with encoding='utf-8-sig' and Python removes it for you.
- Why not just split CSV lines on commas?
- Because cells can contain commas, quotes and even line breaks inside quotes, like "Krause, Bäckerei" or "2,5". The csv module handles all of that; split(',') doesn't.
Read next
- Validating input: check once at the boundary, then trust your types
Where to validate data, how to report every problem at once with field paths, and how to write error messages people can act on. With examples in Python.
- Testing with pytest: run, read and write Python tests
How to run pytest, read a failing test, select tests with markers and -k, share setup with fixtures, cover many cases with parametrize, and test exceptions.
- Money and units in code: whole cents, whole grams, no floating point
Why 0.1 + 0.2 isn't 0.3, how to store money and measurements as integers, and how to round up and round half up with integer arithmetic only.