PDF to Excel: Getting Tables Out Without Retyping Them

There are no tables in PDF. That is the whole problem in one sentence. A PDF stores glyphs at coordinates; a table is something a human infers from the arrangement of those glyphs. Extraction software has to reverse-engineer a structure that was never recorded.

Two kinds of table, two different jobs

Ruled tables

Tables with visible borders are the easy case. The rules are drawn as vector lines, so the extractor can detect the grid geometrically and assign each text run to a cell. Accuracy on clean ruled tables is very high.

Whitespace tables

Financial statements and reports often use alignment alone. The extractor has to cluster text by x-position and guess column boundaries. This works well until a single cell wraps to two lines, or a value is missing, or a column is right-aligned while its neighbour is left-aligned. Then rows shift.

The failure modes you will actually see

• Merged headers spanning two columns collapse into one cell.
• Multi-line cells split into separate rows.
• Numbers in parentheses — accounting negatives — import as text, not as -1234.
• Thousands separators and currency symbols make the whole column text.
• Footnote markers glued to values: '1,204a' instead of 1204.
• Repeated page headers appearing mid-dataset every 40 rows.

A five-minute cleanup routine

1. Sort by the first column and look for rows that are obviously fragments of the row above — those are your wrapped cells.
2. Find and remove repeated header rows in one pass.
3. Select each numeric column and check the sum against a total printed in the source document. If it matches, the column is clean. This one check catches nearly everything.
4. Convert accounting negatives: replace parentheses with a minus sign, then re-type the column as number.
5. Strip currency symbols and separators before conversion, not after.

When the source is a scan

If your PDF is a scan, extraction is OCR first and table detection second, and errors compound. Digits are the highest-risk characters because no dictionary catches them. For scanned financial data, always reconcile against a known total; never trust the output on inspection alone.

The workflow that saves the most time

Extract, then verify with a sum, then clean. Most people clean first and verify last, which means they spend twenty minutes tidying a table that turns out to have dropped a row. One checksum up front tells you whether cleanup is worth starting.