Python Working with CSV Files

A CSV (comma-separated values) file is a plain-text format for storing tabular data, where each line is a row and commas separate the individual field values. It’s the most common format for exchanging data between spreadsheets, databases, and scripts, because almost every tool – Excel, Google Sheets, pandas, SQL databases – can import and export it. Python’s built-in csv module lets you read and write these files correctly, handling tricky details like quoted fields, embedded commas, and platform-specific line endings that a naive line.split(",") would get wrong.

Overview: What CSV Files Are and How Python Handles Them

At first glance a CSV file looks trivial: each line is a record, and fields are separated by a delimiter (usually a comma). You might think you could parse one just by reading each line and calling split(","). The problem is that real-world CSV data is messier than that. A text field might legitimately contain a comma (for example, a product description like "Boxed, gift-wrapped"), and the CSV format handles this by wrapping such fields in quotation marks. It might also contain the quote character itself, which the format escapes by doubling it (""). Line endings differ between operating systems (\n on Linux/macOS, \r\n on Windows), and the csv module needs to control those endings itself rather than let Python’s text-mode file handling interfere with them.

Python solves all of this with the csv module, which ships with the standard library – no installation required. Instead of treating a CSV file as raw text, the module treats it as a sequence of records (rows), where each record is a sequence of fields (strings). Internally, csv.reader and csv.writer objects wrap any file-like object (or, more generally, any iterable of strings for reading, or anything with a .write() method for writing) and apply a set of parsing/formatting rules called a dialect. A dialect bundles together the delimiter character, the quote character, the quoting policy, and the line terminator, so you rarely have to think about them individually – the default dialect, called excel, matches the format produced by Microsoft Excel and is what most CSV files in the wild use.

Because a CSV file is just text, every value that comes out of csv.reader is a plain Python str, even if it looks like a number. There is no automatic type inference – "30" stays the string "30" until you explicitly convert it with int() or float(). This is one of the most common sources of bugs for beginners, and it’s covered in detail in the Common Mistakes section below.

Syntax

The four objects you’ll use most often are csv.reader, csv.writer, csv.DictReader, and csv.DictWriter. All of them wrap a file object that you open yourself with open().

import csv

# Reading
with open("file.csv", newline="", encoding="utf-8") as f:
    reader = csv.reader(f, delimiter=",", quotechar='"')
    for row in reader:      # row is a list[str]
        ...

# Writing
with open("file.csv", "w", newline="", encoding="utf-8") as f:
    writer = csv.writer(f, delimiter=",", quotechar='"')
    writer.writerow(["a", "b", "c"])
    writer.writerows([[1, 2, 3], [4, 5, 6]])

Object / parameter	Purpose
csv.reader(file, **fmtparams)	Returns an iterator; each iteration yields one row as a list of strings.
csv.writer(file, **fmtparams)	Returns an object with .writerow(row) and .writerows(rows) methods.
csv.DictReader(file, fieldnames=None)	Yields each row as an OrderedDict-like mapping; if fieldnames is omitted, the first row is used as the header.
csv.DictWriter(file, fieldnames)	Writes dictionaries; fieldnames is required and defines column order. Call .writeheader() first.
delimiter	The field separator character. Default ","; often ";" or "\t" for other regional/formats.
quotechar	Character used to quote fields containing the delimiter or newlines. Default '"'.
quoting	Policy constant: QUOTE_MINIMAL (default), QUOTE_ALL, QUOTE_NONNUMERIC, QUOTE_NONE.
newline=""	Passed to open(), not to csv itself – prevents double line-ending translation.

Examples

Example 1: Writing and reading a simple CSV file

import csv
import tempfile
import os

rows = [
    ["name", "age", "city"],
    ["Alice", 30, "New York"],
    ["Bob", 25, "Los Angeles"],
    ["Carol", 35, "Chicago"],
]

fd, path = tempfile.mkstemp(suffix=".csv")
os.close(fd)

with open(path, "w", newline="", encoding="utf-8") as f:
    writer = csv.writer(f)
    writer.writerows(rows)

with open(path, newline="", encoding="utf-8") as f:
    reader = csv.reader(f)
    for row in reader:
        print(row)

os.remove(path)

Output:

['name', 'age', 'city']
['Alice', '30', 'New York']
['Bob', '25', 'Los Angeles']
['Carol', '35', 'Chicago']

Notice that the integers 30, 25, and 35 were written just fine – csv.writer calls str() on any non-string value automatically – but when read back, every field comes back as a str, including '30'. The newline="" argument to open() is essential here: it tells Python not to translate line endings itself, so the csv module’s own \r\n handling isn’t doubled up (more on this in Common Mistakes).

Example 2: Using DictReader and DictWriter for named columns

import csv
import tempfile
import os

fieldnames = ["product", "price", "quantity"]
data = [
    {"product": "Widget", "price": 9.99, "quantity": 3},
    {"product": "Gadget", "price": 19.99, "quantity": 1},
]

fd, path = tempfile.mkstemp(suffix=".csv")
os.close(fd)

with open(path, "w", newline="", encoding="utf-8") as f:
    writer = csv.DictWriter(f, fieldnames=fieldnames)
    writer.writeheader()
    writer.writerows(data)

with open(path, newline="", encoding="utf-8") as f:
    reader = csv.DictReader(f)
    total = 0.0
    for row in reader:
        cost = float(row["price"]) * int(row["quantity"])
        total += cost
        print(f"{row['product']}: {cost:.2f}")
    print(f"Total: {total:.2f}")

os.remove(path)

Output:

Widget: 29.97
Gadget: 19.99
Total: 49.96

DictWriter needs an explicit fieldnames list because a plain dict in general has no guaranteed column order until you tell it one, and calling .writeheader() writes that list as the first row. DictReader then reads the first row of the file back as the header automatically and uses it to build a dictionary for every subsequent row, which is why you can refer to row["price"] by name instead of by position – much more readable once a file has more than three or four columns.

Example 3: Custom delimiters and quoting for non-standard files

import csv
import io

data = (
    "name;bio;score\n"
    'Alice;"Loves Python; hiking";95\n'
    'Bob;"Plays chess, guitar";88\n'
)

f = io.StringIO(data)
reader = csv.reader(f, delimiter=";", quotechar='"')
for row in reader:
    print(row)

Output:

['name', 'bio', 'score']
['Alice', 'Loves Python; hiking', '95']
['Bob', 'Plays chess, guitar', '88']

This example uses io.StringIO to simulate a file whose delimiter is a semicolon instead of a comma – common in European-locale exports where the comma is already used as the decimal separator. Notice that Alice‘s bio field contains a semicolon inside quotes; because it’s wrapped in quotechar characters, csv.reader correctly treats it as part of a single field instead of splitting on it. This is exactly the kind of parsing that a manual split(";") would get wrong.

How CSV Parsing Works Under the Hood

When you create a csv.reader, Python builds a small state machine configured by a dialect object (either the built-in "excel" dialect or one you register with csv.register_dialect()). As it reads each line from the underlying file object, it scans character by character: outside quotes, the delimiter ends the current field; inside quotes, the delimiter is just another character, and a doubled quote character ("") is unescaped to a single literal quote. A field is only considered finished when the parser exits quoted mode and hits a real delimiter or line terminator.

On the write side, csv.writer does the reverse: for each row, it converts every value to a string with str(), then decides – based on the quoting parameter – whether each field needs to be wrapped in quotechar. With the default QUOTE_MINIMAL, a field is quoted only if it contains the delimiter, the quote character, or a line terminator. QUOTE_ALL quotes every field unconditionally; QUOTE_NONNUMERIC quotes every field that isn’t already a Python number and converts unquoted fields to float when reading; QUOTE_NONE disables quoting entirely and instead escapes the delimiter with an escapechar, which is unusual and rarely what you want.

If you don’t know a file’s dialect in advance – for example, you don’t know if it’s comma- or tab-delimited – the module includes csv.Sniffer(), which examines a sample of the file’s text and guesses the delimiter and quoting style, returning a dialect object you can pass straight into csv.reader(). This is handy when processing CSV exports from unknown third-party sources.

Common Mistakes

Mistake 1: Opening files without newline="". If you open a CSV file for reading or writing with plain open(path, "w") instead of open(path, "w", newline=""), Python’s universal-newline text mode can interact with the csv module’s own \r\n line terminator and produce extra blank lines between rows on Windows, or otherwise mangle line endings. The fix is simple and shown in every example above: always pass newline="" to open() whenever a csv.reader or csv.writer is involved, regardless of platform.

Mistake 2: Treating every field as its original type. Every value that comes out of csv.reader or DictReader is a str, even if it visually looks like a number or a boolean. Forgetting this leads to type errors:

import csv
import io

data = "name,age\nAlice,30\nBob,25\n"
reader = csv.DictReader(io.StringIO(data))
for row in reader:
    print(row["age"] + 1)

This raises TypeError: can only concatenate str (not "int") to str, because row["age"] is the string "30", not the integer 30. The fix is to convert explicitly: int(row["age"]) + 1. The same trap applies to booleans – bool("False") evaluates to True, because any non-empty string is truthy, so you need an explicit check like row["in_stock"] == "True" instead.

Mistake 3: Building CSV lines by hand with string concatenation. Writing something like f"{name},{bio},{score}" instead of using csv.writer looks fine until a value like bio itself contains a comma – at that point the resulting line has an extra field and every downstream column shifts by one. Always build rows with csv.writer.writerow() (or DictWriter) so quoting is handled automatically instead of reimplementing the CSV spec yourself.

Best Practices

• Always open CSV files with newline="" for both reading and writing to avoid platform-specific line-ending bugs.
• Specify encoding="utf-8" explicitly, and use "utf-8-sig" when reading files exported from Excel that may include a byte-order mark.
• Prefer DictReader/DictWriter over the plain list-based versions once a file has more than a handful of columns – referring to row["price"] is far more readable than row[2].
• Never assume a field’s type – always convert explicitly with int(), float(), or datetime.strptime() after reading.
• Use csv.Sniffer() to detect the dialect automatically when processing CSV files from an unknown or third-party source.
• For very large files, iterate row by row with the reader instead of loading everything into a list with list(reader), to keep memory usage low.
• Let the csv module handle quoting and escaping – never build CSV rows with manual string concatenation.
• Use quoting=csv.QUOTE_ALL or QUOTE_NONNUMERIC when a downstream tool expects consistent quoting, rather than the default minimal quoting.
• For heavy data analysis on large datasets, consider pandas.read_csv(), but reach for the dependency-free csv module for simple scripts and one-off I/O.

Practice Exercises

Exercise 1: Write a program that creates a CSV file for at least five students with columns name, grade1, grade2, grade3, then reads the file back with DictReader and prints each student’s name alongside the average of their three grades, rounded to one decimal place.

Exercise 2: Given a semicolon-delimited CSV string held in an io.StringIO object, parse it with csv.reader(f, delimiter=";") and, using the first row as headers, print every subsequent row as a dictionary built with zip(header, row).

Exercise 3: Extend the DictWriter example from this lesson by adding an in_stock column containing the strings "True" or "False". When reading the file back, write the correct comparison to convert that column into a real Python bool – remember that bool("False") is True, so a naive conversion will silently produce the wrong answer.

Summary

• The csv module reads and writes CSV files while correctly handling quoting, custom delimiters, and fields containing embedded special characters.
• csv.reader/csv.writer work with plain lists of strings; csv.DictReader/csv.DictWriter work with dictionaries so you can refer to columns by name.
• Always open files with newline="" so the csv module’s own line-ending handling isn’t doubled up by Python’s universal-newline translation.
• Every value read from a CSV file is a string – convert explicitly to int, float, bool, or a date type as needed.
• Dialects (delimiter, quote character, quoting policy) can be customized manually or auto-detected with csv.Sniffer() for files whose format isn’t known in advance.