Document Processing

Document Processing

When to Use

• User wants to read, create, edit, merge, split, rotate, watermark, or extract content from PDF files
• User wants to open, read, edit, create, or format Excel/spreadsheet files (.xlsx, .xlsm, .csv, .tsv)
• User needs to build a financial model, clean tabular data, or automate spreadsheet generation
• User needs OCR on scanned documents or table extraction from PDFs
• User asks to convert between document formats where the output is a PDF or spreadsheet file

When NOT to Use

• Database ingestion pipelines or ETL workflows → use data-engineering
• Log file parsing, monitoring dashboards, or structured log analysis → use observability
• Primary deliverable is a Word document, HTML report, or standalone Python script
• Google Sheets API integration (no local file involved)

PDF Processing

Library Selection

Merge PDFs with pypdf

from pypdf import PdfWriter, PdfReader

writer = PdfWriter()
for pdf_file in ["doc1.pdf", "doc2.pdf", "doc3.pdf"]:
    reader = PdfReader(pdf_file)
    for page in reader.pages:
        writer.add_page(page)

with open("merged.pdf", "wb") as f:
    writer.write(f)

Extract Table with pdfplumber

import pdfplumber
import pandas as pd

with pdfplumber.open("document.pdf") as pdf:
    all_tables = []
    for page in pdf.pages:
        for table in page.extract_tables():
            if table:
                df = pd.DataFrame(table[1:], columns=table[0])
                all_tables.append(df)

combined = pd.concat(all_tables, ignore_index=True)
combined.to_excel("extracted_tables.xlsx", index=False)

Create PDF with reportlab

from reportlab.lib.pagesizes import letter
from reportlab.platypus import SimpleDocTemplate, Paragraph, Spacer
from reportlab.lib.styles import getSampleStyleSheet

doc = SimpleDocTemplate("report.pdf", pagesize=letter)
styles = getSampleStyleSheet()
story = [
    Paragraph("Report Title", styles['Title']),
    Spacer(1, 12),
    Paragraph("Body content goes here.", styles['Normal']),
]
doc.build(story)

ReportLab subscripts/superscripts: Never use Unicode characters (₀₁₂, ⁰¹²) — built-in fonts render them as black boxes. Use XML tags instead: H<sub>2</sub>O, x<super>2</super>.

OCR Pattern with pytesseract

import pytesseract
from pdf2image import convert_from_path

images = convert_from_path("scanned.pdf")
text = ""
for i, image in enumerate(images):
    text += f"--- Page {i+1} ---\n"
    text += pytesseract.image_to_string(image) + "\n\n"
print(text)

CLI Alternatives

Spreadsheet Processing

pandas vs openpyxl

import pandas as pd

df = pd.read_excel("file.xlsx", sheet_name=None)  # All sheets as dict
df["Sheet1"].describe()
df["Sheet1"].to_excel("output.xlsx", index=False)

from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font, PatternFill, Alignment

wb = load_workbook("existing.xlsx")
ws = wb.active

ws["A1"] = "Label"
ws["B1"] = "=SUM(B2:B10)"                    # Always formulas, never hardcoded values
ws["A1"].font = Font(bold=True, color="000000")
ws.column_dimensions["A"].width = 20

wb.save("output.xlsx")

Warning: Loading with data_only=True then saving permanently replaces formulas with static values.

Formula-First Philosophy

Never hardcode computed values in Python — let Excel calculate them. The spreadsheet must recalculate when source data changes.

# WRONG
sheet["B10"] = df["Sales"].sum()        # Hardcodes 5000

# CORRECT
sheet["B10"] = "=SUM(B2:B9)"           # Excel owns the calculation
sheet["C5"] = "=(C4-C2)/C2"            # Growth rate as formula
sheet["D20"] = "=AVERAGE(D2:D19)"      # Average as formula

Financial Model Color Coding

Number Formatting Standards

Formula Verification

Before building the full model, test 2–3 sample cell references manually. Then verify:

• NaN handling: pd.notna(value) before writing references
• Division by zero: wrap denominators (=IF(B5=0, 0, A5/B5))
• Row indexing: DataFrame row 5 = Excel row 6 (1-indexed)
• Cross-sheet references: =Sheet1!A1 format
• After saving, run python scripts/recalc.py output.xlsx to recalculate and detect #REF!, #DIV/0!, #VALUE!, #NAME?

Quick Reference

Verification Checklist

PDF

• Correct library chosen for the task (merge → pypdf, extract → pdfplumber, create → reportlab)
• Output file opens without errors and page count is correct
• No Unicode subscript/superscript characters used in reportlab Paragraphs
• OCR output spot-checked against source image for accuracy

Spreadsheet

• Zero formula errors: #REF!, #DIV/0!, #VALUE!, #NAME? all absent
• All calculations use Excel formulas, not Python-hardcoded values
• Financial model color coding applied (blue inputs, black formulas, green cross-sheet)
• Number formats match standards (currency units in headers, zeros as −, negatives in parentheses)
• scripts/recalc.py run after saving and output shows "status": "success"
• Cell references verified for row offset (DataFrame → Excel is 1-indexed)
• Hardcoded source values documented with Source comment (system, date, reference)