Turn Scanned Documents into Data with Python (OCR, DuckDB, RAG)

Transcript

Before we start: all the code in this video is free on GitHub. The link is in the description. Every lesson has the code, a line by line walkthrough, and the real output. To get it, scroll back up, open the Code menu, and copy the clone link from here.

This project lives in data-engineering/build-lab-02-documents-to-data. Open its walkthrough, run the commands under Run it, and follow along with me. Here's everything you'll need. All of it is free.

Python 3.10+: runs every step of the pipeline. VS Code: the editor we write it in. Tesseract OCR: reads the text in each scan.

Pillow: makes and opens the invoice images. pandas: turns records into a table. DuckDB: runs SQL right on the Parquet file. fastembed: turns text into embeddings.

LanceDB: stores and searches the vectors. Here's where we start: a folder of ten scanned invoices. To a computer, they're just pictures. Here's where we finish.

The same invoices answer SQL. Total spend by vendor: Northwind Office Supply leads, at $1,449. And they answer plain English. Ask which invoices were for caffeine, and you get both Harbor Coffee invoices back, with their invoice numbers as sources.

The word caffeine isn't on either one. That's search by meaning. The plan: read the scans with OCR, parse the text into fields, store them as Parquet for SQL, then embed the same text for retrieval. One folder of documents, two consumers.

Let's build it. Up close, a PNG is a grid of pixels, and each one is only a brightness value. There are no letters in there. Finding them is the job of OCR, optical character recognition.

File one: ocr.py. Path finds the files. Pytesseract is a thin wrapper around Tesseract, a free, open source OCR engine.

Pillow's Image opens the pictures. For every PNG, sorted so every run is repeatable, we open the image and convert it to grayscale: one channel of brightness. Then the key line. Tesseract finds the lines of text, and a neural network recognizes the characters.

We keep each text under its file name. Pytesseract runs the same program you can call yourself. Here it is on the first invoice, skipping blank lines. Vendor, invoice number, date, line items and the total, all as plain text.

It isn't perfect: on one invoice it reads a small i. The next step has to be forgiving. Raw text is one long string. A table or a query needs the same named fields for every invoice.

That's a schema. File two: parse.py, with regular expressions. FIELDS maps each field to a pattern.

The parentheses capture the part we keep. Invoice number: INV, a dash, and digits. Date: four digits, two, and two. Total: a dollar amount after the word TOTAL.

Inside parse, we drop blank lines, and the first line is the vendor. Each pattern searches the text, ignoring case, and a field that isn't found becomes None instead of crashing. The total loses its commas and becomes a real number. And we keep the full text.

Search will need it later. A small helper in scripts parses one invoice. Let's try the one with the small i. Ignoring case found it.

Vendor, number, date, and a float for the total. Real documents vary far more, so production adds layout-aware models and flags records with missing fields. File three: main.py ties it together.

OCR every invoice, parse every text, and save the records as JSON, handy for debugging or another service. For analytics we want a table: one row per invoice, and dates as a real date type. Then we write Parquet. It stores each column together, typed and compressed, so a query reads only the columns it needs.

A quick check counts the fields we found: four fields, ten invoices. And DuckDB runs SQL straight on the file. Spend by vendor, top three, with no database server. Let's run it.

Ten invoices read. Forty out of forty fields found. Northwind Office Supply leads with $1,449, then Cobalt Cloud Hosting and Summit Freight. Pandas, Spark and most warehouses read this same file, which makes Parquet the usual hand-off between pipelines.

SQL needs you to know the column. People ask in their own words, and a keyword search for caffeine finds nothing. File four: embed.py.

An embedding turns text into numbers that capture its meaning. BGE small is an open model. It runs on your CPU with no API key, after a one-time download of about sixty-four megabytes. Each text becomes 384 numbers, and similar meanings point in similar directions.

File five: store.py reads the same Parquet file DuckDB queried, and adds a vector column. LanceDB saves vectors and metadata side by side in a local folder. Like DuckDB, there's no server.

File six: search.py builds the index and asks our question. The question is embedded with the same model, and LanceDB returns the two nearest invoices by cosine distance. One minus the distance is the similarity.

Closer to one means closer in meaning. Then the prompt. Each retrieved text is tagged with its invoice number, so the answer can cite it. We don't call a model here.

In production, this prompt goes to the LLM, and it answers only from what we retrieved. Let's run it. Ten invoices embedded. The top two are both Harbor Coffee, espresso beans and cold brew, at 0.

75 and 0.73. The prompt carries both, with their sources. Neither invoice contains the word caffeine.

One honest note: unrelated invoices still score above 0.6, so the ranking matters more than the number. Let's recap. Tesseract turned pixels into text, patterns turned text into fields, and Parquet made them a table DuckDB can query.

Then the same text became vectors, so plain English questions find the right invoices, with sources. To go further, flag records with missing fields, chunk long documents before embedding, and send the prompt to a model. One folder of documents, two consumers. That's the whole project.