Preporato
Messy Documents to a Clean Table: Invoices into a Spreadsheet with AI
Hands-on lab · IDE in your browser

Messy Documents to a Clean Table: Invoices into a Spreadsheet with AI

Get a language model to fill an accounts spreadsheet from invoices in every layout, without code: name the columns, force one format per column including UK dates and European decimals, make it leave cells blank rather than invent them, write rules for the traps real invoices contain (balances brought forward, trade discounts, dotted dates), and confirm the result cell by cell on invoices it has never seen.

Time
45 min
Checked steps
5
Level
Beginner
Setup
None
Read step 1

Hands-on labs require Pro · $29.99/mo · cancel anytime

Lab cockpit45 min · 5 stepsSession running
3 / 5 steps passingFix the costly cells · step 4 of 5
prompt.md▶ Run✓ Check
- dates as YYYY-MM-DD, for example 2026-09-03. Dates written like 03/09/2026 are day/month/year (UK).- money as a plain number with a dot for decimals and no symbol or thousands separator: 1234.5, not £1,234.50.  In European amounts like 1.234,50 EUR the dot separates thousands and the comma separates decimals, so it is 1234.5.- currency as a three-letter code: GBP (£), EUR (€) or USD ($).- vat is 0 when the invoice shows no VAT or says zero-rated. Never guess. If a field is not written on the invoice, use null. In particular:- due_date is null when the invoice gives no payment date (for example "payment on receipt"). A delivery date is not a due date.- po_number is null unless the invoice quotes a purchase order or order reference starting with PO-.
TerminalOutput

The job

Every month Brightline's bookkeeper types about eighty supplier invoices into a spreadsheet, and they arrive in every layout: neat blocks, chatty letters, tables, scans with broken lines, amounts in pounds, euros with comma decimals and dollars. You write the prompt that fills the spreadsheet. A harness runs it on 23 invoices, writes invoices.csv, and checks all 207 cells, charging three times as much for a value the model invented as for a blank.

5 steps, each checked when you finish it

A check runs your work at the end of every step. Hints and the full solution are there if you get stuck.

  1. 1

    Name the columns

    Every month Brightline's bookkeeper types about eighty supplier invoices into a spreadsheet: supplier, invoice number, dates, the purchase order, the amounts.

  2. 2

    One format per column

    Look at invoices.csv (open it from the file tree).

  3. 3

    A blank beats a guess

    Now the most expensive cells are the invented ones.

  4. 4

    Fix the costly cells

    Three invoices in the pile are harder than the rest (inv-021 to inv-023), and they are the kind that turn up every month:

  5. 5

    Held-out invoices

    holdout/ holds nine invoices your prompt has never been tuned on, including one that combines a discount, dotted dates and a previous balance.

Step 1 as it appears in the lab

The lab’s own text. The hint and the solution stay inside the lab.

Step 1: Name the columns

Every month Brightline's bookkeeper types about eighty supplier invoices into a spreadsheet: supplier, invoice number, dates, the purchase order, the amounts. The invoices arrive as text in every layout imaginable (open a few in invoices/): neat blocks, chatty letters, tables, and scans with broken lines.

You will get a language model to fill that spreadsheet, with no code: you write the instructions in prompt.md, and a harness runs them on 23 invoices whose right values are known, writes the result to invoices.csv, and checks every cell. prompt.md has your instructions, the line ---DOCUMENT---, and below it the template each invoice goes into.

The harness scores cells, and not all mistakes are equal:

CellCost
wrong value1
left blank when the invoice has it1
filled in when the invoice does not have it (invented)3

An invented purchase order number is worse than a blank: a blank gets noticed and looked up, an invented number gets paid.

Do this

1. Click Run with the starting prompt. The replies are prose, so no cell can be filled.

2. Replace the TODO: ask for one JSON object and nothing else, with exactly these nine keys, each with a short description of what it holds: supplier, invoice_number, invoice_date, due_date, po_number, currency, subtotal, vat, total. JSON is the format the harness reads; it looks like {"supplier": "...", "total": 12.5}.

3. Run again. Most cells now fill. Look at which columns are still wrong.

prompt.md, the file you edit10 lines
<!-- Notes between these arrows are for you; the harness removes them before sending. -->
You copy fields from supplier invoices into Brightline Books' accounts spreadsheet.
<!-- TODO (Step 1): ask for one JSON object and nothing else, and list the nine keys with a short
     description of each: supplier, invoice_number, invoice_date, due_date, po_number, currency,
     subtotal, vat, total. -->
Read this invoice.
---DOCUMENT---
<invoice>
{document}
</invoice>
Provided for you:docs2table.pyholdout/inv-101.txtholdout/inv-102.txtholdout/inv-103.txtholdout/inv-104.txtholdout/inv-105.txtholdout/inv-106.txtholdout/inv-107.txtholdout/inv-108.txtholdout/inv-109.txtinvoices/inv-001.txtinvoices/inv-002.txtinvoices/inv-003.txtinvoices/inv-004.txtinvoices/inv-005.txtinvoices/inv-006.txtinvoices/inv-007.txtinvoices/inv-008.txtinvoices/inv-009.txtinvoices/inv-010.txtinvoices/inv-011.txtinvoices/inv-012.txtinvoices/inv-013.txtinvoices/inv-014.txtinvoices/inv-015.txtinvoices/inv-016.txtinvoices/inv-017.txtinvoices/inv-018.txtinvoices/inv-019.txtinvoices/inv-020.txtinvoices/inv-021.txtinvoices/inv-022.txtinvoices/inv-023.txtrun_tests.pytruth.json

Frequently asked questions

Do I need to code for this lab?

No. You edit prompt.md and click Run. A harness sends each invoice to a hosted model with your prompt, writes the answers to a CSV you can open as a spreadsheet, and scores every cell against the right value.

Why does an invented value cost more than a blank?

A blank is noticed and looked up. An invented due date or purchase order number looks like data, so nobody checks it. The harness weights invented cells three times a wrong or missing one to push the prompt toward saying 'not on the invoice'.

How are UK dates and European numbers handled?

By stating the rule in the prompt: dates such as 03/09/2026 are day/month/year, and in amounts such as 1.234,50 EUR the dot separates thousands and the comma separates decimals. Without those rules, models guess the US reading.

Can this handle scanned invoices?

The lab includes text from scans with broken lines and noise. Scans need an OCR step first to turn images into text; the extraction prompt works on the text that OCR produces.

Extracting data from documents with AI, cell by cell

Turning documents into rows is one of the most useful everyday automations: invoices, receipts, forms, order confirmations. A language model reads messy layouts well, but a spreadsheet needs every value in the same format, and it needs blanks where a document has nothing, because an invented value is worse than a missing one. In this lab you build an invoice extraction prompt without code and measure it per cell. You define the columns as JSON keys, specify one format per column (ISO dates, day-first UK dates, European decimal commas, currency codes), tell the model to return null instead of guessing and name the traps that cause guesses, write rules for statement invoices, trade discounts and dotted dates, and check the finished prompt on held-out invoices.