Python CSV Files: Read, Write, and Process Data

CSV files are everywhere: spreadsheet exports, sales reports, contact lists, analytics downloads, and database transfers. Although the name means “comma-separated values,” real files may use semicolons, tabs, quoted fields, or embedded line breaks. Python’s standard csv module handles those details more safely than manually splitting strings.

This guide shows how to read, write, filter, validate, and transform Python CSV files with both list-based and dictionary-based APIs. It also explains encoding, delimiters, missing values, and the point at which a dataframe library may be more appropriate.

Why not use split(“,”)?

A simple line.split(",") fails when a value contains a comma inside quotes, when quotes are escaped, or when a field spans multiple lines. The standard parser understands these conventions and can adapt to different dialects. The official Python csv documentation is the authoritative reference.

Read a CSV file with csv.reader

Open the file with newline="" so the module can manage newline conventions correctly. Declare the text encoding explicitly for portability.

import csv

with open("sales.csv", newline="", encoding="utf-8") as file:
    reader = csv.reader(file)

    for row in reader:
        print(row)

Each row is returned as a list of strings. The parser does not automatically know that a column represents an integer, decimal, date, or Boolean. Convert and validate fields according to your schema.

import csv

total = 0.0

with open("sales.csv", newline="", encoding="utf-8") as file:
    reader = csv.reader(file)
    header = next(reader)

    for product, quantity_text, price_text in reader:
        quantity = int(quantity_text)
        price = float(price_text)
        total += quantity * price

print(f"Total: ${total:.2f}")

Calling next(reader) consumes the header row. For a more maintainable approach, use DictReader so code refers to column names instead of positions.

Read rows as dictionaries

import csv

with open("people.csv", newline="", encoding="utf-8") as file:
    reader = csv.DictReader(file)

    for row in reader:
        print(row["name"], row["email"])

When fieldnames is omitted, the first row supplies the keys. This makes code easier to read and more resilient when columns move, although renamed or missing columns still require validation. Our Python dictionaries guide explains the mapping operations used here.

Write CSV data

Use csv.writer for sequences. writerow() writes one record and writerows() writes an iterable of records.

import csv

rows = [
    ["name", "score"],
    ["Maya", 92],
    ["Noah", 87],
    ["Liam", 95],
]

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

Opening with mode w replaces the existing file. Use the practices from the text file guide to choose modes and handle errors safely.

Write dictionaries with DictWriter

import csv

records = [
    {"name": "Maya", "score": 92},
    {"name": "Noah", "score": 87},
]

fieldnames = ["name", "score"]

with open("scores.csv", "w", newline="", encoding="utf-8") as file:
    writer = csv.DictWriter(file, fieldnames=fieldnames)
    writer.writeheader()
    writer.writerows(records)

DictWriter requires an explicit field order. By default, an unexpected key raises an error, which is often useful because it reveals a mismatch between the data and file schema.

Handle semicolon and tab delimiters

Not every CSV uses commas. Spreadsheet software in some locales exports semicolon-delimited files, while tab-separated values use a tab character.

import csv

with open("products.csv", newline="", encoding="utf-8") as file:
    reader = csv.DictReader(file, delimiter=";")

    for row in reader:
        print(row)

Specify the format when you know it. csv.Sniffer can estimate a dialect from a sample, but it is a heuristic and should not replace validation in critical workflows.

Filter and transform rows

A common pipeline reads rows, validates them, filters records, and writes a clean output file. The next example keeps active users and normalizes names and emails.

import csv

with open("users.csv", newline="", encoding="utf-8") as source:
    reader = csv.DictReader(source)

    cleaned = []
    for row in reader:
        if row["active"].strip().lower() != "yes":
            continue

        cleaned.append({
            "name": row["name"].strip().title(),
            "email": row["email"].strip().lower(),
        })

with open("active_users.csv", "w", newline="", encoding="utf-8") as target:
    writer = csv.DictWriter(target, fieldnames=["name", "email"])
    writer.writeheader()
    writer.writerows(cleaned)

For a large input, write each cleaned record immediately instead of storing the entire cleaned list. Streaming keeps memory usage predictable.

Stream large files

import csv

with (
    open("events.csv", newline="", encoding="utf-8") as source,
    open("errors.csv", "w", newline="", encoding="utf-8") as target,
):
    reader = csv.DictReader(source)
    writer = csv.DictWriter(target, fieldnames=reader.fieldnames or [])
    writer.writeheader()

    for row in reader:
        if row.get("level") == "ERROR":
            writer.writerow(row)

File objects and CSV readers are iterators, so rows are produced as needed. This is more scalable than list(reader). The guide to generators and yield explains lazy data flow further.

Validate required columns

Do not assume every file has the expected header. Check required columns before processing records and produce a clear error.

import csv

required = {"name", "email", "active"}

with open("users.csv", newline="", encoding="utf-8") as file:
    reader = csv.DictReader(file)
    actual = set(reader.fieldnames or [])
    missing = required - actual

    if missing:
        names = ", ".join(sorted(missing))
        raise ValueError(f"Missing CSV columns: {names}")

    for row in reader:
        print(row["email"])

Set operations make schema comparisons concise. Learn union, intersection, and difference in the Python sets tutorial.

Handle bad rows without hiding errors

A production import should report the line and reason when conversion fails. Decide whether one invalid row should stop the job or be recorded and skipped.

import csv

with open("orders.csv", newline="", encoding="utf-8") as file:
    reader = csv.DictReader(file)

    for row_number, row in enumerate(reader, start=2):
        try:
            quantity = int(row["quantity"])
            price = float(row["price"])
        except (KeyError, TypeError, ValueError) as error:
            print(f"Row {row_number} skipped: {error}")
            continue

        print(quantity * price)

Starting at two accounts for the header line. Avoid a blanket except, which could also hide programming bugs. See Python exception handling for specific handlers.

Dates and missing values

CSV has no native data types. Empty fields, the text NULL, and omitted values may all mean different things. Define a conversion policy. Parse date strings deliberately rather than comparing them lexicographically unless they use a guaranteed sortable format such as ISO 8601.

from datetime import date

def parse_date(value: str) -> date | None:
    value = value.strip()

    if not value:
        return None

    return date.fromisoformat(value)

The datetime guide covers parsing, formatting, and date arithmetic.

CSV versus pandas

The standard module is ideal for streaming, simple transformations, low dependencies, and precise format control. Pandas is often more productive for exploratory analysis, joins, group operations, missing-data cleanup, and columnar calculations. It loads data into a dataframe and introduces a larger dependency.

Choose based on the task. The site’s Pandas introduction is a good next step for analytical workflows.

Common mistakes

• Parsing rows with split(",") instead of the CSV module.
• Forgetting newline="" when opening CSV files.
• Assuming values are automatically converted to numbers or dates.
• Ignoring encoding and receiving unreadable characters.
• Loading an enormous file into a list unnecessarily.
• Trusting headers and row lengths without validation.
• Overwriting the source file before a transformation is verified.

Frequently asked questions

How do I read an Excel file?

An .xlsx workbook is not CSV. Use a library designed for Excel files, such as openpyxl or pandas. The guide to editing Excel spreadsheets with Python explains that workflow.

Why are all values strings?

CSV stores textual fields and does not carry a universal schema. Convert each field according to your application’s rules and report invalid values clearly.

Can a CSV field contain a comma?

Yes. The writer quotes fields when required, and the reader recognizes properly quoted delimiters. That is a key reason to use the module instead of manual string splitting.

Final thoughts

Reliable Python CSV processing starts with the standard parser, explicit UTF-8 encoding, newline="", documented column rules, and specific error reporting. Stream large files, validate headers before accessing them, and move to a dataframe library only when its higher-level analysis features provide real value.