Convert PDF Tables to Excel

Finding a table on a page that contains no tables

A PDF has no table object. What looks like a grid is a set of drawn lines and a set of positioned text runs that happen to align, and extraction has to work backwards from that appearance to a set of rows and columns.

There are two ways to do it and they behave very differently. Where the table is ruled, the lines themselves are the answer: find the horizontal and vertical rules, intersect them, and every cell boundary is known exactly. Text is then assigned to whichever box it falls inside. This is close to deterministic, which is why a bordered financial table converts so cleanly.

Where there are no rules, the only remaining evidence is alignment. Column boundaries have to be inferred from vertical bands of whitespace running down the page, and row boundaries from the vertical positions of text baselines. It works well on regular data and starts to fail wherever the layout is irregular — a wrapped cell, a merged heading, an indented subtotal line.

Ruled tables are read. Unruled tables are estimated.

What survives into the workbook

Values arrive typed where the tool can tell what they are. A figure that reads as a number becomes a number, so the column sums; anything ambiguous stays as text, which is why a cell that looks numeric sometimes refuses to add up.

Several conventions cause exactly that. Negative numbers in parentheses, as accounting statements print them. Thousands separated by spaces rather than commas. A currency symbol inside the cell instead of in the column heading. A footnote marker appended to a figure. Each of these turns a number into a string, and each is fixed in a moment once you know to look for it.

Structure survives less well. A heading spanning several columns is flattened into one cell, and a two-line header becomes two rows. On a table running across several pages, a header repeated at the top of each page arrives repeated in the data — visible as a row of text sitting in the middle of your numbers, and easy to filter out once spotted.

A worked example: six months of bank statements

Six monthly PDF statements, each three or four pages, each with a ruled transaction table carrying date, description, debit, credit and running balance. Small files, nowhere near the 100 MB ceiling.

Convert one first rather than all six. The layout is identical across the set, so whatever the first one does the rest will do, and finding that out on one file rather than six is worth the two minutes.

Then reconcile before doing anything else with it. Take the opening balance, add the credits column, subtract the debits, and compare the answer with the closing balance printed on the last page. If it agrees, the extraction is sound end to end — every row present, every figure in the right column, every value read as a number.

That single check is worth more than reading down the sheet looking for mistakes, because the errors extraction actually makes are quiet ones. A column boundary drawn one character too far left moves a digit between two figures and produces two plausible-looking numbers, which no amount of scanning by eye reliably catches.

Give the converter less to get more

Extraction quality falls as the page gets busier, so the most effective thing you can do is narrow what you send it.

If the tables you want sit on four pages of a sixty-page annual report, take those four pages out with Extract Pages and convert only those. You remove every chance of a paragraph of narrative, a chart legend or a footnote block being read as table data, and you get a workbook you can use immediately instead of one you have to clean.

The same logic applies to appendices, cover pages and anything with a multi-column layout. Prose in columns is the layout most likely to be mistaken for a table, because columns of text align exactly as columns of data do.

And check the totals rather than the appearance. A workbook that looks right is not evidence of anything — extraction failures are almost always tidy, well-formed and wrong, which is precisely what makes reconciling against a printed figure the only check worth relying on.