How to get a table out of a PDF and into Excel
· 6 minute read
Copying a table from a PDF usually makes a mess. Why that happens, what makes a table convertible, and how to check the numbers afterwards.
A bank statement, a price list, a table of results: the numbers are on your screen and you cannot do anything with them. You try to copy the table and paste it into a spreadsheet, and everything lands in a single column, or the columns are shuffled. This is not you doing it wrong. It is the way PDFs are built.
Why PDFs do not contain tables
A PDF does not know what a table is. It holds instructions to put particular characters at particular positions on the page. A column is simply a set of words that happen to line up vertically; a row is words that happen to share a height. The grid you see is a reading of the layout, made by your eyes.
A converter has to do the same reading. It looks at where each piece of text sits, groups pieces that share a vertical position into rows, and groups pieces that share a horizontal position into columns. When the layout is regular, the result is excellent. When it is not, you get errors.
What converts well
- Statements and lists with clear, evenly spaced columns.
- Documents produced by a program, where the text is real text.
- Tables where each row is a single line.
What causes trouble
- Scans. A picture of a table has no text positions to read. Run OCR PDF first.
- Merged cells and headings that span several columns.
- Cells with text wrapped over several lines, which can be read as separate rows.
- Tables that continue over several pages. Each page arrives as its own sheet, and you join them.
- Tables with no lines or gaps between columns, where words from neighbouring columns nearly touch.
A way of working
- Extract only the pages that hold the table. Use Split PDF to cut them out, which keeps the workbook small.
- Convert, and look at the first sheet against the PDF.
- Check the first and last rows, since errors accumulate at the edges.
- Fix columns that were merged or split. Text to Columns in a spreadsheet does this quickly.
Always check the numbers
A converted table can look right and be wrong: a minus sign dropped, a decimal point moved, two cells merged into one. Check the arithmetic. If the PDF shows a total, the sum of your cells must match it. A total that does not match tells you straight away that something was misread, which is far cheaper than finding it later.
Privacy
Statements are full of account numbers and transactions. Converters that run on a server receive them. One that reads the page in your browser does not. For financial documents, that is the better design.
Tools mentioned
- PDF to Excel — Lift the rows and columns out into a spreadsheet.
- OCR PDF — Make a scan searchable by reading the words off it.
- Split PDF — Take a range of pages out into a file of their own.