How to Extract Invoice Line Items

Most invoice extraction stops at the header: supplier, invoice number, date, total. Four values, one row, done. Line items are the same job with one property changed, and that one property changes everything — a single invoice produces an unknown number of rows instead of exactly one.

This article is about that difference. Three other guides on this site call line items "a different shape of problem" and move on; this is the one that says what the shape is and how to hold it.

Header fields and line items are not the same job

A header field has a property that makes it easy: there is exactly one of it per document. One invoice number. One invoice date. One grand total. You can write the column heading before you have seen the document, and you know in advance that the answer is a single cell.

A line item has none of that. One invoice has three lines; the next has forty-one; the one after that has two lines and a hand-written note. You cannot know the row count before reading the document, which means:

• the output shape depends on the input, so no fixed template describes it;
• every header value has to be repeated or joined onto each line, or the lines lose track of which invoice they belong to;
• the row count becomes something to check, because a dropped line is invisible in a way a dropped invoice is not.

That last one is the real cost. If a batch of fifty invoices comes back as forty-nine rows, you notice. If an invoice with twelve lines comes back with eleven, the sheet looks entirely normal and the total is quietly wrong.

Two layouts, and you have to pick one first

There are only two sensible ways to lay line items out in a spreadsheet, and the choice is worth making deliberately because it is expensive to change once rows exist.

Flat. One sheet. Every row is a line item, and every header field is repeated on every row belonging to that invoice.

The repetition looks wasteful and is not. A flat sheet is the only shape a pivot table, a filter or an accounting import can read without being taught anything, because every row carries its own context. Notice the last row's line total: that invoice prints a quantity and a unit price for the keyboard but no extended amount, so the cell is blank rather than multiplied out. A computed value in a column of printed ones cannot be told apart from the printed ones later.

Relational. Two sheets. One row per invoice on the first, one row per line on the second, joined by the invoice number or — better — by the source filename.

Use relational when the header fields are numerous or when you are going to reconcile invoice-level figures against something else, because it gives you one authoritative row per document to reconcile with. Use flat for everything else, and particularly for anything that ends up in a pivot table.

If you pick relational, make the join key the source filename, not the invoice number. Invoice numbers collide across suppliers far more often than anyone expects, and a filename is unique by construction.

The columns a line-item sheet needs

Seven, usually, and two of them are the ones people leave out:

Source file        which document this line came from
Invoice number     the header field, repeated on every line
Line number        the line's position on the invoice, 1, 2, 3…
Description        as printed
Quantity
Unit price
Line total

Line number is the column that gets forgotten, and it is the one that makes the sheet checkable. Without it you cannot tell whether line 7 of a twelve-line invoice is missing, and you cannot restore the printed order after any sort.

Source file is the column that makes the sheet auditable, exactly as it is in a one-row-per-document sheet. Every other guide here says the same thing about it for the same reason.

Resist adding a tax column per line unless the invoices actually state tax per line. Many state it once, at the bottom, and apportioning it across lines is a calculation you have invented — it belongs in the spreadsheet, as a formula anyone can see, not in the extraction.

Check the rows against the printed total

This is the whole reason line items are worth treating carefully, and it is a check nothing else in document extraction gives you for free.

The lines of an invoice sum to its net total. So for every document: sum the line totals, compare with the header total you extracted, and look at anything that does not reconcile. One formula over a flat sheet tells you which invoices were read completely and which were not.

Expect legitimate differences, and know which are legitimate:

• tax — the lines sum to net, the header total is usually gross;
• delivery, surcharges and discounts, which are often printed outside the line table;
• rounding, where the invoice rounds each line and you are summing unrounded figures.

A difference you can explain is fine. A difference of exactly one line's value is a missing line, and that is what you are looking for.

Why line tables are hard to read out of a PDF

Worth knowing, because it explains why results vary so much between documents.

A PDF does not store a table. It stores text with positions, and a table is something a human infers from those positions. Work it out for yourself with Extract Text from PDF on one of your own invoices: the values come back, and the row structure does not come with them. Extracting tables from PDF goes into why at length.

Line tables add four problems on top of the general table problem:

Wrapped descriptions. A long product description occupies two or three printed lines. It is one item. Treating each printed line as a row is the single most common line-item error, and it produces rows with a description and no figures.

Page breaks. A long invoice continues its table onto page two, usually with the header row repeated and sometimes with a subtotal in between. The repeated header must not become a row, and the subtotal must not become an item.

Sub-lines. Serial numbers, batch references and per-line delivery dates are often printed underneath their item. They belong to the row above, not to a row of their own.

Mid-table summaries. "Carried forward", section subtotals and "continued" markers sit in the middle of the rows and are not items.

All four have the same fix: decide what a row is before you start — normally "a line with a quantity or an amount on it" — and treat everything else as belonging to the nearest such line.

Doing it by hand without making the usual mistake

For a handful of invoices, hand transcription is still right. Two rules keep it honest:

1. Finish one invoice completely before starting the next. Filling a column downwards across documents is the mistake that shifts rows, and a shifted row in a line-item sheet attaches one invoice's quantities to another invoice's prices.
2. Type the line number as you go, from the invoice, not afterwards from the sheet. It is the only record of the printed order once anything is sorted.

Then run the reconciliation above before the sheet is used for anything.

What this site's workflow does and does not do

Plainly, so there is no confusion: the extraction workflow described across this blog produces one row per document — the header fields, under column headings you chose, with a Source file column. That is what extracting invoice data to Excel covers field by field, and what invoice to Excel walks through.

Line items are the shape described above: many rows per document, a row count that depends on the input, and a reconciliation step. If what you need is the per-line detail rather than the invoice totals, the layouts and checks in this article are how to hold it — and if you want to tell us that is the job you have, get in touch, because which of the two layouts people actually need is exactly the sort of thing worth hearing before building.

Common questions

Should header fields be repeated on every line? In a flat sheet, yes. The repetition is what lets any row be read on its own, and it costs nothing a spreadsheet notices.

Flat or relational? Flat unless you have a specific reason. Pivot tables, filters and most accounting imports want flat.

What if a line has no amount printed? Leave it empty. A blank cell is a fact about the document; a figure you multiplied out is a figure you invented, and nobody reading the sheet in six months can tell which columns were printed.

How do I handle credit notes among the invoices? Negative line totals in the same sheet, with a column marking the document type. Separating them means every total has to be computed twice, as consolidating invoices into Excel explains.

Is CSV good enough for a line-item sheet? It is risky. Descriptions contain commas and quotation marks, which is exactly where CSV's escaping gets mangled in transit. PDF to CSV vs PDF to Excel covers the trade-off.

If you only take one thing

Add a Line number column and reconcile the lines against the printed total before the sheet is used.

Everything else about line items is layout, and layout can be changed. A sheet with no way to detect a missing row is a sheet whose totals you cannot defend, and that is not a layout problem.