Python CSV and Excel Data Processing

Tabular formats like CSV and Excel are standards for reporting, analytics, and business data exchange. Python provides native tooling via the csv module as well as high-performance third-party libraries like pandas and openpyxl to parse and transform structured tables.

This guide covers reading, writing, and interconverting CSV and Excel datasets with clean, reproducible examples.

1. Working with CSV Files in Python

CSV (Comma-Separated Values) files store tabular data as plain text. The standard library csv module provides streaming readers and writers that handle escaping, delimiters, and row iteration without external dependencies.

Write Data to CSV (csv.DictWriter)

              
            
Python
Write a list of dictionaries to a CSV file
import csv

employees = [
    {"id": 101, "name": "Liam", "role": "Backend Engineer"},
    {"id": 102, "name": "Sophia", "role": "Data Analyst"}
]

with open("employees.csv", mode="w", newline="", encoding="utf-8") as file:
    writer = csv.DictWriter(file, fieldnames=employees[0].keys())
    writer.writeheader()
    writer.writerows(employees)

print("employees.csv written successfully.")

Read Data from CSV (csv.DictReader)

              
            
Python
Read CSV data into Python dictionaries
import csv

with open("employees.csv", mode="r", encoding="utf-8") as file:
    reader = csv.DictReader(file)
    for row in reader:
        print(f"{row['name']} -> {row['role']}")

2. Working with Excel Files Using openpyxl

Excel (.xlsx) files store multiple sheets, formulas, and formatting. The openpyxl library provides direct read/write access to Excel workbooks and cell grids.

Create and Write an Excel Workbook

              
            
Python
Generate an .xlsx workbook with openpyxl
from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.title = "Inventory"

# Add header row
ws.append(["Product", "Quantity", "Price"])

# Add record rows
ws.append(["Laptop", 15, 1200.00])
ws.append(["Monitor", 30, 300.00])

wb.save("inventory.xlsx")
print("inventory.xlsx saved.")

Read Records from an Excel Sheet

              
            
Python
Read cell values row-by-row from .xlsx
from openpyxl import load_workbook

wb = load_workbook("inventory.xlsx")
ws = wb["Inventory"]

for row in ws.iter_rows(values_only=True):
    print(row)

3. High-Performance Conversions with pandas

When converting between CSV and Excel formats, pandas removes the boilerplate by loading datasets into memory as DataFrames and exporting them to the target format in one line.

CSV to Excel Conversion

              
            
Python
Convert CSV directly to XLSX via pandas
import pandas as pd

# Read CSV
df = pd.read_csv("employees.csv")

# Export to Excel sheet without the auto-generated row index
df.to_excel("employees.xlsx", sheet_name="Team", index=False)
print("Converted employees.csv to employees.xlsx")

Excel to CSV Conversion

              
            
Python
Convert specific Excel sheet to CSV via pandas
import pandas as pd

# Read specific worksheet
df = pd.read_excel("inventory.xlsx", sheet_name="Inventory")

# Export directly to CSV
df.to_csv("inventory.csv", index=False)
print("Converted inventory.xlsx to inventory.csv")

4. Complete Pipeline: Raw Data to CSV and Excel

              
            
Python
End-to-end data pipeline demonstrating dual format exports and validation
import pandas as pd

# Raw in-memory records
sales_data = [
    {"order_id": "ORD001", "item": "Mechanical Keyboard", "units": 4, "unit_price": 89.99},
    {"order_id": "ORD002", "item": "Ergonomic Mouse", "units": 10, "unit_price": 49.50},
    {"order_id": "ORD003", "item": "USB-C Hub", "units": 8, "unit_price": 29.99}
]

# 1. Load into DataFrame and compute calculated column
df = pd.DataFrame(sales_data)
df["total_revenue"] = df["units"] * df["unit_price"]

# 2. Export to CSV
csv_path = "sales_report.csv"
df.to_csv(csv_path, index=False)
print(f"Exported: {csv_path}")

# 3. Export to Excel with custom sheet
xlsx_path = "sales_report.xlsx"
df.to_excel(xlsx_path, sheet_name="Q1 Sales", index=False)
print(f"Exported: {xlsx_path}")

# 4. Verify CSV round-trip
reloaded_csv = pd.read_csv(csv_path)
print("\nReloaded DataFrame Verification:")
print(reloaded_csv[["order_id", "item", "total_revenue"]])

Conclusion

Processing tabular data in Python scales cleanly depending on project scope. Use the built-in csv module for lightweight, zero-dependency processing, openpyxl for fine-grained workbook cell manipulation, and pandas for fast conversions, filtering, and cross-format exports.