Scanned PDF to Excel: Getting Tables Out Intact

To get a scanned PDF into Excel, run the scan through OCR software that detects tables, then export an XLSX workbook. Each table on the page becomes rows and columns of cells. Before using any figure, convert the cells from text to numbers and check the totals against the scanned page.

Tables are the hardest thing OCR is asked to do. Reading a word correctly is one problem. Deciding which column that word belongs to is a second one, and a single wrong decision moves every figure after it. So this guide spends as much time on proving the result as on producing it.

How does OCR find a table on a scanned page?

OCR finds a table by looking for a grid. It uses ruled lines where there are any, and where there are none it uses the alignment of the text itself: words that start at the same horizontal position down the page suggest a column, and words that share a baseline suggest a row.

The software works in three stages, whatever product you use.

1. Find the region. Decide which part of the page is a table and which is ordinary text. A block of short entries lined up in columns, with white space between them, is the signal.
2. Work out the grid. Place the column boundaries and the row boundaries. Ruled lines make this easy. Without them, the software infers boundaries from gaps.
3. Fill the cells. Recognize the characters and assign each run of text to a cell.

Errors at each stage look different, which helps when you are diagnosing a bad export. A failure at stage one gives you a table that arrived as a paragraph, or a paragraph that arrived as a one-column table. A failure at stage two gives you merged or shifted columns. A failure at stage three gives you the right grid with a wrong digit in it.

Some layouts are reliably easier than others:

Exhibits add complications of their own, such as stickers, Bates numbers and nested sub-tables. Those are covered in the post on OCR for exhibits with tables.

Exporting a scanned table to an XLSX workbook

Upload the scan, wait for the pages to be read, review the parsed tables on screen, and export Excel. In Schema Law the workbook contains one worksheet for each table found, plus a final worksheet holding the rest of the document's text.

The Excel export is available on every plan. The free plan takes one document a day of up to three pages, so you can test a spreadsheet export on a short statement, or on the hardest page of a ledger, before paying. The pricing page has the plan details.

Here is what to expect when you open the file.

• A worksheet per table. Sheets are named in the order the tables appear, with the source page in the name: Table 1 (p2), Table 2 (p2), Table 3 (p5). The page number is the page of the uploaded file, which is what you need to trace a cell back to the scan.
• A bold first row. The top row of each table is formatted as its header. If the table on the page had no header row, the first data row will be bold. That is cosmetic, but remember it when you sort.
• Squared-off rows. Where a row was read with fewer cells than the widest row, blank cells are added at the right-hand end so every row has the same width. A row padded this way is a row to check, because the blank may belong in the middle.
• A sheet named Text. It has two columns, Page and Text, and lists every heading, paragraph and list that was not part of a table, much as a plain text export would. Captions, footnotes to the table and "continued on next page" notices end up here.
• Cells stored as text. Every cell holds the characters as they were read. A cell showing 18,450.00 is the text "18,450.00" until you convert it.

A sensible first five minutes with the workbook:

1. Save it under a new name and keep the download untouched.
2. Count the table sheets and compare with the number of tables you can see in the scan.
3. On each sheet, count rows and columns against the page.
4. Read the Text sheet for anything that should have been in a table.
5. Only then start converting and adding up.

If the tables are going into another system and not into a person's hands, a CSV may suit better. The post on converting a scanned PDF to CSV explains the differences.

Why do columns shift or merge in the spreadsheet?

Columns shift when the software places a value one cell to the left or right of where it belongs, and they merge when two columns are read as one. Both come from the same cause: the grid was inferred from spacing, and the spacing on that row was ambiguous.

The situations that produce it:

• Empty cells. A ledger row with nothing in the Debit column gives the software no anchor there. The Credit figure may slide left into the Debit column. This is the most dangerous table error in legal work, because the number is right and its meaning is reversed.
• Narrow gutters. When a long description nearly touches the next column, the two can be joined into one cell, and every later cell on that row moves left.
• Wrapped text. A description that runs to two lines can be read as two rows. The second row holds the tail of the description and blank amounts.
• Subtotal and heading rows. A row such as "Subtotal, March" spans several columns on the page. In the grid it lands in one cell, and its amount may land in an unexpected column.
• Skew and curvature. On a crooked or curved page, a row drifts up or down as it crosses the sheet, and the right-hand end can be matched with the neighboring row.
• Stamps, punch holes and handwriting. Marks in the table area are read as content or split a cell.

How to spot it quickly:

• Sort nothing yet. Scroll down each numeric column and look for text where numbers should be, or numbers in a description column.
• Look at the rows whose last cell is blank. A shifted row often ends one cell early.
• Add a helper column that counts the non-empty cells in each row. Rows with an unusual count are your suspects.
• Check that each date is in the date column. Dates are easy to recognize by eye and make a good plumb line.

Repair by hand, with the scan beside you. Insert a cell and shift right for a slid value, and join split rows by moving the fragment up. Do not write a formula that "fixes" the pattern, because shifted rows are the exceptions, and a formula treats them as the rule.

Checking totals after extracting a table

A table is verified when its totals reproduce. Add up each numeric column in Excel and compare the result with the total printed on the scanned page. If they agree to the cent, the column is very likely right. If they differ, the difference tells you where to look.

This is the strongest check available for OCR output, and it is specific to tables. Prose has no checksum. A column of figures with a printed total does.

A worked example. A scanned account statement shows twelve charges and a printed total of $4,318.75. After conversion and cleaning, your SUM gives $4,298.75.

• The difference is exactly 20.00. A round difference suggests one digit misread in one cell. Look for a figure where an 8 could have been read as a 6, or a 3 as a 1, in the tens place.
• Had the difference equalled one of the charges, a row would be missing or duplicated.
• Had the difference been divisible by 9, two adjacent digits might be transposed, an old bookkeeping test that works just as well on OCR output.
• Had your sum been far too small, some cells are probably still text and were skipped by SUM.

Run these checks in addition to the total:

When the page has no printed total, make your own control. Add up the column on the scan with a calculator, twice, and compare. For a long schedule, total it a page at a time so a discrepancy is confined to one page.

When the table is a creditor account statement, keep the debtor name, each invoice number and the running balance in separate columns. A proof of claim can be built from those rows later. If the statement mentions a security interest or collateral, put that description in a notes column so the amount column stays numeric.

Write down what you checked. A note on the first sheet such as "Totals tied to pages 2 to 9 of the scan" tells a colleague, and later you, how far the workbook can be trusted. The general method for checking recognized text is in the post on why OCR output is full of errors.

Tables that span several pages

A table that runs over several pages is detected page by page, so it arrives as several tables. In a Schema Law workbook, a ledger running from page 4 to page 9 typically gives you six worksheets, each named with its page. You join them yourself, and the join is where rows go missing.

Stack them in this order:

1. Create a new sheet for the combined table and leave the per-page sheets as they are. They are your audit trail.
2. Add a Page column first. Before copying each block, fill a column with its source page number, taken from the sheet name. Every row then carries its origin, which you will want when someone asks where a figure came from.
3. Copy the first page's rows with the header. Copy later pages without their header row. Most multi-page tables repeat the header on every page.
4. Check the column order page by page. The software decides the grid afresh on each page. Page 6 may have five columns where the others have six because an empty column vanished. Line the columns up before pasting.
5. Delete carried-forward lines. "Balance brought forward" and "Continued" rows at page tops and bottoms are not transactions. Leaving them in double-counts.
6. Mend rows broken by the page boundary. An entry that starts at the foot of one page and finishes at the head of the next needs to be rejoined by hand.

Then check the seams. For each page boundary, compare the last row before it and the first row after it with the scan. Finally, tie the combined total to the grand total on the last page.

Documents made of many statements, such as a year of monthly bank statements, add another layer: each statement has its own opening and closing balance. The post on bank statements to Excel for family law deals with reconciling a run of statements.

Numbers, dates and currency in exported cells

Exported cells hold text, exactly as it was read from the page. Excel will not add, sort or filter them as numbers or dates until you convert them. Do the conversion deliberately, column by column, because a spreadsheet program's automatic guesses about data types can change values without telling you.

Text cells are the safer starting point. Nothing has been reinterpreted, so the workbook still shows what the page showed. Microsoft's guidance on converting numbers stored as text to numbers says such cells can cause unexpected results in formulas, and describes fixing them with the Convert to Number option or the VALUE function.

Convert into a new column and keep the text column beside it. Things to handle on the way:

• Currency symbols and separators. Strip "$" and thousands commas before converting. Watch for a period read as a comma, which turns 1.250 into 1,250.
• Negatives. Statements print negatives as (125.00), 125.00- or 125.00 CR. Each needs its own rule. Getting this wrong flips the sign of the amount.
• Stray characters. A lowercase "l" for 1, a capital "O" for 0, or a space inside a number will make a cell refuse to convert. Cells that fail conversion are worth reading against the scan, since the failure itself points at a misread.
• Dates. 03/04/2021 is March 4 or April 3 depending on who wrote it and on your computer's regional settings. Decide which from the document, then convert. Two-digit years need the same care.
• Percentages. "4.5%" as text becomes 0.045 as a number. Make sure the column is consistent.
• Identifiers that look like numbers. Account numbers, claim numbers, ZIP codes and Bates numbers should stay as text. Converting them drops leading zeros. Long ones are worse: the Excel specifications and limits page gives number precision as 15 digits, so a 16-digit card or account number cannot be held exactly as a number.

After converting, count again: the number of numeric cells in the column should equal the number of data rows. Any shortfall is a cell that did not convert, and you should find it before it is left out of a sum.

Once the columns are typed and the totals tie, the workbook is ready for the work you wanted it for. If you are still deciding which format suits a document, the export formats on the homepage list what each is for.