Turning PDFs, Scans and Spreadsheets Into Text a Bot Can Search
The hardest part of a document assistant is rarely the model. It's the pile of files it has to read. One knowledge base usually mixes born-digital PDFs, phone photos of paper, contracts exported from a signing tool and spreadsheets nobody ever documented. Retrieval quality gets decided during ingestion. Whatever the extractor loses, the assistant can never cite. Below are the calls we make when building this pipeline for a customer, cloud or entirely on their own hardware.
Know What Kind of File You Are Actually Holding
Three families, three behaviours: text-layer PDFs, image-only scans, structured tabular files. A PDF is a container, not a format, so the same extension can hold clean text, a photocopy, or both on alternating pages. I've seen a single 200-page contract flip between the two four times. Cheap triage before extraction beats running OCR on everything.
- Extractable character count per page
- Ratio of text to image area
- Page rotation
- Language detection
- Scanned-page flag
Tip: store the routing decision next to the document, so you can re-run only files that took the wrong path.
Getting Clean Text Out of PDFs
Layout carries meaning. Two-column papers, footnotes and running headers scramble reading order when extracted naively. Repeated headers, footers and page numbers pollute chunks and dilute embeddings, so strip them by frequency across pages. Keep what a reader relies on: headings, section numbers, list markers and the page reference used for citation. And watch the small stuff. Ligatures, soft hyphens and line-break hyphenation quietly break keyword search - nobody notices until a search for a word that's plainly on the page returns nothing. Preserve the mapping from extracted text back to page and coordinates, and an answer can point straight at its source.
Scans and OCR: Where Accuracy Is Won or Lost
OCR is a pre-processing problem first: deskew, denoise, correct rotation, rescue low-contrast photocopies. Handwriting, stamps, signatures and embedded tables each need handling different from body text. Language settings matter too, because the wrong model quietly mangles Polish, German or Nordic diacritics. Quietly is the operative word - it doesn't error out, it just gives you words that almost exist.
- Per-page confidence scores
- Dictionary hit rate and ratio of non-word tokens
- Empty-page detection
- A manual spot-check sample
Flag low-confidence pages for human review instead of letting the assistant answer from garbage. And since OCR is the heaviest step by a wide margin, it's what drives hardware sizing in on-premise deployments.
Spreadsheets Are Not Documents
Dumping a sheet as CSV into an embedding model throws away the thing that made it useful: the relationships between rows and columns. Merged cells, multi-row headers, hidden columns, formulas versus cached values, five sheets per workbook. All of it complicates the job. Two strategies actually work. Serialize each row into a self-describing sentence carrying its column names, or keep the table structured and answer over it with generated queries. Narrative lookups suit serialization. Sums, counts and comparisons need real query execution, not retrieval - no embedding model is going to add a column for you. Carry units, currencies and date formats explicitly.
Chunking and Metadata That Make Retrieval Work
Chunk on structure, not a fixed character count. Section and heading boundaries beat arbitrary windows every time. Overlap helps continuity but inflates the index, so tune it against real customer questions rather than whatever the default was. Every chunk should carry source file, page or sheet, section heading, document date, version and access group. Put access control in the index from day one; filtering after generation arrives far too late. Tip: build a small evaluation set of real questions with known answer locations before tuning anything, otherwise chunking changes are guesswork. A stable chunk identifier lets answers name their exact origin.
Running the Pipeline in Production
Ingestion is a recurring job, not a one-off import. Documents get replaced, superseded, deleted. Detect changes by content hash, re-process only what moved, and remove stale chunks so the assistant stops quoting withdrawn versions. Log every stage - then a bad answer traces back to the extraction that caused it. Cloud or on-premise, the pipeline stays the same; what changes is where OCR and embedding run and what leaves the network. Plan for corrupt files, password-protected PDFs and oversized workbooks, then surface them to someone instead of skipping silently. Silent skips are how a corpus ends up with a hole in it that nobody finds for six months.
FAQ
Do we need OCR if our PDFs came from a computer?
Usually yes. Archives drift over time, and digital files often contain scanned attachments, signed pages or faxed appendices. Triage decides per page, not per file, so only the pages that need OCR get it.
How do we know the extraction was good enough?
Combine confidence signals with sampling, then measure against an evaluation set of real questions whose correct sources you already know. If retrieval can't find a passage you can see with your own eyes, extraction is the suspect.
Can this run without sending documents to an external provider?
Yes. OCR and embedding models both run locally, keeping the corpus inside the network. The trade-off is hardware: you size GPUs and storage for peak ingestion, and throughput becomes something you plan rather than buy.
What to Take Away
Ingestion quality sets the ceiling for everything the assistant does afterwards. Route files by what they truly are, keep structure and provenance intact, treat spreadsheets as data rather than prose. Build the evaluation set early and re-run it after every pipeline change. Start with the document types carrying the questions people actually ask, then widen the corpus once those answers hold up.
