To extract data from a PDF report, pull the tables out with Excel's From PDF import, a free extractor like Tabula, or OCR (text recognition) if the pages are scans. Then rebuild the records the page layout split apart, and check them against the counts and totals the report already prints.
This one's for whoever has to make that work. You run onboarding or implementation at a software company, a new customer is leaving a system that only gives them PDF reports, and their history has to be in your product before go-live.
Why does a PDF report come out wrong?
Because a report is your customer's records laid out for a printer, not a spreadsheet. The PDF "only contains lines and character glyphs which we tend to interpret as tables," as one long-standing Stack Overflow answer puts it. So a converter has to guess where each row starts and ends.
Here's one customer from an illustrative service history report, the kind an old field service or billing system prints, where a customer's jobs run across a page break.
One customer across a page break in a service history report, and the records you need from it
On the left, the bottom of page 14 and the top of page 15 of an illustrative service history report. Five traps are marked: 1, the page header and footer repeat on every page; 2, the customer name and ID are printed once above their jobs; 3, the job of 04/02/2025 is split by the page break; 4, the note on job J-20903 wraps onto a second line; 5, a customer total row, 3 jobs and 1,115.00, sits between customers. On the right, the three records you need, each with the customer ID C-00412 on its row: J-20817 for 420.00, J-20903 for 185.00 and J-21044 for 510.00, which add up to the printed total of 1,115.00.
Each of the five traps does its own damage when you convert the page:
- The page header and footer repeat. On a 250-page report that's 250 copies of the column names and the page number, and each one can come out as a row.
- The customer is printed once. Their name and ID sit above their jobs, so the rows you get carry no customer at all. You have to copy that customer onto every job below it.
- A job splits at the page break. The 04/02/2025 job starts on page 14 and finishes on page 15, under a fresh header. Converted, that's two half rows.
- A note wraps onto a second line. "Replaced 2 heads" comes out as its own row, with no date and no amount.
- Subtotals sit between customers. "Customer total (3 jobs) 1,115.00" looks like one more job to a converter. Load it and you've doubled the customer's revenue history.
Excel's PDF import, for example, joins similar tables on consecutive pages into one by default, according to Microsoft. That helps with a long table. It also means the split job and the subtotal land in one long list with everything else, and you're the one who sorts them out.
One test first: try to select the text in the PDF. If you can't, the pages are scanned images, and they need OCR before any extractor can read them.
How do you extract data from a PDF?
Before you start, ask once whether the old system can run the same report as a CSV or Excel file, or give you a plain list of the records instead of a report. It's worth a day of waiting. The legacy system migration guide covers how to ask. When the PDF is all you'll get, you've got three kinds of tools to pick from.
The tools you already have. In Excel, go to Data, Get Data, From File, From PDF. Excel opens a Navigator that lists the tables it found, and you load the ones you want. Adobe Acrobat exports a PDF to Excel and lets you choose a worksheet for each table, each page or the whole document, with text recognition as a setting for scanned pages. Both are fine for a few clean pages.
Free open-source extractors. Tabula lets you draw a box around a table and export it to CSV. Its own README says it "only works on text-based PDFs, not scanned documents." Camelot does the same job in Python, and its newer version adds an optional mode for scanned pages. Someone on your team who's comfortable with a script can run a whole report through either.
Document AI services and chat models. Cloud services read scanned and digital PDFs, and you can paste a page into ChatGPT and ask for a table. They're the most capable option and the hardest to check. A 2026 benchmark of table extraction tools found that general vision-language models like GPT "suffer from table content hallucination": they write values that aren't on the page. LlamaIndex, which sells document parsing, describes the quieter failure: long tables that come back shorter than the page: the rows just stop, the table still looks tidy, and nothing warns you.
No tool gets every table right. The same benchmark, run on scientific documents, concluded that table extraction "remains challenging." Whatever you use, you'll need to prove the result.
And before you upload anything: these are your customer's records. Check where an online tool sends the file and what it keeps, and get your customer's OK if it leaves your systems.
How do you know the rows came out right?
Use the report against itself. When the old system printed the PDF, it also did the counting: how many jobs each customer had, what they added up to, and the total for the whole report. If your rows add up to the same numbers, nothing went missing and nothing extra got in.
| Check | How | What it catches |
|---|---|---|
| Records per customer | Count each customer's rows and compare with the count the report prints, like "3 jobs". | Rows lost at a page break, and one customer's jobs filed under the next customer. |
| Each customer's total | Take the subtotal rows out first. Then add up each customer's amounts and compare with the printed customer total. | Subtotal rows loaded as jobs, an amount pushed into the wrong column, a job under the wrong customer. |
| The grand total | Add up every amount and compare with the report's last line. | Whole pages that never came out. |
| A sample of page breaks | Read every page where a customer's jobs cross the break, plus a handful of others. | Split jobs, and page headers loaded as data. |
| IDs and dates | Compare a few rows with the page, character by character. | Dropped leading zeros, and day and month swapped (03/04/2025 is March 4 or April 3). |
In the example, Hollis Property Group should come to 3 jobs and 1,115.00. Let the subtotal row slip through and it sums to 2,230.00. Let the split job come out as two half rows and you count 4 jobs. Either way you'd know before a single record reached your product.
Let the numbers decide. Asking a second chat model to check the first isn't proof. A sum either matches the printed total or it doesn't. Open the result in a free CSV viewer to sort and scan it before you start adding things up.
Some reports print no totals at all. For those, count records against the old system itself, as in the legacy guide's section on proving nothing is missing. Then load a sample and have your customer check it before cutover, the way the onboarding process guide lays out.
What if the PDF runs to 250 pages?
Run the checks on the whole report, not on the pages you happened to look at. Nobody reads every row of 250 pages, and the printed totals are the only check that covers all of it. Start with a few pages to tune your settings, then run everything and let the totals tell you which customers to check by hand.
The harder part: the next customer is leaving a different system, and its report won't look like this one. In a Reddit thread from someone reading 500-page product PDFs by hand, one commenter said the answer "really hinges on how variable the structure of the data is in the PDF files." In customer onboarding, it varies with every deal.
That's where onboarding teams get stuck. Before Rubie, Granum's team faced what their customer story calls an impossible choice: decline customer PDFs, or accept them and watch admin time balloon. When they did take complex documents, they often processed just one page out of 250 and left the rest to the customer. In Granum's own words: "We've had (customers) who give us those 250 page PDFs, and in the past we couldn't even work with them, but now we have Rubie."
Rubie pulls data from the complex PDFs Granum couldn't process before, magazine-style layouts included. And the PDF is just one source in a finished migration: Rubie cleans and transforms the records into your product's shape, sends the flagged ones to a review queue where your team approves, rejects or edits them with an audit trail, and loads the rest. Your onboarding team runs it without engineering time, and by Rubie's own data, migration timelines are 50-90% shorter on average. If you're weighing whether your engineers should script this instead, the build vs buy guide runs the numbers.
