Getting tables out of a PDF and into Excel

A PDF does not contain a table

A PDF page is a list of drawing instructions, not a data structure. The content stream says "pick this font, move to this point, paint these glyphs, stroke a line from here to there". There is no notion of a row, a column or a cell anywhere in that list. What you recognise as a bank statement or a price list is simply text that has been positioned in neat vertical alignment, sometimes with rules drawn around it.

The one exception is tagged PDF. If the producer wrote a logical structure tree, the file really does containTable, TR and TD elements, and extraction becomes reading rather than guessing. In practice most files that come out of a print driver, a legacy reporting engine or a "save as PDF" menu carry no tags at all. So the job an extractor performs is reconstruction: rebuilding a structure that was thrown away at export time, using geometry as the only evidence.

Triage: what kind of file is on your desk

Open the file and try to drag-select one row of the table. That single gesture tells you which route to take.

• Text selects and the table has ruling lines: the easy case, the lines give you the grid.
• Text selects but there are no lines: columns will be inferred from whitespace alone.
• Nothing selects: the page is an image and needs OCR before anything else.
• Text selects but comes out as gibberish: the embedded font has no usable character mapping, and extraction will inherit exactly that garbage.

Extraction takes one of two routes

1. Follow the ruling lines

The tool collects horizontal and vertical strokes plus thin filled rectangles, finds where they intersect and builds a cell grid from those intersections. Each run of text is then assigned to whichever cell its centre falls into. On a ruled invoice or an audited financial statement this is close to exact. Camelot calls this its lattice mode, and the same idea sits behind most commercial extractors.

2. Follow the whitespace

With no lines to work from, all that remains is coordinates. A text extractor knows the horizontal start and end of every glyph box. The tool looks for vertical corridors that run down the whole page without a single glyph crossing them, and treats those as column boundaries. Rows come from the same logic on the vertical axis, clustering glyphs whose baselines sit within a tolerance of each other. Because it is a heuristic, it is sensitive to thresholds: tighten the corridor test slightly and two columns fuse, loosen it and one column splits in half.

Right-aligned numeric columns are the classic trap. A three-digit figure and an eight-digit figure start at different x positions, so the corridor test sees no clean gap on the left side of the column, while a left-aligned header above it may land in the neighbouring column entirely.

Six things that reliably break it

Five of those six come from visual choices in the source document. The prettier the table, the harder it is to rebuild; a plain ruled table with single-line cells extracts correctly in almost any tool.

The target format changes what survives

The same extraction looks different as CSV and as xlsx. CSV carries values only: cell formatting, merges and multi-line content are gone, and a line break inside a cell becomes a quoting problem that can corrupt the whole file. xlsx keeps cell types, so the number-versus-text distinction is visible in the file itself. If the data is heading into a database or a script, CSV is convenient; if a human is going to eyeball it first, xlsx saves work.

Wide reports hide one more trap. Some are printed in landscape by writing a rotation angle onto the page object while the text coordinates stay in the unrotated space, so an extractor may look for columns along the wrong axis. When the output is not merely wrong but nonsensical, try normalising the page rotation and running it again.

Scanned pages are a different problem

If the page is an image there are no glyphs to extract. Characters have to be recognised first, then the recognised boxes have to be grouped into rows and columns. The two steps feed each other: on a page scanned a degree or two off square, baselines drift and the vertical corridors close up, so the table can split wrongly even when every character was read correctly. Run PDF OCR first, and see what actually determines OCR quality before you blame the table extractor.

Verify before you trust the sheet

Table extraction fails quietly. The file opens, the columns look like columns, and two rows have silently merged. A five-minute check catches it.

1. Row count: compare the number of records in the sheet with the number in the PDF.
2. Column count: filter for rows that have fewer filled cells than the rest.
3. Totals: if the document prints a grand total, recompute it in the sheet. A mismatch means a lost row or a number stored as text.
4. Type check: numeric columns should right-align by default; anything left-aligned is still a string.
5. Edges: the first and last rows break most often, so read both against the original.

A workflow that holds up

1. Check whether text selects; if it does not, start with OCR.
2. In a long report, split out only the pages you need so the extractor has less to guess about.
3. Run the file through PDF to Excel and apply the five checks above.
4. If you want flowing text rather than a grid, PDF to Word preserves reading order better.
5. When the goal is only to show the table, rendering the page to an image is the fastest honest answer.

One caveat worth stating plainly: the PDF tools on this site upload your file to a server for processing. If the document is a payroll run or a signed contract, read how redaction really works before you share it, because drawing a black box over a name does not remove the name.