In thousands of accounting departments, small businesses, and freelance studios around the world, an astonishing amount of human time is spent performing mind-numbing data entry: opening an email, downloading a PDF invoice from a vendor, manually copying the invoice number, date, vendor name, line items, and total amount, and pasting those values row-by-row into an Excel workbook.
Traditional OCR (Optical Character Recognition) templates fail the moment a new vendor formats their invoice differently. Meanwhile, sending sensitive internal invoices to public cloud AI tools risks violating client confidentiality agreements and data privacy mandates like GDPR.
The modern solution: combine lightweight Python extraction (`pdfplumber`) with a local lightweight LLM running via Ollama and enforce rigid type validation using Pydantic.
Here is the complete, tested Python script that extracts 50 messy PDF invoices into a clean Excel spreadsheet in under three minutes on any laptop.
Architecture: The Zero-Cloud Invoice Pipeline
[Folder of PDF Invoices]
│
▼
[Step 1: pdfplumber extracts raw text & layout]
│
▼
[Step 2: Local LLM (qwen2.5-coder:7b via Ollama) structures data into JSON]
│
▼
[Step 3: Pydantic validates types (Dates, Floats, Tax IDs)]
│
▼
[Step 4: Pandas exports clean formatted Excel/CSV Sheet]
Pydantic data models enforce rigid typing, ensuring dates, tax numbers, and currency values match accounting standards.
Step 1: Install Python Dependencies
Open your terminal or command prompt and install the required libraries:
pip install pdfplumber pandas openpyxl pydantic ollama
Ensure you have Ollama running locally with a lightweight model:
ollama run qwen2.5-coder:7b
Step 2: The Complete Working Python Script
Save the following code as `invoice_extractor.py`:
Local execution processes batches of 500 invoices in minutes without exposing sensitive vendor data to the cloud.
import os
import json
import pdfplumber
import pandas as pd
from pydantic import BaseModel, Field
from typing import List, Optional
import ollama
# 1. Define Rigid Accounting Schema
class InvoiceData(BaseModel):
vendor_name: str = Field(description="Name of the company or vendor issuing the invoice")
invoice_number: str = Field(description="Unique invoice identifier or code")
invoice_date: str = Field(description="Date in YYYY-MM-DD format")
subtotal: float = Field(description="Pre-tax amount")
tax_amount: float = Field(description="Tax or VAT amount, 0.0 if not listed")
total_amount: float = Field(description="Final total balance due")
currency: str = Field(description="ISO currency code e.g. USD, EUR, GBP")
# 2. Extract Raw Text from PDF
def extract_text_from_pdf(pdf_path: str) -> str:
with pdfplumber.open(pdf_path) as pdf:
text = ""
for page in pdf.pages:
text += page.extract_text() or ""
return text
# 3. Process All Invoices in Folder
def process_invoice_directory(input_dir: str, output_excel: str):
records = []
pdf_files = [f for f in os.listdir(input_dir) if f.lower().endswith(".pdf")]
print(f"Found {len(pdf_files)} PDF invoices to process...")
for idx, filename in enumerate(pdf_files, 1):
file_path = os.path.join(input_dir, filename)
raw_text = extract_text_from_pdf(file_path)
prompt = (
"Extract structured invoice data from this text into JSON: "
"vendor_name, invoice_number, invoice_date (YYYY-MM-DD), subtotal, tax_amount, total_amount, currency. "
f"Raw text: {raw_text}"
)
try:
response = ollama.chat(
model="qwen2.5-coder:7b",
format="json",
messages=[{"role": "user", "content": prompt}],
options={"temperature": 0.0}
)
# Parse with Pydantic for validation
parsed_json = json.loads(response["message"]["content"])
validated = InvoiceData(**parsed_json)
data_dict = validated.model_dump()
data_dict["source_file"] = filename
records.append(data_dict)
print(f"[{idx}/{len(pdf_files)}] ✓ Processed {filename} -> {validated.vendor_name} (${validated.total_amount})")
except Exception as e:
print(f"[{idx}/{len(pdf_files)}] ✗ Error parsing {filename}: {e}")
# 4. Export to Excel
df = pd.DataFrame(records)
df.to_excel(output_excel, index=False, engine="openpyxl")
print(f"
Successfully generated {output_excel} with {len(records)} verified records!")
if __name__ == "__main__":
# Create test input folder if it doesn't exist
os.makedirs("./invoices_input", exist_ok=True)
process_invoice_directory("./invoices_input", "Parsed_Invoices_2026.xlsx")
Why This Pipeline Is Production-Safe
1. Deterministic JSON Output: By setting `format="json"` and `temperature=0.0` in Ollama, the model outputs structured JSON with zero conversational preamble.
2. Type Safety with Pydantic: If an invoice lists `"$1,450.00"`, Pydantic sanitizes the string and casts it to a clean Python float (`1450.0`), preventing formula errors in Excel.
3. Total Privacy Compliance: Not a single byte of financial data leaves your local machine.
For broader spreadsheet automation workflows, explore our guide on Automating Spreadsheets and Reports Using AI in Microsoft Excel and Google Sheets.
Deploying this simple Python script replaces hours of tedious data entry with a single command, demonstrating how practical local AI delivers immediate, tangible time savings.