Convert PDF to Excel

How a grid is recovered from something that has none

A PDF does not contain a table. It contains characters at coordinates, and the lines that look like a table are drawn artwork with no connection to the text inside them. So the grid is inferred, by two rules worth knowing exactly. Rows come from shared baselines, with the vertical position rounded into four-point buckets so that a superscript or a slightly raised currency symbol does not start a row of its own. Columns come from collecting every distinct left edge on the page and merging any two within eight points of each other — done across the whole page rather than row by row, so a column that happens to be empty on one line still holds its place on the others.

That is why right-aligned numbers are the hard case, and it is worth saying plainly because it is the most common disappointment here. A column of figures aligned to the right has a different left edge on every row — 9.99 starts a long way right of 1,234,567.89 — and once those edges differ by more than eight points they are read as different columns. A financial table can therefore arrive spread across several columns that ought to be one. Left-aligned and centred columns do not have this problem. Two pieces of text that do land in the same cell are joined with a space rather than one overwriting the other.

Cells are typed on the way out: anything that parses cleanly as a number is written as a number and will sum, and everything else is written as text. The usual accounting decorations do not parse — a thousands separator, a trailing per-cent sign, a currency symbol, or a negative written as (1,234) in brackets — so those cells arrive as text that looks numeric and refuses to add up. Excel's own Text to Columns fixes a whole column of them in one pass.

Each page becomes its own sheet, named Page 1, Page 2 and so on, rather than being stitched into one continuous table — a three-page table arrives as three sheets with the header repeated. What comes across is values only. Formulas were never in the PDF to recover, and the fill colours, borders, fonts, merged cells and charts are deliberately not reconstructed, because guessing at them produces a spreadsheet that looks authoritative and is subtly wrong.

When you want something else

Table extraction is a genuine research problem and there are tools that do nothing else. Tabula is the one to reach for when the tables are ruled: it reads the drawn lines as the grid rather than inferring one from text positions, which solves the right-aligned-number case outright. It is free, it has a point-and-click interface, and it exports CSV. Camelot does the same from Python with both a lattice mode for ruled tables and a stream mode for unruled ones, and will tell you how confident it is.

When the layout is simple and you only want it readable, Poppler's pdftotext -layout in.pdf out.txt preserves the column spacing as plain text, which opens straight into a spreadsheet as fixed-width data and skips the inference entirely. And if the PDF is a scan, nothing here applies — there is no text to position, and it needs OCR first.