How to Use Python to Parse CSV and Excel Files for Reports

A CSV that looks simple hides quoted commas, embedded newlines, a byte order mark and numbers with thousands separators. Splitting lines on commas works until the first quoted field. The csv module handles all of that, and a few lines turn an export into a summary. This is the Python version of the awk techniques in awk with CSV files. The examples assume Python 3.13.

Read rows by column name

import csv
from collections import defaultdict
from decimal import Decimal

totals = defaultdict(Decimal)
with open("sales.csv", newline="", encoding="utf-8-sig") as handle:
    for row in csv.DictReader(handle):
        totals[row["region"]] += Decimal(row["amount"].replace(",", ""))

for region, total in sorted(totals.items(), key=lambda kv: kv[1], reverse=True):
    print(f"{region:<10} {total:>12,.2f}")

north         48,210.50
south         31,004.00
east          12,780.25

Given a header row of region,amount,date, DictReader exposes each row as {"region": ..., "amount": ...}, so reordering columns in the export does not break the script. newline="" is required by the module's documentation so that line breaks inside quoted fields are handled correctly. Use Decimal for money, because floats give totals like 0.30000000000000004.

Write a summary

with open("summary.csv", "w", newline="", encoding="utf-8") as out:
    writer = csv.writer(out)
    writer.writerow(["region", "total"])
    for region, total in sorted(totals.items()):
        writer.writerow([region, f"{total:.2f}"])

csv.writer quotes fields as needed, so a region named East, Coast does not break the file. For other delimiters pass delimiter=";" or "\t". If a file's dialect is unknown, csv.Sniffer().sniff(sample) guesses, but give the format explicitly when you can.

Bad data

bad = []
with open("sales.csv", newline="", encoding="utf-8-sig") as handle:
    for number, row in enumerate(csv.DictReader(handle), start=2):
        try:
            Decimal(row["amount"].replace(",", ""))
        except Exception:
            bad.append(number)
print(f"{len(bad)} bad rows: {bad[:10]}")

Row numbers start at 2 when the header is line 1. Collect problems and report them at the end, rather than stopping on the first or ignoring them. An encoding error means the file is not UTF-8; try cp1252 for old Windows exports, or open with errors="replace" only if lost characters are acceptable. See encoding and locale problems.

Excel files

from openpyxl import load_workbook

wb = load_workbook("report.xlsx", read_only=True, data_only=True)
sheet = wb["Sales"]
rows = sheet.iter_rows(values_only=True)
header = next(rows)
for values in rows:
    record = dict(zip(header, values))
    print(record["region"], record["amount"])

Install it with python3 -m pip install openpyxl. data_only=True returns the cached results of formulas instead of the formula text, and read_only=True streams large sheets with low memory. Dates arrive as datetime objects, not strings. The older .xls format needs a different library.

Choosing between csv, pandas and awk

The standard library csv module is the right default for reports of moderate size: it needs no installation, streams rows with constant memory, and handles quoting that hand-written splitting gets wrong. pandas is worth its weight when the work is analytical, such as grouping by several columns, joining two files or reshaping, because the equivalent loops are long. For a quick total or a count by one column on a file you will never touch again, an awk one-liner is faster to write than either, as in parsing CSV with awk.

The boundary is maintainability. A script that runs weekly from cron and feeds a manager's inbox should be plain Python with tests, because the person who inherits it will not remember the awk. Prefer the least powerful tool that stays readable, and avoid adding a large dependency to a server for one report.

Types, locales and numbers

Every value in a CSV is text. A column of amounts must be converted before it is summed, and the conversion is where bugs live. A value like 1,234.50 breaks float(), a European export may use a comma as the decimal separator, and a blank field raises ValueError. Decide the rules in one function that parses a cell, return None for blanks, and let the caller decide whether to skip, count or abort.

For money, use decimal.Decimal built from the string, not float, so totals match the source to the cent. Floats accumulate rounding error that shows up as a total one cent off from the accounting system, and arguing with an accountant about binary fractions is not a good use of anyone's time. Dates deserve the same discipline: parse with datetime.strptime and an explicit format, and refuse ambiguous values like 03/04/2026 unless the format is documented.

Encodings, delimiters and the BOM

A file exported from a spreadsheet often arrives as UTF-8 with a byte order mark, and the first column header then carries an invisible extra character in front of name, which makes row["name"] raise a key error that looks impossible. Open the file with encoding="utf-8-sig", which strips the mark if present and costs nothing if it is not. Always pass newline="" when opening a file for the csv module, as its documentation requires, so embedded line breaks in quoted fields are handled correctly.

When the delimiter is unknown, csv.Sniffer can guess it from a sample, but a guess in a scheduled job is a source of silent errors. Specify the delimiter in the code and fail if the header does not contain the columns you expect. A one-line check that the required names are present turns a changed export into a clear message on day one rather than a wrong report weeks later.

Writing the report so people trust it

A report is only as useful as its provenance. Include the input file name, the row count read, the number of rows skipped and why, and the time it ran. Those four lines let a reader see that the total came from the whole file, not from the half that parsed. Write the output with csv.writer or as a small HTML table, and save it atomically by writing a temporary file and renaming it, so a mail job never attaches a half-finished file.

Sort the output deterministically. A report whose rows reorder between runs makes every diff noisy and every spot check harder, so sort by a stable key and break ties by a second column. Keep the totals row at the bottom, computed from the same data as the rows above it, so the figures reconcile when someone adds them with a calculator.

Check the script against the awkward inputs, not the tidy sample: an empty file, a header with no rows, a row with too few fields, a quoted field with a comma and one with a line break, and a value that is not a number. Each should either be handled and counted or stop the run with a message that names the line. Using csv.DictReader and its line_num attribute makes that message easy to produce, and it lets a person fix the source file instead of guessing which row was wrong.

Keep the report logic separate from the reading, in functions that take plain lists of dictionaries. Those can be checked using a few literal rows, and the same functions will work when the data source changes from a file to a database query.

Where this does not apply

For a one-off look, column -s, -t or csvlook from csvkit is faster. Very large files, in the gigabytes, are better loaded into SQLite or processed with DuckDB, as in importing CSV into a database. The csv module does no type conversion, so a column of identifiers with leading zeros stays correct only because you keep it a string. See the csv module documentation and the openpyxl documentation.