Extract Billing Records to Excel From Scanned Invoices

To extract billing records from scanned invoices into Excel, run the invoices through OCR that detects tables, export a workbook, and stack the line items in one worksheet with one row per charge or time entry. Put the invoice number and source page on every row, then check each invoice's rows against its printed total.

The work is less about recognition than about shape. An invoice is a header, a table and a footer, and a stack of forty invoices is forty of each. What you want is one long table of entries, each of which still knows which invoice and which page it came from.

Which billing records does a legal matter put in a spreadsheet?

Three kinds of billing record reach a spreadsheet in legal work: fee invoices listing time entries, bills from vendors and contractors listing charges, and account statements listing invoices and payments. Each has a different row. Decide which kind you hold before you pick columns, because a statement row and a time entry do not belong in one table.

A fictional example runs through this guide. Pinecrest Builders has sued a subcontractor, Calloway Drywall, and has produced 38 scanned invoices from its replacement contractor as proof of the cost of finishing the job. The other side has produced a one-page statement of account. The invoices become a line-item sheet. The statement becomes a short second sheet used as a control.

Check the fee agreement before you lock the columns. A billable hour invoice puts a rate and hours on each line. A contingency fee may show one amount and no hourly rate. Record filing fees and courier charges as costs, in an expense row, so a total of professional fees leaves them out. Where the invoice carries e-billing codes, copy those codes into a column of their own.

Some records look similar and are handled elsewhere. Medical bills carry procedure codes, insurer adjustments and write-offs that need their own columns; see the guide to converting medical records to a spreadsheet. Records of hours an employee worked are pay records, not invoices, and the post on timesheets to Excel deals with those.

Whether a court or tribunal wants billing records in a set layout is a matter for its own rules and orders. Check them before you settle your columns, since it is easier to build to a required layout than to convert to one.

What columns does a scanned invoice or time entry need in Excel?

A scanned invoice or time entry needs eight columns in Excel: date, biller, description, hours or quantity, rate or unit price, amount, invoice number and source page. The first six come off the line. The last two you add, and they are what let a reader trace any row to the paper.

Columns worth adding when the documents call for them:

• Entry type. fee, expense, tax, credit. Lets you total fees apart from costs.
• Invoice date. Differs from the entry date and matters for interest and payment terms.
• Unit. Hours, days, sheets, linear feet. Without it a quantity of 40 means nothing.
• Amount computed. Hours times rate, by formula, for the reconciliation.
• Row ID. A number in stacking order, so any sort can be undone.

Four rules for the values:

1. One entry per row. Never enter an invoice total as a row among the lines.
2. Invoice numbers are text. A number such as 004417 loses its zeros as a numeric cell.
3. One unit of time. If one biller prints 1.5 and another prints 1:30, convert both to decimal hours and keep the printed form in a spare column.
4. Names as a list. Decide the spelling of each biller once and use it on every row, or the totals by person will split "J. Okafor" from "Okafor, J."

If several people will fill the sheet, write these choices down as a field list first. The post on structured fields explains writing the field list before any document is converted.

Extracting line items from a stack of scanned invoices into one worksheet

Work in three passes: list the invoices, convert them, then stack the line items into one sheet with the invoice number and page added to each block. The list comes first because it gives you the control totals that every later check depends on.

Pass one: the invoice register

Before any OCR, make a small sheet by hand with one row per invoice: invoice number, invoice date, biller, pages in the scan, and the total printed on the invoice. For the Pinecrest set that is 38 rows. Because a person typed those 38 totals from the page, they are independent of whatever the software reads.

While you are there, note any invoice that is missing from a numbered run, any that appears twice, and any credit note.

Pass two: conversion

1. Put the invoices in register order. If they arrived as one PDF, leave them as one; if as many files, name each by invoice number.
2. Run them through OCR that detects tables. With Schema Law, you upload a PDF, PNG or JPEG of up to 20 MB in the browser, check the parsed pages against the original on screen, and export Excel. Each table gets its own sheet and the rest of the text goes on another. Every plan includes the Excel export; the pricing page shows how many pages one document may have on each plan.
3. Save the export untouched and work in a copy.

Pass three: stacking

1. Create a sheet named Lines with your column headings.
2. Take the first table sheet. Identify which invoice and page it came from.
3. Paste its line rows under your headings, lining up each exported column with yours. Invoices from different billers put columns in different orders, so look every time.
4. Fill Invoice number and Source page down the block you just pasted.
5. Repeat for every table sheet, in register order.
6. Number the rows in the Row ID column.
7. Convert hours, rates and amounts to numbers in new columns, keeping the columns as read. The Excel guide covers numbers, dates and currency in exported cells.

Count as you go. After each invoice, compare the number of rows you pasted with the number of lines on the page.

Why do long billing narratives split one entry across several rows?

A long billing narrative splits because it wraps onto a second or third printed line, and the software reads each printed line as a table row. The first row carries the date, the hours and the amount. The rows after it carry only the rest of the description, with every other cell empty.

Time entries written as full sentences are the ones at risk. An entry on the page:

The same entry after conversion can look like this:

Finding the fragments

Fragments have a blank date and a blank amount. Filter the Date column for blanks and you have the candidates. Do not delete them: each one is part of somebody's description.

Not every blank-date row is a fragment. Some invoices print the date once and list several entries under it. Those rows have hours and an amount of their own. The rule is: blank date and blank amount means a continuation; blank date with an amount means a new entry that needs its date filled down.

Rejoining

For a few dozen fragments, do it by hand: copy the fragment's text onto the end of the description in the row it continues, with a space between, then delete the fragment row.

For hundreds, use a helper column. Give every row a group number that goes up by one whenever the Date cell is filled and stays the same when it is blank. Then join the descriptions of each group with TEXTJOIN, using a space as the separator, and keep only the first row of each group. Check a sample against the page before you delete anything.

Two related faults:

• A narrative split at a page break. The entry starts at the foot of one page and ends at the head of the next, with a "continued" line and a repeated header between them. Remove the furniture, join the halves, and keep the first page as the source page.
• Two entries merged into one. Where printed lines sit close together, two short entries can land in one row, with two amounts in one cell. A cell in the Amount column that will not convert to a number is the sign.

After rejoining, the row count for each invoice should equal the number of entries on the paper.

Invoice headers, remittance stubs and summary pages that are not line items

Leave out of the Lines sheet everything that is not a single charge or time entry: the header block, the remittance stub, subtotals and totals, balance-forward lines, payment acknowledgments, and summary pages that restate the entries. Keep those figures on a separate sheet, because several of them are your control totals.

Two of these deserve care.

The timekeeper summary. Some fee invoices close with a recap: each person, their rate, their hours, their fees. It is tempting to treat it as more data. It is the same hours a second time, and adding it to the Lines sheet doubles the invoice. Kept apart, it is a gift: your entries, totalled by person, should reproduce it.

Previous balance and payments. A line reading "Balance forward 12,640.00" is last month's invoice, already in your stack. Adding it counts those charges again.

When a table sheet from the export turns out to hold a remittance stub or an address block that was read as a table, set it aside whole. Do not copy from it.

Checking hours times rate against each scanned invoice total

Reconcile at three levels: each row's hours times rate against its printed amount, each invoice's rows against the total in your register, and the whole sheet against the sum of the register. A sheet that passes all three matches the invoices line for line.

Level one: the row

The formulas in this section assume the Lines sheet runs Row ID in column A, then Date, Biller, Description, Entry type, Hours (F), Rate (G), Amount (H), Invoice number (I) and Source page (J). Change the letters to match your own sheet.

In the Amount computed column, multiply hours by rate and round to cents: =ROUND(F2*G2, 2). In the next column subtract the printed amount. Filter for anything that is not zero.

A non-zero row has one of three causes. A digit was misread in hours, rate or amount; the post on why OCR gets numbers wrong covers how to tell which. The rate on that line differs from the one you filled in, for instance after a rate change part-way through the matter. Or the invoice itself is wrong, which you can only say after reading the three figures on the page.

Level two: the invoice

On the register sheet, add a column that totals the Lines rows for each invoice:

=SUMIFS(Lines!H:H, Lines!I:I, A2)

Here H is Amount and I is Invoice number. Beside it, subtract the printed total you typed in pass one.

A worked example from the Pinecrest set:

Read the difference before you read the page. A difference equal to hours times rate for one line is a missing or doubled row. A difference equal to a tax, discount or retainage figure on the invoice is a row that should not be in the sum, or should. Anything else is a digit.

Level three: the set

Sum the register's printed totals and sum the Amount column of Lines. They should agree. Then compare the register with the statement of account, if you have one: every invoice on the statement should be in the register at the same total.

What reconciliation does not prove

A row can pass every check and still have the wrong date, the wrong biller or a garbled description, because none of those are summed. Read the rows you intend to rely on against the page. And tying to the invoice total shows only that your sheet matches the invoice. Whether the invoice is right is the next question, and it is one the sheet now lets you ask.

Where the invoices support a claim, the totals by category feed the schedule that claims them. The guide to checking a damages schedule converted from PDF to Excel picks up from there. For the other file types one upload can be exported as, see the formats list on the homepage.