Day 31

Documents and spreadsheets: PDF, DOCX, XLSX and large tables

You ship
A document intake service for PDF, Word and Excel files, with accuracy and cost measured for each path to the model.

What you'll build today

After Day 30, WorkBot is connected to the customer's systems and secured. It already files documents into requests (Day 17), but customers send many kinds of files. Some PDFs carry a text layer, the text you can select and copy, and some are photographs of paper. Contracts arrive as Word files with tracked changes, and sales exports arrive as spreadsheets far too large for a prompt.

Today WorkBot reads each format by its own path to the model, chosen from the file's bytes. A path is how a file reaches the model, as its extracted text or as the file itself, and you learn what each one costs and what it loses. A spreadsheet never enters a prompt. It loads into a small database, and the model answers questions by writing SQL that it shows under each answer.

By the end of the day, WorkBot reads all four formats, and a measurement over invoices, contracts and an export tells you which path each format should take.

The paths
The bytes decide the format. A PDF goes as its text unless it is a scan, and an image goes as the file. A Word document goes as its text, with tracked changes marked. A workbook loads into DuckDB, and the model answers questions about it in SQL.

By the end of the day you can

  • Extract structured data from PDFs, Word documents (DOCX) and Excel spreadsheets (XLSX) at production quality
  • Choose between native document input, which sends the file itself, and pre-parsing, which sends its extracted text, per document type
  • Handle tables too large for a context window with code execution and SQL

Topics

2 Document Paths

  • Native: send the PDF to the model, and the model sees layout and images. Both major providers accept PDFs, with page limits and per-page token costs
  • Pre-parsed: extract the text first, then send text. It is cheaper and loses layout. Libraries include pdf-parse or pdf.js (through unpdf) for text PDFs, mammoth for DOCX, and SheetJS or ExcelJS for XLSX
  • Scanned documents: OCR (optical character recognition) or vision, per page. Detect whether a PDF is text or scanned before you choose a path
  • Mixed documents: text, tables and figures together. Split them by region and process each region with the right path

DOCX + Office Formats

  • DOCX structure: zipped XML with paragraphs, runs, tables and headers. mammoth converts it to HTML or text and keeps headings and lists
  • Tracked changes and comments: keep them for contracts, strip them for reports
  • Templates: fill a DOCX from structured data, for the output side

Spreadsheets + Large Structured Data

  • XLSX to rows with SheetJS or ExcelJS: sheets, merged cells, formulas versus values, and dates stored as serial numbers
  • The limit: a 50,000-row sheet does not fit a prompt. Load it into DuckDB or Postgres, and let the model write queries
  • Schema inference: the model reads the header row and a sample, then proposes column types and a cleaning plan. Code runs the plan
  • Code execution as a tool: the model writes SQL or TypeScript, you run it in a sandbox, and the model reads the result
  • Validation: totals that reconcile, row counts that match in and out, and types that parse

Production Patterns

  • Confidence per field and per document. Send low-confidence documents to review (Day 17)
  • Idempotent processing, where running it twice has the same effect as once. Key it by the document's hash, so you can reprocess after a prompt change
  • Cost: page counts, per-page pricing, and when pre-parsing pays for itself

Build

Features shipped today

  • WorkBot: Reads invoices, contracts and letters from PDF and DOCX, scanned PDFs included, and queries order exports from XLSX with SQL

Build WorkBot's document intake service:

  1. Accept PDF, DOCX and XLSX uploads. Detect the type, and for a PDF, whether it is text or scanned
  2. PDFs: send scanned and layout-heavy documents as native model input, and the rest as pre-parsed text. Extract each to a Day 17 schema (invoice or contract terms)
  3. DOCX: extract a contract's parties, dates, obligations and termination terms. Keep tracked changes visible in the output
  4. XLSX: load a 50,000-row sales export into DuckDB and expose a query tool. The model answers 5 analytical questions by writing SQL, and the page shows the SQL and the result under each answer
  5. Measure extraction accuracy on 10 documents per type, the cost per document per path, and where the native and pre-parsed paths disagree

Step by step

Detect a file by its bytes

Every path starts from knowing what a file is. A file's name and its declared type can both be wrong, so WorkBot reads the bytes instead.

Study first

  • File signatures are the fixed bytes a format opens with. A PDF starts with %PDF-, and PNG, JPEG and WebP each have a fixed opening of their own. The web's standard for sniffing a file's type lists the image signatures byte by byte, and the zip signature too. A declared type comes from the file name or from whatever the sender says, so it proves nothing.
  • Office Open XML is the format of Word and Excel files. A .docx and a .xlsx are zip archives of XML parts, and the part names tell them apart, word/document.xml for Word and xl/workbook.xml for Excel. Microsoft's guide to a spreadsheet's structure opens a workbook as a zip and shows the parts inside.

Build

  1. Write formatOf(bytes) in src/lib/files.ts. It reads the signature for PDFs and images, and for a zip it reads the names of the parts without unpacking the parts themselves.
  2. Make every upload path decide the type from the bytes. That covers the new-request page and POST /api/documents from Day 17, and the inbox from Day 18. The endpoint refuses any other format with a 400 that lists the formats it accepts.

Done when

  • A PDF renamed .png is read as a PDF, and a text file renamed .docx is refused before any model call.
Technical notes
  • A file can start with PK, the zip signature, and still not be a valid zip. Unzipping it then throws, and the API turns the error into a 500. Treat a broken archive as an unknown format, and add a test that posts one and expects a 400.

Two paths for a PDF

Once WorkBot knows a file is a PDF, it has to choose how to send it. The two paths differ in what they cost and in what the model gets to see.

Study first

  • Native input means sending the PDF file itself. Both major providers accept a PDF, and by their documentation each gives the model every page's text and an image of the page. So the model sees the layout, and each page costs image tokens. Read your provider's page limits and per-page cost, in Anthropic's guide to PDF support or OpenAI's guide to file inputs.
  • Pre-parsing means you extract the text layer yourself and send text. It is cheaper, but it loses layout, because columns come back as lines and a wrapped heading becomes two lines.
  • A scan has no text layer, or almost none, so count the letters on each page before you choose a path.

Build

  1. Write pdfPages(bytes), which returns the text of each page through pdf.js, using the unpdf package. Write scanned(pages), which is true when the pages average fewer than 50 letters and digits.
  2. Write route(source) in src/lib/extract.ts. A text PDF goes as its pages' text, labeled Page 1, Page 2, while a scan, an image, or a PDF whose text cannot be read goes as the file. An optional argument forces the file, so you can compare the two paths on one document.
  3. Make ingest, the Day 17 function that classifies and extracts a document, route the document first. Store the path on the document, and show it on the request page as "read from its text" or "read from the file itself".

Done when

  • A text PDF is filed from its text, and a scan of the same invoice is filed from the file, with the same fields.
A scan, read from the file
A scanned invoice, a grey and slightly crooked photograph printed to PDF, is filed with every field right. The header says which path it took.
Technical notes
  • A threshold of 100 letters a page is too high: in the reference run it sent a short test document down the scan path. 50 is a heuristic. A sparse typed page can fall below it and go as the file, which costs more and loses nothing.
  • A scan can carry a text layer from OCR (optical character recognition, software that turns an image of text into text). Such a scan passes the threshold and goes as that text, which is as good as the OCR was.
  • pdf.js takes over the buffer it is given, so pdfPages hands it a copy and the caller keeps the file.
  • The router averages a document's pages, so a typed cover on top of scanned pages can send the whole file as text and lose the scans. The fix is to split a document by page or region, which today's build leaves out.

Word contracts and their tracked changes

A contract needs more care than a PDF, because a reviewer has to see what changed as well as what it now says. A Word file holds both, if you read its XML directly.

Study first

  • word/document.xml holds the text, as Microsoft's guide to a Word document's structure shows. Paragraphs (w:p) hold runs (w:r), which are stretches of text with one format, and runs hold the text itself (w:t). A tracked insertion wraps its runs in w:ins, and a deletion wraps them in w:del, with its text in w:delText.
  • Keep tracked changes for a contract, and strip them for a report. A converter that renders the document as it would print either accepts or rejects the changes, but a reviewer needs to see both sides, so you read the XML yourself.
  • The output side works the same way in reverse, because writing that XML from data is how you generate a document. The production version fills a customer's own template, keeping its styles and replacing its placeholders.

Build

  1. Write docxText(bytes). In one pass over the XML, it writes paragraphs as lines and separates table cells with a bar. It writes an insertion as [+60+] and a deletion as [-30-], each in its place in the sentence.
  2. Write docxFrom(paragraphs, author), which builds a minimal Word document from runs, changes included. It builds the test fixtures, the sample files the tests read, and does not open or fill an existing template.
  3. Add a contract schema to src/lib/document-model.ts. It holds the title, the parties with their roles, the effective and end dates, renewal, each party's obligations, termination notice and causes, governing law, and changes. Every field holds the contract's value with the changes accepted, and changes lists every insertion and deletion.

Done when

  • A contract whose governing law was changed from Delaware to California is filed with California as its law, and the request page shows both the deletion and the insertion.
A contract
A subscription agreement from a Word file shows its parties, dates, renewal, obligations per party and termination. The tracked change shows Delaware struck through and California inserted.
Technical notes
  • Write the fixtures with their changes inline, as [-Delaware-][+California+]. That is the notation docxText produces, so a test can build a Word file from a paragraph and read the same paragraph back.
  • The request page shows changes the way the document does, with inserted text underlined and deleted text struck through. Every other field shows as plain text.
  • docxText reads word/document.xml only, so headers, footers, footnotes and comments are not in the text. A contract that keeps terms in a footnote needs those parts read too.

Load a spreadsheet into a database

A spreadsheet export is the opposite problem from a contract. It is far too large to read in a prompt, so WorkBot loads it into a database where the model can query it.

Study first

  • An .xlsx holds sheets, shared strings (text stored once and referenced by its cells), styles, merged ranges and formulas. A date is a number with a date format. A formula carries the result calculated by the program that saved the file. A reader does not recalculate, so a file written by a script may carry no result at all.
  • A prompt is the wrong place for the rows, because a 50,000-row export is about 3 MB of text. Load it into DuckDB or Postgres instead, and let the model write queries. DuckDB is a SQL database that runs inside your process.
  • Exports have habits, such as a title merged across the top, a blank row, and a totals row at the bottom. A SUM over a column that still contains its totals row counts everything twice.

Build

  1. Write readSheet(bytes) in src/lib/sheets.ts, on ExcelJS. It reads the first worksheet, and takes as the header the first of the top rows whose cells are all distinct text, which passes over a merged title. A merged cell reads as the cell it was merged from, and a formula reads as its result.
  2. Give each column one type when every value agrees (DOUBLE, DATE, TIMESTAMP, BOOLEAN), and VARCHAR otherwise. Normalize the column names for SQL, so Unit price becomes unit_price.
  3. Take a last row labeled Total out of the data, and compare each of its numeric figures with the sum of its column. Either the totals reconcile, or the sheet says by how much they do not.
  4. Add a sheets table that keeps the file, the columns and the totals for 30 days. Add /admin/sheets, which lists the sheets and takes an upload from a user with the agent role (Day 30).

Done when

  • WorkBot reads the 50,000-row export in under a second, with its columns typed and its totals reconciled.
A sheet
After upload, the export shows 50,000 rows, with both totals reconciled against the rows. Each column shows the type that every value in it parsed as.
Technical notes
  • readSheet infers the types in code from every value, which is exact for an export like this one. A sheet typed by hand may store dates as serial numbers or put unit labels in the header. For such a sheet, the model can propose a cleaning plan from the header and a sample, and code runs the plan, but today's build leaves that out.
  • Bind JSON as text with a cast, $n::text::jsonb. The postgres driver encodes a JSON string a second time, so on Postgres the columns come back as a string. PGlite, the embedded Postgres the tests run on, does not, so every test passes anyway. With the cast, both read the value once.
  • In the reference run, the page cache from Day 28 had the same bug, so a cached page came back in quotes with its line breaks escaped.

Answer questions with SQL

With the sheet in a database, the model can answer questions about it by writing queries. Running code that a model wrote needs a sandbox, an isolated place where a bad query can do no harm.

Study first

  • Code execution as a tool works in a loop. The model writes a query, you run it, and the model reads the result. The prompt is not a boundary, so the sandbox decides what a bad query can do.
  • DuckDB's settings for a sandbox turn off external access (no files, no network) and extension loading, lock the configuration, and set a memory limit. Also check each statement before it runs, cap the rows read back, and interrupt a query that runs too long.
  • Show the work, because an answer about money that the reader cannot check is a liability.

Build

  1. Write withSandbox(table, fn). It creates an in-memory DuckDB for each question, loads it through DuckDB's appender (its bulk-insert interface), and closes it afterward. A query must be exactly one statement, of type SELECT. It reads at most 50 rows, says when there were more, and is interrupted after 5 seconds.
  2. Write ask(table, question, model), an agent with one tool, query, that runs under the step, cost and time limits of the Day 11 agent loop. Tell the model the column names, types and headers and three sample rows, never the table. Show a reserved word such as order quoted, as SQL needs it. Return the answer with every query the agent ran and the rows each returned, refused queries included.
  3. On the sheet's page, let a user ask a question, then show the answer and each query's SQL with its rows. Route the task as analyze in the Day 24 routing table, with the small model first and the large one behind it if a call fails.
  4. Write tests. A SELECT runs and stops at its limit, while a write, two statements, a file read and a runaway query are each refused or stopped. The answer lists a refused query beside the one that answered.

Done when

  • A question about the export comes back with the right figure, the SQL that produced it, and the rows.
An answer
Asked which sales rep brought in the most revenue in December, the model answers. Below the answer come the query that produced it and its rows, then the model's check of the date range.
Technical notes
  • withSandbox rebuilds the database from the stored file for each question. That takes about a second for 50,000 rows, against several seconds for the model's turns. A cache would save that second, but it would add eviction, and the problem of closing a database while a query runs.
  • Each query gets its own connection, so the time limit interrupts that query alone.
  • The query tool requires the viewer role and checks it on every run, like every tool since Day 30. The sandbox holds only the sheet being asked about.

Measure and choose each path

Each path now works, so the last part measures them side by side and turns the numbers into a decision. That decision is what someone choosing a path for a new kind of document will read.

Study first

  • Know what 10 documents a type can show. They can show that a path is broken, but they cannot show a two-point difference, because a single wrong field out of 80 is more than one percentage point. Anthropic's statistical approach to model evals shows how to put an error bar on a score, and how many cases a difference of a given size needs. Read the result for what failed and why, not only for the mean.
  • Compute the ground truth apart from the system under test. For the spreadsheet, plain code computes each answer over the same generated orders.
  • Read the cost per document per path from the traces (Day 19).
  • Choose a path per format, with the numbers. Where the cheaper path loses nothing, take it, and where it loses something, name the documents it loses on.

Build

  1. Write pnpm formats, which runs 3 sets. The first is the 10 invoices of the Day 17 extraction set, printed by Chrome once with a text layer and once as a scan. WorkBot reads the text PDF as its text and again as the file, and reads the scan as the file.
  2. Make the second set the 10 contracts of content/eval/contracts.json, built as Word files. Make the third 5 questions about a seeded 50,000-row export, generated the same way on every run.
  3. Score a field as the Day 17 extraction eval does, where an eval is a scored test of WorkBot's output. Score an answer right when it names what the code found and states its figure to the precision it is written in. "$2.2 million" states 2,199,846, and "2,785 orders" does not state 2,784.
  4. List the fields where the text and the file disagree, with what each gave.
  5. Record the decision, which path each format takes, in brain/architecture/decisions.md, the brain's numbered decisions from Day 21. In the reference run it is D20. Write the paths and the table in docs/documents.md. A PDF with a text layer goes as text, and a scan or an image goes as the file. Word goes as text with the changes marked, and a spreadsheet goes to SQL on the small model.
  6. List what is not built. That covers splitting a document by page or region, OCR as its own step, older formats (.doc, .xls) and CSV, and more than one worksheet.
  7. Add idempotency by content hash to that list. Idempotent means a second run of the same file changes nothing, but today a document uploaded twice is read twice. Keying the record by the file's hash and the prompt's version would let a reprocess after a prompt change touch only what changed.

Done when

  • The table is in docs/documents.md with the decision, and pnpm formats --save <dir> writes the files the measurement built, so you can upload them by hand.
  • Someone choosing a path for a new kind of document can read why each existing format goes the way it does.
The measurement
In the reference run, the router chose correctly for all 20 invoice files. The paths agree on everything but one vendor name, which a narrow column split in the text layer. Every graded field and every tracked change of the contracts came out right.
Technical notes
  • In the reference run, the file path sent twice the input tokens of the text path, about 1,600 more per page, yet cost only a third more. The record the model writes back, about 1,000 tokens, is most of the bill at the small model's prices. The gap grows with pages and with the model's price.
  • On the spreadsheet in the reference run, both models answered 5 of 5, the small one at $0.0004 a question and the large one at $0.0088. The large model spent its extra queries checking the data.
  • One of those checks found a flaw in the test itself, because the generator had put every order between the 1st and the 28th of its month. Spread the generated orders over every day of each month.
  • The contracts were short and written for the test, and the export is synthetic, so they prove only that the paths work. Put real documents from the customer in the set before anyone calls the extraction production quality.

Resources