Field notes · ai
Extracting Office documents for local RAG
A useful local RAG pipeline needs format-aware extraction, table-preserving chunks, and triage after more than 80% of apparent errors were harmless files.
Office-document extraction is where a tidy retrieval diagram meets reality. Paragraph-only code can make a Word file look empty, flatten a fee table into nonsense, or treat a temporary lock file as a production failure. I got better results by extracting each format deliberately and recording a verdict for everything I could not index.
The goal is not to turn every file into text at any cost. The goal is to preserve enough structure for retrieval while making missing coverage visible.
Key takeaways
- Use a format-specific extractor before falling back to generic conversion.
- Treat paragraphs, tables, headers, and footers as separate content sources.
- Distinguish born-digital PDFs from scanned pages that need OCR.
- Keep table rows together and repeat headers when a table must be split.
- Exclude temporary files before counting extraction failures.
- Produce a per-file verdict report instead of silently dropping content.
Why did paragraph extraction miss useful documents?
Many Word documents store their important content in tables, headers, footers,
text boxes, or images rather than body paragraphs. A parser that iterates only
document.paragraphs can return almost nothing even though the file looks full
when opened in Word.
This surfaced at scale in my pilot. Thousands of Word files produced no useful paragraph text. Some were truly empty templates or image-only scans. Many were table-driven forms and reports. The extraction code was answering the wrong question: “Does this document have paragraphs?” instead of “Where does this document store readable content?”
The official python-docx quickstart shows that tables expose their own iterable rows and cells. I changed the Word extractor to walk body paragraphs and tables, including nested tables, then add header and footer text.
A simplified pattern looks like this:
def table_text(table):
rows = []
for row in table.rows:
cells = [clean(cell.text) for cell in row.cells]
rows.append(" | ".join(cells))
return "\n".join(rows)
The real implementation also avoids repeated merged-cell text and walks nested table content. The important design choice is recognizing tables as structure, not as an accidental string between paragraphs.
How should each Office format be handled?
Use the native structure of modern formats where possible: DOCX paragraphs and tables, XLSX worksheets and cells, PPTX text frames, and PDF page text plus table detection. Route older binary Office formats through a separate conversion service instead of pretending a modern parser can open them.
My extraction matrix is intentionally explicit:
| Format | Primary extraction | Common failure |
|---|---|---|
| Page text plus detected tables | Scanned image has no text layer | |
| DOCX | Paragraphs, recursive tables, headers, footers | Important data exists only in tables or images |
| XLSX | Used worksheet cells rendered as tables | Sparse sheets and formulas lose context |
| PPTX | Text from slide shapes and notes where supported | Diagrams and embedded images carry meaning |
| TXT and CSV | Encoding-aware text read | Legacy encodings and delimiter ambiguity |
| Legacy Office | Generic document conversion service | Conversion availability and fidelity |
For text files, I try UTF-8 first and a constrained legacy encoding fallback. For spreadsheets, I render a used range as a Markdown-style table rather than joining every cell into one sentence. For presentations, I preserve slide boundaries so a chunk does not blend unrelated slides.
I also enforce an extraction-size cap. Very large documents need an explicit policy because they can monopolize OCR, conversion, memory, or embedding work. The cap is not a declaration that those files are unimportant. It is a routing decision that keeps the ordinary crawl bounded and reports what needs a separate path.
How do PDF tables change the extraction strategy?
Born-digital PDF tables often preserve enough lines and positioned text for table detection, while scanned tables require OCR that understands layout. A plain text extractor may recover every word and still destroy the row and column relationships that make the table meaningful.
PyMuPDF’s official Page.find_tables() documentation
describes table detection strategies based on vector lines or positioned text.
Its table object can return cell data or Markdown. I use ordinary page text for
narrative content and a table representation for detected grids.
The difference matters for a lookup such as:
| Service | Weekday fee | Weekend fee |
|---|---|---|
| Example A | 25 | 40 |
| Example B | 15 | 30 |
Flattened extraction might produce Service Weekday fee Weekend fee Example A 25 40 Example B 15 30. A model can guess the relationships, but retrieval has
already made the evidence harder to use. Markdown preserves a compact structure
that both keyword and semantic retrieval can understand.
Scanned tables are a different problem. Generic OCR can recover cell words in reading order without reconstructing the grid. I mark those documents as needing table-aware reprocessing rather than quietly presenting the flattened result as equivalent.
How should tables be chunked for retrieval?
Keep a table whole when it fits the token budget. When it does not, split at row boundaries and repeat the header in each chunk. Never split in the middle of a row merely because the next token window begins there.
My chunker uses ordinary overlapping token windows for narrative text and a separate path for Markdown tables:
small table -> one chunk
large table -> header + rows that fit
header + next rows that fit
header + remaining rows
Repeating the header adds tokens, but it preserves meaning. A chunk containing
Example B | 15 | 30 is ambiguous without the column names. The repeated header
makes that chunk independently retrievable and understandable.
I still enforce a hard token cap after table splitting. A single pathological row may contain paragraphs of text, and no table rule should be allowed to overflow the model’s context budget.
The companion architecture in Building Private AI Search for a Windows File Share stores the source path and chunk index with every result, so a table answer can still point back to the complete document.
Why were most apparent extraction errors not real errors?
More than four-fifths of one early error report consisted of Office temporary
lock files whose names begin with ~$. They were not damaged records. They were
short-lived coordination files created while a document was open, and they
should have been excluded before extraction.
That finding changed how I read pipeline metrics. An “error count” is not a quality measure until expected noise is classified. I added exclusions for temporary files and separated genuine extractor exceptions from unreadable, unsupported, and intentionally oversized files.
The verdicts serve different operators:
- Error: engineering should inspect the parser or file.
- Unreadable: a records owner may need OCR or a replacement copy.
- Unsupported: the roadmap needs another extractor or converter.
- Oversize: the file needs a separate bounded workflow.
- Excluded temporary: no action is needed.
I keep the source identifier, extension, verdict, and sanitized detail in a CSV report. I do not store extracted document text in that operational report. The report is useful for coverage without becoming another uncontrolled copy of the records.
How do duplicates distort a RAG index?
Duplicate files inflate chunk counts, consume embedding time, and can make one answer look more strongly supported simply because the same text exists in many folders. Content-hash deduplication removed roughly 30 percent of candidate documents in my pilot and cut the stored chunks from tens of thousands to a much smaller set.
Path-based deduplication is not enough. A copied file has a different path but the same content. I calculate a normalized content hash after extraction, retain one indexed source according to a deterministic rule, and record the other path as a duplicate verdict.
There is a tradeoff. Two identical files may have different permissions or records meaning. I do not merge their authorization blindly. The deduplication policy must preserve a safe permission boundary and enough path information to explain which copy was indexed.
This is another reason extraction and indexing should be separate stages. The extractor reports what it found. The indexer decides whether that content is new, authorized, and worth embedding.
What should be verified before calling extraction complete?
Verify representative files from every supported format, include table-heavy and image-only fixtures, compare extracted output with what a human sees, and account for every candidate in a verdict report. A successful parser return is not proof that the important content survived.
My minimum fixture set includes:
- A normal PDF with headings and paragraphs.
- A born-digital PDF with a multi-column table.
- A scanned PDF that should be routed to OCR.
- A DOCX with body text, nested tables, headers, and footers.
- An XLSX with sparse cells and more than one worksheet.
- A PPTX with multiple text shapes.
- A temporary Office lock file that must be excluded.
- An unreadable or malformed file that must fail cleanly.
For each fixture, I assert the verdict, expected anchor text, table headers, and source metadata. I also inspect a rendered sample because structure loss can produce text that passes an assertion while remaining useless to a reader.
Frequently asked questions
Extraction quality sets the ceiling for retrieval quality. A better model cannot recover a column relationship or paragraph the pipeline discarded.
Should every unsupported document be sent to OCR?
No. OCR is expensive and only helps image-based content. Use format detection and a triage verdict to distinguish scans from encrypted, corrupt, empty, or unsupported files.
Is Markdown the best storage format for extracted tables?
It is a practical representation for retrieval because headers and rows remain visible in plain text. Keep original cell coordinates or richer metadata if the application needs exact table reconstruction.
Can generic document conversion replace native parsers?
It is a valuable fallback for legacy formats, but native parsers usually expose more structure and clearer failure modes. I prefer native extraction first and a generic converter as a separately monitored path.