Back to Blog
Working with CSV Files: Tabular Data in Python

Working with CSV Files: Tabular Data in Python

November 16, 2026
Reynov Christian

Welcome to Phase 2 of our Python for Beginners series! In Phase 1, you learned how to save plain text files using pathlib. That works well for simple notes, but what happens when you need to store structured, table-like data such as expenses, student grades, or product catalogs?

This is where CSV files shine. In this lesson, you will learn how to read, write, and format spreadsheet-style data using Python’s built-in csv module, and you will build a Personal Expense Tracker that exports clean financial reports.

1. What Is a CSV File?

CSV stands for Comma-Separated Values. It is a plain text format where each line represents a row in a table, and values in each row are separated by commas. Because CSV files are lightweight and universal, you can open them directly in Excel, Google Sheets, or any text editor.

date,category,description,amount
2026-11-10,Food,Lunch at cafe,12.50
2026-11-11,Transport,Bus pass,25.00
2026-11-12,Books,"Python Guide, 2nd Edition",19.99

Notice the last line: "Python Guide, 2nd Edition" contains a comma inside the text. If you tried to parse CSV lines manually with line.split(","), that title would break into two columns! Python’s built-in csv module handles quoted fields, escaped characters, and different delimiters automatically.

2. Reading and Writing Raw Rows with csv.reader and csv.writer

The simplest tools in the csv module are csv.writer and csv.reader. They treat every row in your CSV file as a standard Python list of strings.

When opening CSV files in Python, always pass newline="" and encoding="utf-8" to open(). The newline="" argument prevents Python from adding blank lines between rows on Windows.

import csv
from pathlib import Path

file_path = Path("sample_expenses.csv")

rows = [
    ["date", "category", "amount"],
    ["2026-11-10", "Food", "12.50"],
    ["2026-11-11", "Transport", "25.00"],
]

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

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

Using csv.reader is quick, but accessing columns by index like row[2] can become hard to read in larger scripts. That is why dictionaries are often a better choice.

3. Cleaner Mapping with csv.DictReader and csv.DictWriter

csv.DictReader uses the first row of the file (the header) as dictionary keys for every row that follows. Instead of guessing what row[1] means, you can write row["category"].

import csv
from pathlib import Path

file_path = Path("sample_expenses.csv")

with open(file_path, "r", newline="", encoding="utf-8") as file:
    reader = csv.DictReader(file)
    for row in reader:
        print(f"{row['date']} | {row['category']} | ${row['amount']}")

When writing dictionaries with csv.DictWriter, you provide a fieldnames list that defines the column order, and call writer.writeheader() to write the top header row.

4. Handling Custom Delimiters and Quotes

In some regions or software exports, files use semicolons (;) or tabs (\t) instead of commas. You can pass delimiter=";" or delimiter="\t" directly to csv.reader, csv.writer, csv.DictReader, or csv.DictWriter. Python’s csv module also handles quote characters automatically using quotechar='"' by default.

5. Practical Project: Personal Expense Tracker

Let’s combine everything into a clean Personal Expense Tracker. Create a file named expense_tracker.py. It lets you log expenses to a CSV file, view all recorded expenses, and export a formatted financial summary report. Note that float is fine for learning here, while real accounting systems use decimal.Decimal for exact money math. At the bottom, if __name__ == "__main__": is a standard guard that runs main() only when you execute this file directly (covered in detail in Lesson 15).

import csv
from pathlib import Path

EXPENSES_FILE = Path("expenses.csv")
REPORT_FILE = Path("expense_report.txt")
FIELDNAMES = ["date", "category", "description", "amount"]


def add_expense(
    file_path: Path,
    date: str,
    category: str,
    description: str,
    amount: float,
) -> None:
    """Append a single expense record to the CSV file."""
    file_exists = file_path.exists()

    with open(file_path, "a", newline="", encoding="utf-8") as file:
        writer = csv.DictWriter(file, fieldnames=FIELDNAMES)
        if not file_exists:
            writer.writeheader()
        writer.writerow({
            "date": date,
            "category": category,
            "description": description,
            "amount": f"{amount:.2f}",
        })


def load_expenses(file_path: Path) -> list[dict[str, str]]:
    """Read all expense records from the CSV file."""
    if not file_path.exists():
        return []

    with open(file_path, "r", newline="", encoding="utf-8") as file:
        reader = csv.DictReader(file)
        return list(reader)


def export_summary_report(
    expenses: list[dict[str, str]],
    report_path: Path,
) -> None:
    """Calculate category totals and write a formatted text report."""
    if not expenses:
        print("No expenses found to export.")
        return

    totals_by_category: dict[str, float] = {}
    grand_total = 0.0

    for item in expenses:
        category = item["category"]
        amount = float(item["amount"])
        totals_by_category[category] = totals_by_category.get(category, 0.0) + amount
        grand_total += amount

    lines = [
        "=== Personal Expense Report ===",
        "",
        "Totals by Category:",
    ]
    for category, total in sorted(totals_by_category.items()):
        lines.append(f"- {category:<15} ${total:>8.2f}")

    lines.append("-" * 30)
    lines.append(f"Grand Total:      ${grand_total:>8.2f}")

    report_path.write_text("\n".join(lines), encoding="utf-8")
    print(f"Summary report exported to {report_path}.")


def get_valid_amount(prompt: str) -> float:
    """Prompt the user until a valid positive amount is entered."""
    while True:
        try:
            value = float(input(prompt))
            if value <= 0:
                print("Amount must be greater than 0.")
                continue
            return value
        except ValueError:
            print("Invalid input. Please enter a valid number (for example, 14.99).")


def main() -> None:
    """Run the Personal Expense Tracker CLI."""
    print("Welcome to your Personal Expense Tracker!")

    while True:
        print("\nMenu:")
        print("1. Add Expense")
        print("2. View All Expenses")
        print("3. Export Financial Summary Report")
        print("4. Exit")

        choice = input("Choose an option (1-4): ").strip()

        if choice == "1":
            date = input("Date (YYYY-MM-DD): ").strip()
            category = input("Category (e.g., Food, Transport): ").strip().title()
            description = input("Description: ").strip()
            amount = get_valid_amount("Amount ($): ")
            add_expense(EXPENSES_FILE, date, category, description, amount)
            print("Expense saved to expenses.csv.")
        elif choice == "2":
            expenses = load_expenses(EXPENSES_FILE)
            if not expenses:
                print("No expenses recorded yet.")
                continue
            print("\n--- Recorded Expenses ---")
            for item in expenses:
                print(
                    f"{item['date']} | {item['category']:<12} | "
                    f"${item['amount']:>7} | {item['description']}"
                )
        elif choice == "3":
            expenses = load_expenses(EXPENSES_FILE)
            export_summary_report(expenses, REPORT_FILE)
        elif choice == "4":
            print("Goodbye!")
            break
        else:
            print("Invalid choice. Please enter 1, 2, 3, or 4.")


if __name__ == "__main__":
    main()

Try adding a few expenses, including one with a comma in the description, and open expenses.csv in a spreadsheet program. You will see every column aligned cleanly.

Common Beginner Mistakes

  • Forgetting newline="" on Windows: Without newline="", csv.writer inserts an extra blank line after every row.
  • Using .split(",") on CSV lines: Manual string splitting breaks whenever a field contains a comma inside quotes, such as "Python Guide, 2nd Edition".
  • Writing headers on every append: Calling writer.writeheader() unconditionally in append mode ("a") repeats the header row in the middle of your file. Always check file_path.exists() first.
  • Forgetting that CSV values are always strings: csv.DictReader returns every value as a str, so remember to convert numeric fields with float() or int() before doing math.

Try It Yourself

  • Category Filter (filter_expenses.py): Add a menu option that asks for a category name and displays only expenses matching that category along with their total.
  • Semicolon Export (export_semicolon.py): Write a function that exports expenses.csv using delimiter=";" so it opens cleanly in European Excel locales.
  • Empty File Guard (safe_csv_reader.py): Test what happens when expenses.csv exists on disk but is completely empty (0 bytes), and make sure your code handles it gracefully.

Frequently Asked Questions

Why are all values from csv.DictReader strings instead of numbers?

CSV is a plain text format without data type metadata, so Python reads every cell as a string. Convert columns like prices or quantities with float() or int() when you need to calculate totals.

Should I use csv.reader or csv.DictReader?

Use csv.DictReader whenever your CSV has a header row because reading row["amount"] is much clearer and less error-prone than row[3].

What if my file uses semicolons instead of commas?

Pass delimiter=";" when creating your reader or writer, for example csv.DictReader(file, delimiter=";").

Can Python read Excel (.xlsx) files with the csv module?

No. The csv module only reads plain text CSV files. To read .xlsx workbooks directly, you can export them as CSV from Excel or use a third-party library later in Phase 2.

Need to review how we built our first complete CLI application? Check out the previous lesson: Phase 1 Capstone: Building a Command-Line Inventory System. In our next article, Working with JSON Data: Python and Web Standards, you will learn how to parse and write JSON, the data format that powers modern web APIs.

Learning programming works best as a collaborative process, and your questions and insights help make these tutorials more effective for everyone. If you get stuck with CSV delimiters, run into an encoding error, or build a useful feature for the expense tracker, please share your experience in the comments. Your feedback helps create a learning community where we can all benefit from each other’s discoveries and solutions.

You Might Also Like

Comments

Loading comments…

Leave a Comment

0/5000