Python CSV: Read and Write CSV Files (With 7 Gotchas Fixed)
The Python csv module reads and writes comma-separated files with no install. Learn reader, writer, DictReader and DictWriter, quoting, delimiters and encodings, plus seven real gotchas such as blank lines on Windows and BOM-prefixed headers, each shown with tested output.
- The Python csv module handles commas, quotes and line breaks inside fields for you. Never split lines on commas by hand.
- Always open CSV files with
newline=""and an explicitencoding. - Every value comes back as a string; convert numbers and dates yourself.
- Use
utf-8-sigfor files from Excel, or your first header gets a hiddenprefix.
CSV is the universal export format: spreadsheets, banks, analytics tools and databases all speak it. It looks simple enough that many people parse it with line.split(","), and that works until a customer types “tea, coffee” into a notes field. The Python csv module, part of the standard library, handles those edge cases correctly and is fast enough for most jobs.
Every example below ran on Python 3.13.6 and the output is copied from the run. Some behaviour differs by operating system; we ran on Windows and say where that matters. For general file basics, see our Python file handling guide.
What is the Python csv module?
The csv module reads and writes delimited text files. It gives you four main tools:
| Tool | Works with | Use for |
|---|---|---|
csv.reader |
Lists of strings | Simple row-by-row reading |
csv.writer |
Lists or tuples | Writing rows |
csv.DictReader |
Dicts keyed by header | Readable access by column name |
csv.DictWriter |
Dicts | Writing rows with a fixed column order |
Writing a CSV file
import csv
rows = [["id", "name", "city", "note"],
[1, "Asha", "Pune", "likes tea, coffee"],
[2, "Ravi", "Delhi", 'said "hi"'],
[3, "Meera", "Goa", "line1\nline2"]]
with open("people.csv", "w", newline="", encoding="utf-8") as f:
csv.writer(f).writerows(rows)
The raw bytes of the file show what the writer did:
id,name,city,note\r\n
1,Asha,Pune,"likes tea, coffee"\r\n
2,Ravi,Delhi,"said ""hi"""\r\n
3,Meera,Goa,"line1\nline2"\r\n
It quoted the field containing a comma, doubled the quotes inside said "hi", kept the line break inside a quoted field, and ended every row with \r\n, the line ending the CSV standard (RFC 4180) specifies. Numbers were converted to text automatically.
Reading a CSV file with csv.reader
with open("people.csv", newline="", encoding="utf-8") as f:
for row in csv.reader(f):
print(row)
# ['id', 'name', 'city', 'note']
# ['1', 'Asha', 'Pune', 'likes tea, coffee']
# ['2', 'Ravi', 'Delhi', 'said "hi"']
# ['3', 'Meera', 'Goa', 'line1\nline2']
Every tricky field came back exactly as written. Use next(reader) to grab the header row first if you want to skip it in a loop. The reader is an iterator, so it streams the file one row at a time instead of loading it all into memory.
Reading rows as dictionaries with DictReader
with open("people.csv", newline="", encoding="utf-8") as f:
people = list(csv.DictReader(f))
print(people[0])
# {'id': '1', 'name': 'Asha', 'city': 'Pune', 'note': 'likes tea, coffee'}
print(type(people[0]["id"]).__name__) # str
DictReader uses the first row as keys, so code like row["city"] keeps working even if someone reorders the columns. Note the type: the ID is the string '1', not the number 1. Our Python dictionary guide covers working with the results.
Writing dictionaries with DictWriter
with open("out.csv", "w", newline="", encoding="utf-8") as f:
w = csv.DictWriter(f, fieldnames=["sku", "price"])
w.writeheader()
w.writerows([{"sku": "A1", "price": 9.5}, {"sku": "B2", "price": 12}])
# sku,price
# A1,9.5
# B2,12
fieldnames fixes the column order. A dict with a key that is not in fieldnames raises an error, which catches typos early:
csv.DictWriter(f, fieldnames=["sku"]).writerow({"sku": 1, "extra": 2})
# ValueError: dict contains fields not in fieldnames: 'extra'
Pass extrasaction="ignore" if you deliberately want to drop extra keys. Missing keys are written as empty strings, or a restval you choose.
Quoting rules
csv.writer(s, quoting=csv.QUOTE_ALL).writerow([1, "a"])
# "1","a"
csv.writer(s, quoting=csv.QUOTE_NONNUMERIC).writerow([1, "a", 2.5])
# 1,"a",2.5
list(csv.reader(io.StringIO('1,"a",2.5\n'), quoting=csv.QUOTE_NONNUMERIC))
# [[1.0, 'a', 2.5]]
The default, QUOTE_MINIMAL, quotes only when needed. QUOTE_ALL quotes everything, which some strict importers require. QUOTE_NONNUMERIC quotes text but not numbers, and when reading it turns every unquoted field into a float, a quick way to get numbers back, as long as the file really follows that convention.
Delimiters, tabs and dialects
semi = "id;name;amount\n1;Asha;1.234,50\n"
list(csv.reader(io.StringIO(semi), delimiter=";"))
# [['id', 'name', 'amount'], ['1', 'Asha', '1.234,50']]
csv.Sniffer().sniff(semi).delimiter # ';'
list(csv.reader(io.StringIO("a\tb\n1\t2\n"), delimiter="\t"))
# [['a', 'b'], ['1', '2']]
In many European locales, Excel exports use semicolons because the comma is the decimal separator, as in 1.234,50. Pass delimiter for those and for tab-separated files. csv.Sniffer guessed the semicolon correctly here, but it is a heuristic, so set the delimiter explicitly when you know it. You can also register a named dialect with csv.register_dialect to reuse a set of options.
7 Python CSV gotchas and how to fix them
1. Blank lines between rows on Windows. Opening the file without newline="" produced this on our Windows machine:
b'id,name,city,note\r\r\n1,Asha,Pune,"likes tea, coffee"\r\r\n'
The writer emitted \r\n and Python’s text mode turned the \n into another \r\n, giving \r\r\n, which spreadsheets display as blank lines. Always use newline="" for both reading and writing, as the Python documentation recommends. On Linux and macOS you may not see the bug, but the setting is still correct there.
2. A strange first column name. Files saved by Excel as “CSV UTF-8” start with a byte order mark:
with open("bom.csv", newline="", encoding="utf-8") as f:
print(list(next(csv.DictReader(f)).keys())) # ['id', 'name']
with open("bom.csv", newline="", encoding="utf-8-sig") as f:
print(list(next(csv.DictReader(f)).keys())) # ['id', 'name']
With plain utf-8, row["id"] raises KeyError even though the header looks right. utf-8-sig strips the mark if it exists and works fine if it does not. More on encodings in our UTF-8 guide.
3. Everything is a string. Summing row["price"] concatenates text instead of adding numbers. Convert explicitly, as shown in the next section.
4. Splitting on commas by hand. '1,Asha,Pune,"likes tea, coffee"'.split(",") gives five pieces instead of four. Let the csv module parse quotes.
5. Ragged rows.
list(csv.DictReader(io.StringIO("a,b,c\n1,2\n3,4,5,6\n")))
# [{'a': '1', 'b': '2', 'c': None},
# {'a': '3', 'b': '4', 'c': '5', None: ['6']}]
Short rows fill missing columns with None (or your restval), and long rows put the extras under the key None. Neither raises an error, so validate row lengths when data quality matters.
6. Huge fields. By default a single field may hold at most 131,072 characters (csv.field_size_limit()). Larger fields raise _csv.Error; raise the limit with csv.field_size_limit(new_value) only for trusted files.
7. CSV injection. Values starting with =, +, - or @ can run as formulas when a spreadsheet opens your export. If users control the content, prefix such values with a single quote or strip them before writing.
Tip: the safe default for every CSV open() call is newline="" plus an explicit encoding, usually "utf-8" for writing and "utf-8-sig" for reading files from spreadsheets.
Converting types safely
from decimal import Decimal
from datetime import date
def parse(row):
return {"id": int(row["id"]),
"price": Decimal(row["price"]),
"ordered": date.fromisoformat(row["ordered"]),
"note": row["note"].strip() or None}
Write one small parsing function per file type, use Decimal for money to avoid float rounding, and let it raise on bad values so problems surface at import time. For more structured validation, dataclasses or pydantic models work well. If you are building fake CSV fixtures for tests, see test data generation.
How fast is the csv module?
We wrote and read a 200,000-row, 7.5 MB file with four columns (one run each):
| Task | Time |
|---|---|
Write 200,000 rows with writerows |
361 ms |
Read all rows with csv.reader |
148 ms |
Read all rows with DictReader |
380 ms |
| Read and sum one numeric column | 177 ms |
DictReader was about 2.5 times slower than the plain reader because it builds a dict per row. That is still well under a second for this file; switch to the plain reader with column indexes only when profiling says it matters.
csv module vs pandas
| Need | Better choice |
|---|---|
| Stream a huge file row by row with low memory | csv |
| No third-party dependencies | csv |
| Grouping, joins, statistics, type inference | pandas |
| Quick exploration in a notebook | pandas |
We did not run pandas for this article. For JSON instead of CSV, our Python JSON guide covers the equivalent tools, and pathlib makes file paths cleaner in both cases.
Python CSV checklist
- Open with
newline=""and an explicit encoding. - Use
utf-8-sigwhen reading spreadsheet exports. - Prefer
DictReaderandDictWriterfor readable code. - Convert types in one place; use
Decimalfor money. - Set
delimiterexplicitly for semicolon or tab files. - Validate row lengths and required columns.
- Neutralise formula characters in user-controlled exports.
Final thoughts
The Python csv module turns a deceptively tricky format into a few reliable lines of code. Write with csv.writer or DictWriter, read with reader or DictReader, and let the module handle quotes, commas and line breaks. Remember the seven gotchas, above all newline="", utf-8-sig and string-only values, and your imports and exports will survive real-world data. For more string tools, see Python string methods.
Frequently asked questions
How do I read a CSV file in Python?
Open the file with newline set to an empty string and an encoding, then loop over csv.reader for lists or csv.DictReader for dictionaries keyed by the header row.
Why does my CSV file have blank lines between rows?
The file was opened without newline set to an empty string, so on Windows each row ends with an extra carriage return. Open the file with newline set to an empty string when writing.
How do I write a list of dictionaries to CSV?
Create a csv.DictWriter with the fieldnames, call writeheader once, then call writerows with the list. The fieldnames control the column order.
Why does my first column name start with a strange character?
The file starts with a UTF-8 byte order mark, common in Excel exports. Open it with the utf-8-sig encoding and the mark is removed automatically.
Does the csv module convert numbers automatically?
No. Every value is read as a string. Convert with int, float, Decimal or date functions, or use the QUOTE_NONNUMERIC option when the file quotes all text values.
Keep learning
Need help with testing, WordPress or SEO?
We help teams ship better software and grow their sites. Tell us what you are working on and we will suggest a clear next step.