1. Home
  2. Blog
  3. Software Development

Software Development

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.

9 min read
Python CSV: read and write CSV files with 7 gotchas fixed

Key takeaways

  • 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 explicit encoding.
  • Every value comes back as a string; convert numbers and dates yourself.
  • Use utf-8-sig for files from Excel, or your first header gets a hidden  prefix.

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-sig when reading spreadsheet exports.
  • Prefer DictReader and DictWriter for readable code.
  • Convert types in one place; use Decimal for money.
  • Set delimiter explicitly 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

Work with Stack Quora

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.

Keep reading

More guides on testing, AI & modern development

Enjoyed this article? Pick a topic and jump straight into our newest tutorials.

Artificial Intelligence

K Means Clustering Explained: How It Works, With Python Code

K means clustering explained: the algorithm, a from-scratch NumPy version, choosing k with the elbow and silhouette methods, k-means++, scaling…

Read article →
Software Development

API Versioning: URL vs Header vs Media Type, With Deprecation

API versioning explained: breaking changes, URL vs header vs media type vs query versioning, default versions, deprecation and sunset headers,…

Read article →
DevOps

Git Log Explained: 15 Useful Commands and Pretty Formats

Git log explained with 15 tested commands: oneline, graph, filters by author, date, message and code, file history, stats, pretty…

Read article →
Test Automation

Pytest conftest.py Explained: Shared Fixtures, Hooks and Overrides

Pytest conftest.py explained with a tested suite: shared fixtures, scope and teardown, folder overrides, autouse, custom options, markers and useful…

Read article →
Software Testing

Usability Testing: Methods, Metrics, SUS Score and Checklist

Usability testing explained: methods, planning, writing tasks, running sessions, metrics, SUS scoring, how many users you need and a practical…

Read article →
Databases & SQL

SQL UPDATE Statement: Safe Updates, UPDATE With JOIN and RETURNING

The SQL UPDATE statement explained with tested examples: WHERE guards, CASE updates, UPDATE with JOIN, the subquery NULL trap, RETURNING…

Read article →
Databases & SQL

SQL COUNT: COUNT(*) vs COUNT(column) vs COUNT(DISTINCT) Explained

SQL COUNT explained: COUNT(*) vs COUNT(column) vs COUNT(DISTINCT), NULLs and empty strings, conditional counts, HAVING, the LEFT JOIN trap and…

Read article →
Software Development

JavaScript Generators Explained: yield, next() and 10 Examples

JavaScript generators explained with 10 tested examples: yield, next, infinite and lazy sequences, yield*, return and throw, iterables and async…

Read article →
Software Development

Python Recursion Explained: 9 Examples, Memoization and the Recursion Limit

Python recursion explained with 9 tested examples: base cases, a traced call stack, memoization with lru_cache, the recursion limit, trees…

Read article →
Software Development

Python CSV: Read and Write CSV Files (With 7 Gotchas Fixed)

Read and write CSV files with the Python csv module: reader, writer, DictReader, DictWriter, quoting, delimiters, encodings and 7 real…

Read article →
Artificial Intelligence

K Nearest Neighbors (KNN) Explained: How It Works, With Python Code

K nearest neighbors explained: the KNN algorithm, a from-scratch NumPy version, choosing k, feature scaling, distance metrics, regression and vector…

Read article →
Software Development

HTTP Cookies Explained: Secure, HttpOnly, SameSite and Scope

HTTP cookies explained: Set-Cookie, Path, Domain, expiry, Secure, HttpOnly, SameSite, prefixes, deleting cookies and a secure session recipe, tested locally.

Read article →
DevOps

Git Rebase Interactive: Squash, Fixup, Reword and Autosquash

Git rebase interactive explained step by step: pick, reword, squash, fixup, edit, drop, autosquash, conflicts and undoing a rebase with…

Read article →
Software Testing

Alpha vs Beta Testing: Key Differences, Exit Criteria and Checklist

Alpha vs beta testing compared: who tests, what each phase finds, entry and exit criteria, how to run a beta,…

Read article →
Test Automation

Pytest Monkeypatch: 10 Tested Examples for Env Vars, Time and APIs

Pytest monkeypatch explained with 10 passing tests: environment variables, functions, config, time, sleeps, paths, blocking the network and the wrong-target…

Read article →
Databases & SQL

SQL Auto Increment: MySQL, PostgreSQL, SQL Server and SQLite

SQL auto increment in MySQL, PostgreSQL, SQL Server and SQLite: syntax, reading the new ID, why gaps appear, ID reuse,…

Read article →
Databases & SQL

SQL BETWEEN: Inclusive Ranges and the Date Trap Explained

SQL BETWEEN explained with tested queries: inclusive ranges, reversed bounds, NULLs, the last-day date trap, half-open ranges and index-friendly date…

Read article →
Software Development

JavaScript Template Literals: 10 Examples Including Tagged Templates

JavaScript template literals explained with 10 tested examples: interpolation, multiline strings, escaping, tagged templates for safe HTML and SQL, and…

Read article →
Artificial Intelligence

Softmax Function Explained: Formula, Python Code and Stable Version

The softmax function explained: formula, worked example, NumPy code, the stable version that avoids NaN, temperature, cross-entropy and softmax vs…

Read article →
DevOps

Git Diff Explained: Staged Changes, Branches and Commits

Git diff explained: read the output, compare unstaged and staged changes, commits and branches, two dots vs three dots, and…

Read article →
Test Automation

Pytest Xdist: Run Tests in Parallel and Cut Test Time

Run pytest tests in parallel with pytest-xdist: install, the -n option, distribution modes, real timings and how to fix shared-state…

Read article →
Software Testing

Verification vs Validation in Software Testing: Example and Table

Verification vs validation in software testing explained with a comparison table and a tested coupon example where every spec test…

Read article →
Databases & SQL

SQL CAST Explained: Convert Data Types With Tested Examples

SQL CAST explained with tested examples: text to number, truncation, integer division, numbers stored as text, dates, bad input and…

Read article →
Databases & SQL

SQL DISTINCT: How to Remove Duplicate Rows (and Its Traps)

SQL DISTINCT explained with tested queries: one and many columns, NULLs, COUNT DISTINCT, DISTINCT vs GROUP BY, the SUM DISTINCT…

Read article →
Artificial Intelligence

Levenshtein Distance: Formula, Worked Table and Python Code

Levenshtein distance explained: the edit operations, the dynamic programming table for kitten and sitting, Python code with two-row memory, normalised…

Read article →
DevOps

Gitignore: Patterns, Negation and Fixing Tracked Files

A tested gitignore guide: pattern rules, leading and trailing slashes, negation with !, the ignored parent folder trap, git check-ignore…

Read article →
Software Testing

Acceptance Criteria: Given When Then Examples and Checklist

How to write acceptance criteria testers can run: rule-based and Given When Then formats, a worked discount code example turned…

Read article →
Software Testing

State Transition Testing: Diagram, Table and Example

State transition testing explained with a login lockout example: states, events, the diagram, the state table, valid and invalid transitions,…

Read article →
Test Automation

Python Unittest: Assert Methods, setUp and Mocks With Examples

A practical Python unittest tutorial: TestCase, assert methods, setUp and tearDown, subTest, skips, expected failures and mock, with a test…

Read article →
Artificial Intelligence

Jaccard Similarity: Formula, Python, MinHash

Jaccard similarity measures overlap between two sets as intersection over union. Work the formula, compute it in Python for words…

Read article →
DevOps

Git Hooks: Pre-commit and Commit-msg Examples

Git hooks run scripts at key moments such as commit and push. See tested pre-commit, commit-msg and prepare-commit-msg hooks, sharing…

Read article →
Test Automation

API Testing in Python: Pytest + Requests Suite

Build an API testing suite in Python with pytest and requests: fixtures, status codes, headers, JSON schema validation, CRUD flows,…

Read article →
Software Testing

Test Design Techniques: 9 Methods + Examples

Test design techniques help you write fewer tests that catch more bugs. Learn 9 black-box, white-box and experience-based methods with…

Read article →
Artificial Intelligence

TF-IDF Explained: Formula and Worked Example

TF-IDF scores a word by how often it appears in a document and how rare it is across all documents….

Read article →
DevOps

Git Tag: Create, Push and Delete Release Tags

Git tag explained with tested commands: annotated vs lightweight tags, listing and sorting, pushing with follow-tags, deleting local and remote…

Read article →
Test Automation

Pytest Coverage: pytest-cov and Branch Gaps

Measure pytest coverage with pytest-cov: term-missing reports, branch coverage, HTML reports, pyproject settings and a fail-under gate for CI, with…

Read article →