Turn scanned invoices into SQL data with Python OCR

These invoices are just pictures. To a computer, a scan is only pixels: a grid of brightness values, with no letters inside. In three Python files, they become data you can query with SQL. This is Build Lab 02, a project you can build over a weekend: pixels to text, text to fields, fields to a table.

The plan

1. OCR turns pixels into characters.
2. Parse turns raw text into a schema: the same named fields (vendor, invoice number, date, total) for every invoice.
3. Store and query: write the records as Parquet, a columnar, typed and compressed file, and ask DuckDB questions in SQL, with no database server.

Our folder holds ten scanned invoices from six vendors. A small script drew them with Pillow, with a slight tilt, blur and paper noise so they look scanned and OCR has real work to do. It uses a fixed seed, so every run is identical.

You need Tesseract, which is a program rather than a Python package (brew install tesseract on macOS, sudo apt install tesseract-ocr on Debian or Ubuntu), plus the Python packages:

pip install pillow pytesseract pandas pyarrow duckdb

File one: ocr.py

Pytesseract calls Tesseract, a free OCR engine. Optical character recognition: pixels in, characters out.

from pathlib import Path
 
import pytesseract
from PIL import Image
 
 
def ocr_folder(folder):
    texts = {}
    for png in sorted(Path(folder).glob("*.png")):
        img = Image.open(png).convert("L")
        # Tesseract: pixels in, characters out
        text = pytesseract.image_to_string(img)
        texts[png.name] = text
    return texts

For every PNG in the folder, in a fixed order, we open it and convert it to grayscale ("L" is one brightness channel). Tesseract finds each line of text, then recognizes it character by character. We keep each file's text, keyed by its name.

File two: parse.py

Raw text is one long string. We want a schema: the same named fields for every invoice. Each field is a pattern.

import re
 
FIELDS = {
    "invoice_no": r"Invoice No:\s*(INV-\d+)",
    "date": r"Date:\s*(\d{4}-\d{2}-\d{2})",
    "total": r"TOTAL\s*\$([\d,]+\.\d{2})",
}
 
 
def parse(text):
    lines = [ln.strip() for ln in text.splitlines()]
    lines = [ln for ln in lines if ln]
    rec = {"vendor": lines[0]}
    for name, pattern in FIELDS.items():
        m = re.search(pattern, text, re.I)
        rec[name] = m.group(1) if m else None
    if rec["total"]:
        amount = rec["total"].replace(",", "")
        rec["total"] = float(amount)
    rec["text"] = " ".join(lines)
    return rec

Invoice number: INV-, then digits. The date: year, month, day. The total: a dollar amount after TOTAL. The part in parentheses is what gets kept. In parse, we drop the blank lines, and the first line is the vendor.

Each pattern searches the text ignoring case, and that flag is there for a real reason. OCR isn't perfect: on INV-2048, Tesseract reads "Invoice No:" with a small i. Without re.I, that invoice would lose its number. A field that isn't found becomes None instead of crashing, so a bad scan shows up as a gap you can count.

The total becomes a real number ("1,168.00" becomes 1168.0), and we keep the full text as one line. Part two needs it.

File three: main.py

import json
import duckdb
import pandas as pd
from ocr import ocr_folder
from parse import parse
 
texts = ocr_folder("invoices")
records = [parse(t) for t in texts.values()]
with open("data/docs.json", "w") as f:
    json.dump(records, f, indent=2)
 
df = pd.DataFrame(records)
df["date"] = pd.to_datetime(df["date"])
df.to_parquet("data/docs.parquet", index=False)
ok = df.drop(columns="text").notna()
n, t = ok.sum().sum(), ok.size
print(f"{len(df)} invoices read, {n}/{t} fields")
sql = """SELECT vendor, sum(total) AS spend
FROM 'data/docs.parquet'
GROUP BY vendor ORDER BY spend DESC LIMIT 3"""
for vendor, spend in duckdb.sql(sql).fetchall():
    print(f"{vendor:<26}{spend:>9,.2f}")

OCR every scan, parse every text, and save the records as JSON. Then pandas builds a table, dates become real dates, and we write Parquet. Parquet stores each column together, typed and compressed, so tools read only the columns they need.

A quick check counts the fields we found: 4 fields times 10 invoices is 40. Then DuckDB runs SQL right on the Parquet file. Spend by vendor, top three, no database server.

The run

cd docs_pipeline
python3 main.py

10 invoices read, 40/40 fields
Northwind Office Supply    1,449.00
Cobalt Cloud Hosting       1,168.00
Summit Freight               745.00

Ten invoices read. Forty out of forty fields found. Northwind Office Supply leads with $1,449.00, then Cobalt Cloud Hosting, then Summit Freight.

One table, two consumers

Here's the real payoff. This one table can now serve two consumers. SQL analytics, like the query above: DuckDB, pandas, Spark and most warehouses read Parquet. And AI retrieval: the same text column feeds an embedding model.

That's Part 2 of the Build Lab. Each invoice's text becomes a vector of 384 numbers with the open bge-small-en-v1.5 model (via fastembed), stored in LanceDB. Asked "Which invoices were for caffeine?", it returns the two Harbor Coffee Roasters invoices, INV-2043 (0.75) and INV-2049 (0.73), even though the word caffeine appears in neither. Those two invoices then go into a prompt, tagged with their invoice numbers, so an answer can cite its sources.

What this version simplifies

• Clean scans. These invoices share one layout and one font, so a few regular expressions find every field. Real documents vary, and production pipelines add layout-aware extraction or a model for fields, plus checks that flag records with missing values (None here) for a person to review.
• Count, don't assume. The 40/40 line is a tiny data-quality check. Keep one like it in any OCR pipeline: when a new batch of scans prints 37/40, you know before anyone queries the table.
• Tesseract version. The video used Tesseract 5.5.2. Check yours with tesseract --version.

Try this

1. Add an items field to FIELDS that captures the first line item after Description. How many of the 10 invoices does your pattern match?
2. Remove re.I from the search and rerun. Which field count drops, and on which invoice?
3. Write a second SQL query: spend by month, using the real date type pandas gave the date column.

Pixels, text, fields, table. Scans in, SQL out. The full project, with the invoice images and the script that drew them, is in the repo under data-engineering/build-lab-02-documents-to-data, and the complete build is also a 7-minute YouTube walkthrough (6:53).