Skip to content

Read spreadsheets, documents, and PDFs

Full step  about 7 min

Spreadsheets, Word documents, and PDFs are ubiquitous across research institutions, but they represent compound binary or presentation formats rather than plain text. In this exercise, you direct the agent to extract information from all three formats, audit the structural compromises inherent in each extraction pipeline, and evaluate when converting to plain text at the source is preferable.

Understanding parsing trade-offs is essential: because language models operate strictly on extracted text streams, any table misalignment or merged-cell distortion introduced during extraction can produce silent calculation errors.

Before you start: A project directory containing office or PDF files, with an active agent session (Setup).

Background

An agent cannot inspect visual layouts directly in a terminal. It executes specialized parsing programs to convert binary files into raw text strings before evaluating them. The reliability of subsequent analyses depends directly on this conversion step.

Format Internal structure Standard extraction utility Common parsing vulnerabilities
.xlsx Multi-sheet XML cells, formulas, formatting openpyxl, pandas Unhandled title blocks above headers, merged cell ranges, hidden sheets, formula strings vs. evaluated values
.docx Paragraph trees, style runs, embedded tables python-docx, pandoc Tracked changes, margin comments, floating callout boxes, nested tables
.pdf (Digital) Explicit 2D glyph coordinates pdftotext, pypdf Disrupted multi-column reading order, unaligned table columns, fragmented footnotes
.pdf (Scanned) High-resolution raster images OCR tools (e.g., ocrmypdf, tesseract) Character recognition noise, table structure erasure, high compute latency

You do not need to manually research extraction utilities. State the target file and research question; the agent will select and install the necessary libraries within the project environment (Environment). For the broader representation hierarchy, consult Inputs.

General methodology

Use a prompt pattern that couples the extraction task with an explicit structural audit:

Inspect <file>. Report the extraction utility used and flag any structural complexities or lossy conversions (e.g., merged cells, multi-column layouts, images, or scanned pages). Then address the following analytical objective: <objective>.

Requiring the agent to declare its extraction tool and report structural anomalies ensures potential parsing errors remain transparent. When data will be analyzed repeatedly across a project, instruct the agent to export an intermediate plain-text snapshot to a text/ directory (CSV for tabular sheets, Markdown for text documents). Reading structured plain text is faster, cheaper, and more dependable than re-parsing binary containers on every turn.

Representative scripts executed by the agent (for reference):

# XLSX: Audit sheet names, dimensions, and merged cells before assuming header locations
from openpyxl import load_workbook
wb = load_workbook("file.xlsx", data_only=True)  # data_only evaluates formulas to stored numbers
for ws in wb.worksheets:
    print(ws.title, ws.dimensions, ws.merged_cells.ranges)
    for row in ws.iter_rows(values_only=True):
        print(row)
# DOCX: Extract paragraphs and tabular cell matrices separately
from docx import Document
doc = Document("file.docx")
for p in doc.paragraphs:
    print(p.style.name, "|", p.text)
for table in doc.tables:
    for row in table.rows:
        print([cell.text for cell in row.cells])
# PDF: Extract positioned text preserving layout order
pdftotext -layout file.pdf -

# DOCX to Markdown conversion using Pandoc
pandoc file.docx -t markdown

Workshop example · Clinical: clinic network

Start from: the data kit (how to restore)
Produces: a text/ folder with plain-text versions of the three files

Prompt

Read clinic_targets.xlsx, pilot_protocol.docx, and patient_newsletter.pdf. For each, tell me which tool you used and what was hard to extract. Then list each clinic's HbA1c target, the pilot's eligibility rules and outcome definition, and anything in the newsletter about Saturday hours. Finally, save plain-text versions of all three in a folder called text/ and tell me what changed in each conversion.

Files used: clinic_targets.xlsx, pilot_protocol.docx, patient_newsletter.pdf.

What you should see

File Expected
clinic_targets.xlsx Two sheets, Targets and Staffing (planned). The real header is on row 3, under a merged title row. Targets are fractions formatted as percent: Northside 0.60, Riverside 0.60, Hillcrest 0.55, for "most recent HbA1c < 8.0% (share of adult patients with diabetes)".
pilot_protocol.docx Eligibility: adults (18 or older) at Hillcrest; index visit is the first visit from 2025-04-01 to 2025-08-31 with HbA1c of 7.0% or higher. Procedure: a nurse call within 10 days; record the date and whether the patient was reached (Y/N). Outcome: any HbA1c from 14 to 120 days after the index visit. A one-row table (Hillcrest, 2025-04-01, R. Alvarez, RN).
patient_newsletter.pdf Three plain lines, including that Saturday hours are available at Northside and the Hillcrest follow-up call program. Extraction is clean because the PDF has a real text layer.

The text/ folder is what later work should read from. The protocol's definitions (index visit, outcome window) are exactly what the pilot analysis needs, so they are worth saving as text.

Verification criteria

  • Declared extraction tool: The agent explicitly identifies the parser or command line utility used to read the file.
  • Structural anomaly audit: The agent flags layout irregularities (such as descriptive banner rows above column headers or merged cell spans) rather than blindly assuming row 1 represents column headers.
  • Numerical consistency: Extracted values match the underlying cells when inspected in standard spreadsheet software.
  • Comprehensive sheet coverage: For multi-sheet workbooks, the agent verifies each relevant worksheet rather than inspecting only the default sheet.

Common pitfalls

Symptom Underlying cause Resolution
"ModuleNotFoundError" during script execution Parser library is not installed in the virtual environment Authorize the agent to install the missing package via uv
PDF extraction returns empty output Document is a scanned image lacking a digital text layer Instruct the agent to run an OCR pipeline or obtain the digital source file
Table columns merge into unaligned text Complex multi-column layout parsed linearly Re-run pdftotext with the -layout flag, or export the original table to CSV
Erroneous spreadsheet totals Formulas read as null, or header rows offset Instruct the agent to print the first 10 raw rows and explicitly verify the active header offset

Concepts behind this

  • Inputs. Language models process information as text. Selecting appropriate ingestion pathways and source formats preserves data structure and prevents extraction errors. Go deeper →

Builds on: Orient the agent in a folder, Prepare your environment