Every Monday, one of two estimators at a commercial electrical contractor opens six supplier quotes and types about 140 line items into the estimating spreadsheet: catalog number, description, quantity, unit and price. Two quotes are clean digital PDFs. One is a scan of a fax. Another has a table that runs across three pages with the header repeated on each.
Wire is priced per thousand feet, so one slip in the unit turns a $600 line into $600,000, or into sixty cents. The estimator catches most of these. The question is how to stop typing without letting the rest through.
First, find out what kind of PDF you have
PDFs come in two kinds, and they need different tools. A digital PDF, exported from the supplier's system, already contains real text, so extraction reads the characters exactly and the challenge is structure. A scanned PDF is a picture of a page. It needs OCR first, and OCR can misread characters.
Microsoft's documentation offers a quick test: try to select a single word. If you can only select the whole page as one image, the PDF is image-based. Do this for a sample of each source before choosing anything, because one supplier may send both kinds.
Pick the lightest tool that works
| Situation | Tool | Watch out for |
|---|---|---|
| A few digital PDFs with clear tables | Excel's Get Data from PDF (Power Query), in Excel for Microsoft 365 on Windows | Rows that wrap onto two lines; tables split across pages |
| Many digital PDFs in the same layout | The same Power Query import, pointed at a folder | A supplier changing its layout breaks the query |
| Scanned pages or phone photos | OCR, then extraction | Misread characters, so values need confidence scores and review |
| Many layouts, and values that must be right | AI extraction with validation and a review queue | Needs a field spec and a side-by-side test first |
Excel's PDF connector is underrated for digital files. Check your version first: Microsoft's list of Power Query sources includes PDF for Excel for Microsoft 365 on Windows, but not among the sources in Excel for Mac. On a Mac, a PDF editor's export to Excel or CSV works for digital PDFs only; a scan needs OCR first. Otherwise, run the import on a Windows machine.
Once you're in, Microsoft documents options to limit the page range and to merge similar tables on consecutive pages. They aren't checkboxes in the import dialog; you set them in the Pdf.Tables formula, in the formula bar or the Advanced Editor. For many PDFs at once, Microsoft suggests the Folder connector. None of it knows whether a number is right, though, which is where the next steps come in.
Write the field spec before anything else
A field spec is a one-page agreement about what goes in each column. It's the single most useful thing you can write, whether you automate with Excel, a vendor or a person.
| Field | Format | Rule |
|---|---|---|
| Supplier | Text, from your supplier list | Must match a known supplier |
| Quote number and date | Text; MM/DD/YYYY | Date within the last 30 days |
| Catalog number | Text, never a number | Keep leading zeros and dashes exactly |
| Quantity | Number | Greater than zero |
| Unit | E, C or M (each, per hundred, per thousand) | Must be one of the three |
| Unit price | Currency | Flag if far from the last price paid |
| Extended price | Currency | Equals quantity times price, divided by 100 for C or 1,000 for M |
Decide the shape of the sheet, too. Repeat the header fields (supplier, quote number, date) on every line row, so the sheet can be filtered and pivoted. Keep a second tab with one row per document: file name, total, number of lines and a status of passed, reviewed or rejected.
Validation rules that catch what eyes miss
Extraction gets values off the page. Validation decides whether to believe them. The best rules use the document's own arithmetic against itself:
- Arithmetic. Each line's extension matches quantity times price; lines sum to the subtotal; subtotal plus tax and freight equals the total.
- Row count. The number of rows extracted matches the number of lines on the page, so nothing is dropped or doubled.
- Ranges. Dates fall in the expected window, quantities are positive, prices sit near the last price paid.
- Formats. Catalog numbers stay text; negative amounts shown in parentheses become negative numbers.
- Continuity. For an accounting firm pulling client statements, each statement's opening balance equals the previous closing balance.
Flowchart
One PDF, from folder to spreadsheet row
- StartA supplier quote PDF lands in the “Quotes in” folder
- DecisionDoes the PDF have a real text layer?NoStepOCR runs first, and every character gets a confidence scoreYes
- StepHeader fields and line items extracted according to the field spec
- StepChecks run: extensions, subtotal, total, formats and row count
- DecisionAll rules pass, and every field above its confidence bar?NoA personEstimator sees the page beside the row, fixes the value and notes whyYes
- StepRows written to the sheet, each with its file name, page and line
- DoneThe estimate is priced from checked numbers
Notice that the person in that flow only sees rows that failed something, and sees them with the page alongside. That's the difference between automation that saves time and automation that simply moves the typing into a checking job.
Before and after
Monday's quotes, before and after
Typing from the PDF
- 01PDF on one screen, spreadsheet on the other
- 02Each line typed, then spot-checked if there's time
- 03Leading zeros and per-thousand prices mangled quietly
- 04No record of which page a number came from
ResultHours each week, and errors found in the estimate
Spec, extract, validate
- 01Fields defined once, with a rule for each
- 02Lines extracted and the arithmetic checked automatically
- 03Failures and low-confidence values shown beside the page
- 04Every cell traceable to its file, page and line
ResultTyping replaced by reviewing the exceptions
Run it side by side before you trust it
- 01For two weeks, keep typing as usual while the system extracts the same PDFs.
- 02Compare the two results cell by cell, and count differences per field, not overall.
- 03Look at every difference and decide who was right. Sometimes it's the system.
- 04Set a confidence threshold per field: strict for prices and catalog numbers, looser for descriptions.
- 05Go live one field or one supplier at a time, and keep the review queue.
Keep an audit trail from the first day: for each row, the source file, page and line, when it was extracted and whether a person changed it. When someone asks where a number came from in six months, the answer takes one click.
If one source fails the same rule week after week, fix it upstream. Ask that supplier or client whether they can send a spreadsheet or CSV export instead of a PDF. When they can, a clean export beats the best extraction, and the PDF becomes the backup.
Pitfalls that trip up PDF extraction
- Tables across pages. Repeated headers imported as data, page subtotals counted twice, a row split by a page break.
- Wrapped rows. A long description spills onto a second line and becomes a second row. Microsoft's documentation notes multi-line rows may need cleanup after import.
- Look-alike characters in scans. 0 and O, 1 and l, 5 and S, 8 and 3, which matter most in catalog and account numbers.
- Excel's helpful conversions. Long numbers turned into scientific notation, leading zeros dropped, “1-2” turned into a date. Format those columns as text before import.
- Sectioned statements. Brokerage and bank statements hold several tables, such as dividends, interest and sales, that must not be merged.
- Locked files. Password-protected PDFs usually need the lock removed first; Microsoft's document service, for one, requires it.
If your PDFs are mostly scans, start with how OCR differs from AI extraction to see what each layer adds. For contractors, the same approach pulls scope, addenda and due dates from bid documents; see how it fits for electricians.
Doing this with Document Processing Factory
Script
- Custom code
- Breaks when layout changes
Factory
- Widgets on a canvas
- You adjust when layout changes
The field spec and validation rules in this guide are exactly what you set up in Document Processing Factory. Instead of a script, you drag widgets onto a canvas, or we build the workflow for you: bring PDFs in from Amazon S3, a SharePoint site or email, sort them by type, extract the fields you listed, and send them to your software with an API call or save them to a database. When the PDF layout changes, you adjust the workflow yourself.
Book a call with a few sample PDFs and the spreadsheet they end up in.