RAGPDF parsingdocument ingestiondata pipelinesembeddings

Extracting Tables for RAG: Parsing PDFs and Spreadsheets

Pramod Dutta · Jun 20, 2026 · 17 min read

RAG tables extraction is where most retrieval pipelines quietly fail. A PDF-to-text loader flattens a pricing table into a wall of numbers with no columns, an embedding model turns that wall into a near-meaningless vector, and your retriever confidently returns the wrong row when someone asks "what's the price for the Enterprise tier." Tables carry structure that is the whole point of the data: rows relate to columns, headers give meaning to cells, and merged cells or multi-page tables add layout logic that plain text extraction throws away. This article covers how to extract tables from PDFs and spreadsheets correctly, how to represent them for embedding, and how to chunk them so retrieval actually works.

Why tables break naive RAG pipelines

Most RAG tutorials start with pypdf or pdfminer reading a PDF page and returning a single text blob. For prose, this is fine: sentences flow left to right, paragraph breaks are meaningful, and a text splitter can chop it into chunks without losing information. Tables do not work this way.

Consider a two-column PDF page with a table in the left column and a paragraph in the right column. Naive extraction reads left to right across the page, so it interleaves table rows with unrelated paragraph sentences. Or consider a table where the header row is "Region | Q1 Revenue | Q2 Revenue | Q3 Revenue" and the extractor drops the pipe-equivalent spacing, producing "Region Q1 Revenue Q2 Revenue Q3 Revenue North 4.2M 4.8M 5.1M South 3.1M 3.4M 3.9M" as one continuous string. A human can still parse this with effort. An embedding model cannot recover which number belongs to which region and which quarter, because the semantic structure (row and column association) has been destroyed.

There are three failure modes worth naming explicitly:

Column misalignment: text extraction reads in document order, not visual column order, so cells from different columns get interleaved.
Lost headers: a chunk boundary lands mid-table, and the retrieved chunk shows data rows with no header row above them, so the LLM has numbers with no labels.
Merged cells and spanning headers: a header like "Revenue by Quarter" spanning four columns, with sub-headers "Q1", "Q2", "Q3", "Q4" underneath, gets flattened into a sequence that loses the parent-child relationship.

The fix is not a smarter text splitter. The fix is extracting tables as structured objects (rows and columns, or at minimum a well-formed markdown table) before you ever touch chunking or embedding.

Extracting tables from PDFs

PDFs do not contain tables as a data type. A PDF is a page-description format: it has strings positioned at x/y coordinates and lines drawn between points. "Table" is a visual pattern a human recognizes, not metadata embedded in the file. So every PDF table extractor is really a layout-inference tool, and the approaches differ in how they infer structure.

Rule-based extraction with pdfplumber and Camelot

For PDFs with visible ruling lines or consistent whitespace gaps, rule-based extractors work well and are fast, free, and deterministic.

pdfplumber inspects character positions and can detect tables via explicit lines or via whitespace-based heuristics:

import pdfplumber

with pdfplumber.open("quarterly_report.pdf") as pdf:
    for page_num, page in enumerate(pdf.pages):
        tables = page.extract_tables()
        for table_idx, table in enumerate(tables):
            print(f"Page {page_num + 1}, table {table_idx + 1}")
            for row in table:
                print(row)

extract_tables() returns a list of tables, each a list of rows, each row a list of cell strings. You can tune detection with table_settings:

table_settings = {
    "vertical_strategy": "lines",
    "horizontal_strategy": "lines",
    "snap_tolerance": 3,
}
tables = page.extract_tables(table_settings)

Use "lines" when the PDF has visible grid lines (most invoices and financial statements). Switch to "text" for tables that rely on whitespace alignment instead of ruled borders, which is common in reports generated from Word or Google Docs.

Camelot is a good alternative, especially for tables with visible borders. It has two extraction flavors:

import camelot

# Lattice: for tables with ruling lines (most bordered tables)
tables = camelot.read_pdf("quarterly_report.pdf", pages="1-5", flavor="lattice")

# Stream: for tables without visible borders, using whitespace
tables = camelot.read_pdf("quarterly_report.pdf", pages="1-5", flavor="stream")

for table in tables:
    df = table.df  # a pandas DataFrame
    print(table.parsing_report)  # accuracy score, whitespace ratio
    print(df)

table.parsing_report returns an accuracy estimate, which is genuinely useful: you can set a threshold (say, reject anything under 80) and route low-confidence tables to a fallback extractor or a human review queue instead of silently ingesting garbage.

Rule-based tools are fast and cheap to run at scale, but they degrade on scanned documents, rotated tables, and tables with irregular spacing. For those, you need layout-model or vision-based extraction.

Layout-model extraction: Unstructured and Docling

unstructured and Docling (from IBM Research) use trained layout-detection models rather than pure geometry rules, so they handle a wider range of real-world PDFs including scanned ones once combined with OCR.

from unstructured.partition.pdf import partition_pdf

elements = partition_pdf(
    filename="quarterly_report.pdf",
    strategy="hi_res",
    infer_table_structure=True,
)

for element in elements:
    if element.category == "Table":
        print(element.metadata.text_as_html)

With infer_table_structure=True, unstructured runs a table-transformer model that outputs the table as HTML, preserving row and column spans. That HTML is far more useful downstream than a flat string, because you can parse it with pandas.read_html or feed it directly to an LLM, which reads HTML tables reliably.

Docling follows a similar pattern and is worth evaluating if you're processing a large, heterogeneous document set (mixed scanned and native PDFs), since it was built specifically around a unified document representation that keeps table structure, reading order, and layout metadata together rather than as an afterthought.

from docling.document_converter import DocumentConverter

converter = DocumentConverter()
result = converter.convert("quarterly_report.pdf")

for table in result.document.tables:
    df = table.export_to_dataframe()
    print(df)

Layout-model extraction costs more compute per page than rule-based extraction (they run a neural network per page instead of geometry math), so for a large corpus, a two-tier pipeline makes sense: run pdfplumber or Camelot first, check the accuracy score, and only fall back to a layout model for pages that score poorly.

Vision-based extraction with multimodal LLMs

For genuinely messy tables (scanned receipts, tables with handwriting, tables where cells contain small charts or icons), the most robust approach in 2026 is to render the page as an image and hand it to a multimodal model directly, asking it to output the table as markdown or JSON.

import base64
from anthropic import Anthropic
from pdf2image import convert_from_path

client = Anthropic()

pages = convert_from_path("scanned_invoice.pdf", dpi=200)
page_image_path = "/tmp/page_1.png"
pages[0].save(page_image_path)

with open(page_image_path, "rb") as f:
    image_data = base64.standard_b64encode(f.read()).decode("utf-8")

response = client.messages.create(
    model="claude-sonnet-4-5",
    max_tokens=4096,
    messages=[{
        "role": "user",
        "content": [
            {
                "type": "image",
                "source": {
                    "type": "base64",
                    "media_type": "image/png",
                    "data": image_data,
                },
            },
            {
                "type": "text",
                "text": (
                    "Extract every table on this page as GitHub-flavored "
                    "markdown. Preserve every row and column exactly as shown. "
                    "If a header spans multiple columns, repeat the parent "
                    "header text in each sub-column. Output only the "
                    "markdown tables, nothing else."
                ),
            },
        ],
    }],
)

print(response.content[0].text)

This approach handles rotated tables, low-contrast scans, and tables embedded inside charts far better than geometry-based tools, because the model is reasoning about the visual layout the same way a human would. The tradeoff is cost and latency: you're paying per-page inference instead of running a local library, so it's usually reserved for the subset of documents that rule-based and layout-model extraction score poorly on, rather than the default for an entire corpus.

Extracting tables from spreadsheets

Spreadsheets are easier than PDFs because the data is already structured as cells in rows and columns. The challenge shifts from "detect where the table is" to "handle the messiness spreadsheet authors introduce": merged cells, multiple tables on one sheet, formulas instead of values, and formatting used to convey meaning (bold headers, colored cells for "at risk" rows).

Reading Excel and CSV with pandas

For clean, single-table sheets, pandas handles the basics directly:

import pandas as pd

df = pd.read_excel("sales_data.xlsx", sheet_name="Q3")
print(df.head())

for sheet_name in pd.ExcelFile("sales_data.xlsx").sheet_names:
    df = pd.read_excel("sales_data.xlsx", sheet_name=sheet_name)
    print(f"{sheet_name}: {df.shape[0]} rows, {df.shape[1]} columns")

Real-world spreadsheets rarely start their table on row 1. Reports often have a title row, a blank row, then the header. Use skiprows and header to point pandas at the real table:

df = pd.read_excel("sales_data.xlsx", sheet_name="Q3", skiprows=2, header=0)

For CSVs, watch encoding and delimiter, both of which vary more than you'd expect from exported data:

df = pd.read_csv("export.csv", encoding="utf-8-sig", sep=None, engine="python")

sep=None with engine="python" triggers pandas' delimiter sniffer, which handles the common case of a CSV exported with semicolons instead of commas (common in European locales).

Handling merged cells with openpyxl

Pandas silently forward-fills or blanks merged cells depending on the reader, which loses information. If merged cells carry meaning (a category header spanning several rows), read the file with openpyxl directly and unmerge programmatically:

from openpyxl import load_workbook

wb = load_workbook("sales_data.xlsx", data_only=True)
ws = wb["Q3"]

for merged_range in list(ws.merged_cells.ranges):
    min_row, min_col, max_row, max_col = (
        merged_range.min_row, merged_range.min_col,
        merged_range.max_row, merged_range.max_col,
    )
    top_left_value = ws.cell(row=min_row, column=min_col).value
    ws.unmerge_cells(str(merged_range))
    for row in range(min_row, max_row + 1):
        for col in range(min_col, max_col + 1):
            ws.cell(row=row, column=col, value=top_left_value)

data_only=True reads the last-computed value of formula cells rather than the formula string itself, which matters because you almost always want "42000" in your RAG index, not "=SUM(B2:B12)".

Multiple tables on one sheet

Financial models and operational spreadsheets often pack several distinct tables onto one sheet, separated by blank rows or a title cell. There is no library that reliably auto-detects this for you, so a practical heuristic is to scan for fully blank rows and split on them:

def split_sheet_into_tables(df: pd.DataFrame) -> list[pd.DataFrame]:
    blank_mask = df.isna().all(axis=1)
    tables = []
    start = 0
    for i, is_blank in enumerate(blank_mask):
        if is_blank:
            if i > start:
                chunk = df.iloc[start:i].dropna(how="all", axis=1)
                if not chunk.empty:
                    tables.append(chunk)
            start = i + 1
    if start < len(df):
        tables.append(df.iloc[start:].dropna(how="all", axis=1))
    return tables

Run this after reading the raw sheet with header=None so blank-row detection isn't confused by a header pandas already consumed.

Choosing a table representation for embeddings

Once you have extracted a table as structured rows and columns, you have to decide how to represent it as text before embedding, because embeddings only take text (or image) input. Three representations are common, each with tradeoffs.

Markdown table. Compact, human-readable, and LLMs are extensively trained on markdown tables so they parse it reliably at query time.

| Region | Q1 Revenue | Q2 Revenue |
|--------|-----------|-----------|
| North  | 4.2M      | 4.8M      |
| South  | 3.1M      | 3.4M      |

Good default for most RAG pipelines. The main weakness: wide tables with many columns produce long lines that eat into your context budget per row, and very sparse tables (mostly empty cells) waste tokens on the pipe characters.

Row-as-sentence (denormalized). Convert each row into a natural-language sentence that repeats the column headers as labels. This is the best representation for embedding similarity search specifically, because the embedding model sees semantically meaningful text rather than pipe-delimited symbols:

def row_to_sentence(row: dict, table_name: str) -> str:
    parts = [f"{key}: {value}" for key, value in row.items()]
    return f"{table_name} - " + ", ".join(parts)

# "Quarterly Revenue by Region - Region: North, Q1 Revenue: 4.2M, Q2 Revenue: 4.8M"

This representation shines when your users ask row-specific questions ("what was North's Q1 revenue") because the row becomes its own retrievable unit with full context baked in, instead of depending on the retriever also grabbing the header row from a neighboring chunk.

JSON. Best when the downstream consumer is code, not a human reading a chat response, or when you need to preserve nested structure (a table where one column itself contains a list). LLMs handle JSON well too, but it's more token-expensive than markdown for the same information.

table_json = df.to_dict(orient="records")

A practical pattern: store the table as JSON in your metadata store (source of truth, used for exact lookups and re-rendering), but embed the row-as-sentence representation (used for semantic search), and generate the markdown representation on the fly when you inject the retrieved table back into the LLM's context for answer generation. Each representation is doing the job it's best at rather than one format serving three purposes badly.

Chunking strategy for tables

The single most common bug in table RAG is a chunk boundary that separates data rows from their header row. If your chunker treats a table as generic text and blindly splits every 500 tokens, a large table gets cut mid-body, and the second half of the table is retrieved with zero column labels.

The rule: never split a table across a semantic chunk boundary unless the table itself is larger than your context budget. Concretely:

If a table fits within your target chunk size (say, under 800 tokens as markdown), keep it as one atomic chunk. Do not merge it with surrounding paragraph text either, since mixing prose and table rows in one chunk dilutes the embedding for both.
If a table is larger than your chunk size, split by row groups, and repeat the header row at the top of every resulting chunk:

def chunk_large_table(rows: list[dict], header: list[str], rows_per_chunk: int = 20) -> list[str]:
    chunks = []
    for i in range(0, len(rows), rows_per_chunk):
        batch = rows[i : i + rows_per_chunk]
        lines = ["| " + " | ".join(header) + " |"]
        lines.append("|" + "|".join(["---"] * len(header)) + "|")
        for row in batch:
            lines.append("| " + " | ".join(str(row.get(h, "")) for h in header) + " |")
        chunks.append("\n".join(lines))
    return chunks

Attach table-level metadata (source document, page number, table caption or title if one exists) to every chunk, not just the first one. When the retriever returns chunk 4 of a 9-chunk table, the LLM still needs to know this data is "Quarterly Revenue by Region, page 14" to answer with proper context.
For tables with a natural grouping column (region, product, date range), consider chunking by group instead of by row count, so each chunk is a coherent subset a user is likely to ask about together.

Handling multi-page tables

Long tables that continue across PDF page breaks are a frequent source of broken extraction, because most PDF tools treat each page independently and have no concept that "this table continues from the previous page."

Two practical strategies:

Header repetition heuristic. After extracting tables page by page, compare the header row of table N+1 against table N. If they match (or are near-identical after whitespace normalization), treat them as one logical table and concatenate the row lists, discarding the duplicate header.

def merge_continued_tables(tables: list[list[list[str]]]) -> list[list[list[str]]]:
    merged = [tables[0]]
    for table in tables[1:]:
        prev_header = merged[-1][0]
        curr_header = table[0]
        if curr_header == prev_header:
            merged[-1].extend(table[1:])
        else:
            merged.append(table)
    return merged

Bottom-of-page / top-of-page detection. Some extractors expose bounding boxes. If a table on page N ends within a few points of the page's bottom margin, and a table on page N+1 starts within a few points of the top margin with a matching column count, flag them as a likely continuation even if the header wasn't literally repeated (some documents only print the header once, on the first page).

Neither heuristic is perfect, so log a warning whenever a merge decision is made and spot-check a sample during pipeline development. A silent, wrong merge is worse than a table staying split, because it can splice unrelated data together under one header.

Evaluating table extraction quality

Before wiring extraction into a production RAG pipeline, build a small labeled evaluation set: 20 to 50 representative tables from your actual document corpus, hand-verified against the source. For each extracted table, check:

Cell accuracy: does every extracted cell value match the source exactly, including decimals and currency symbols.
Structural accuracy: are row and column counts correct, are merged headers correctly propagated.
Row completeness: did the extractor drop or duplicate any rows, which happens more often than cell-level errors and is easy to miss with spot checks.

A lightweight scoring script comparing extracted markdown against a hand-written ground-truth markdown table (using a cell-by-cell diff) catches regressions when you upgrade a library version or swap extraction methods. Treat this eval set the same way you'd treat a retrieval eval set: rerun it whenever you touch the ingestion pipeline, not just once at launch.

Putting it together: a table-aware ingestion pipeline

A pipeline that handles both PDFs and spreadsheets end to end looks roughly like this:

def ingest_document(file_path: str) -> list[dict]:
    chunks = []

    if file_path.endswith(".pdf"):
        tables = extract_pdf_tables(file_path)  # pdfplumber/Camelot, fallback to vision model
        tables = merge_continued_tables(tables)
    elif file_path.endswith((".xlsx", ".xls")):
        tables = extract_spreadsheet_tables(file_path)  # openpyxl + split_sheet_into_tables
    elif file_path.endswith(".csv"):
        tables = [extract_csv_table(file_path)]
    else:
        raise ValueError(f"Unsupported file type: {file_path}")

    for table_idx, table in enumerate(tables):
        header, rows = table[0], table[1:]
        markdown = table_to_markdown(header, rows)

        if estimate_tokens(markdown) <= 800:
            table_chunks = [markdown]
        else:
            table_chunks = chunk_large_table(
                [dict(zip(header, row)) for row in rows], header
            )

        for chunk_idx, chunk_text in enumerate(table_chunks):
            chunks.append({
                "text": chunk_text,
                "embedding_text": row_group_to_sentences(chunk_text),
                "metadata": {
                    "source": file_path,
                    "table_index": table_idx,
                    "chunk_index": chunk_idx,
                    "content_type": "table",
                },
            })

    return chunks

Store text (the markdown) for injection into the LLM's context at answer time, embed embedding_text (the row-as-sentence version) for retrieval, and keep metadata for filtering and citation. Index prose chunks from the same documents through a separate path so table and text content don't get mixed into the same chunk type, since they benefit from different chunk sizes and different embedding representations.

FAQ

Which library should I start with for PDF table extraction? Start with pdfplumber if your PDFs are digitally generated (not scanned) with reasonably consistent layout. It's free, fast, and has no external dependencies beyond Python. Move to a layout-model tool like unstructured or Docling once you hit scanned documents or tables without visible ruling lines, and reserve vision-model extraction for the hardest cases where accuracy really matters and volume is manageable.

Should I always convert tables to markdown before embedding? No. Markdown is a good default for injecting into an LLM's context at answer time, but for the embedding step specifically, a row-as-sentence representation usually retrieves better because it reads as natural language rather than pipe-delimited symbols. Keep the two representations separate: one for search, one for context injection.

How do I handle a table that spans two pages with no repeated header? Use bounding-box position (table ends near the bottom margin, next table starts near the top margin on the following page) combined with a matching column count as your continuation signal. Log every automatic merge for manual review during development, since a wrong merge silently corrupts two unrelated tables into one.

Is OCR necessary for scanned PDF tables? Yes, if the PDF has no embedded text layer (a pure image scan), you need OCR before any table structure can be inferred. Tesseract works for simple cases; for tables specifically, an OCR engine paired with a layout model (or a vision-capable multimodal model that does OCR and layout understanding together) produces meaningfully better results than plain OCR followed by rule-based table detection.

How large should a table chunk be? Keep single chunks under roughly 800 tokens as markdown so the table fits comfortably alongside a user's query and other retrieved context without crowding out the model's context window. For tables larger than that, split by row groups and repeat the header in every chunk rather than relying on a generic text splitter that has no concept of table structure.

Do I need a different vector index for tables versus prose? Not necessarily a different index, but tag chunks with a content_type metadata field ("table" versus "prose") so you can filter or boost retrieval by type when a query looks tabular ("what is the price for X" versus "explain how X works"). Some teams do maintain a fully separate index for tabular data when volume is high enough to justify tuning embedding models or chunk sizes independently for each content type.

What about tables inside images embedded in a PDF, like a chart with a data table below it? Treat that region as an image and route it to a vision-capable multimodal model rather than trying to force a text-based table extractor to parse it. Rule-based and layout-model extractors work on the PDF's text and vector-graphics layer; a table baked into a raster image has no extractable text layer at all, so OCR or vision-model extraction is the only path.