Skip to content

From PDF to Spreadsheet: Automating Data Entry Without Losing Accuracy

By USA AI Corp · · 5 min read

The short answer

To automate data entry from PDF to Excel accurately, first check whether your PDFs are digital or scanned, then write down exactly which fields you need and the rules each one must pass, such as lines that add up to the total and dates in range. Run the extraction alongside your current process, set confidence thresholds per field, and link every cell back to the page it came from. For a few clean digital PDFs, Excel's own PDF import may be enough.

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

Matching the tool to the job
SituationToolWatch out for
A few digital PDFs with clear tablesExcel's Get Data from PDF (Power Query), in Excel for Microsoft 365 on WindowsRows that wrap onto two lines; tables split across pages
Many digital PDFs in the same layoutThe same Power Query import, pointed at a folderA supplier changing its layout breaks the query
Scanned pages or phone photosOCR, then extractionMisread characters, so values need confidence scores and review
Many layouts, and values that must be rightAI extraction with validation and a review queueNeeds 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.

A field spec for supplier quote lines (example)
FieldFormatRule
SupplierText, from your supplier listMust match a known supplier
Quote number and dateText; MM/DD/YYYYDate within the last 30 days
Catalog numberText, never a numberKeep leading zeros and dashes exactly
QuantityNumberGreater than zero
UnitE, C or M (each, per hundred, per thousand)Must be one of the three
Unit priceCurrencyFlag if far from the last price paid
Extended priceCurrencyEquals 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

  1. StartA supplier quote PDF lands in the “Quotes in” folder
  2. DecisionDoes the PDF have a real text layer?
    No
    StepOCR runs first, and every character gets a confidence score
    Yes
  3. StepHeader fields and line items extracted according to the field spec
  4. StepChecks run: extensions, subtotal, total, formats and row count
  5. DecisionAll rules pass, and every field above its confidence bar?
    No
    A personEstimator sees the page beside the row, fixes the value and notes why
    Yes
  6. StepRows written to the sheet, each with its file name, page and line
  7. DoneThe estimate is priced from checked numbers
Every value either passes its rules or reaches a person with the page beside it.

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

  1. 01PDF on one screen, spreadsheet on the other
  2. 02Each line typed, then spot-checked if there's time
  3. 03Leading zeros and per-thousand prices mangled quietly
  4. 04No record of which page a number came from

ResultHours each week, and errors found in the estimate

Spec, extract, validate

  1. 01Fields defined once, with a rule for each
  2. 02Lines extracted and the arithmetic checked automatically
  3. 03Failures and low-confidence values shown beside the page
  4. 04Every cell traceable to its file, page and line

ResultTyping replaced by reviewing the exceptions

The estimator stops typing and starts reviewing only the lines that fail a rule.

Run it side by side before you trust it

  1. 01For two weeks, keep typing as usual while the system extracts the same PDFs.
  2. 02Compare the two results cell by cell, and count differences per field, not overall.
  3. 03Look at every difference and decide who was right. Sometimes it's the system.
  4. 04Set a confidence threshold per field: strict for prices and catalog numbers, looser for descriptions.
  5. 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

  1. PDF
  2. Custom code
  3. Breaks when layout changes

Factory

  1. PDF
  2. Widgets on a canvas
  3. 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.

Sources

Common questions

Quick answers.

Can Excel extract data from a PDF automatically?

Yes, for digital PDFs, in Excel for Microsoft 365 on Windows. Its Get Data from PDF option, built on Power Query, imports tables and can refresh them, and a folder import handles many files with the same layout. Excel for Mac doesn't list PDF as a source. Either way, Excel doesn't check whether the values are correct.

How do I extract data from a scanned PDF?

A scanned PDF is an image, so it needs OCR before any data can be pulled out. Use a tool that gives each value a confidence score, add validation rules such as totals that must add up, and send low-confidence values to a person.

How do I check that extracted PDF data is accurate?

Run the extraction alongside manual entry for a couple of weeks and compare cell by cell, counting errors per field. Keep arithmetic checks running after go-live, and store the file, page and line for each value so spot-checks are quick.

What's the best PDF data extraction tool?

It depends on your files. Excel's Power Query import suits a few clean digital PDFs. Desktop PDF editors with table export suit occasional one-offs. Cloud document-AI services suit scans and varied layouts, if you add checks. A custom setup with a review queue fits when values must be right every time and feed other systems.

Ready when the work is.

Show us what is slowing your business down.

Book a call

Not ready to talk?

Written brief

Skip the call.
Send a brief.

A few sentences is plenty. We read every brief and reply by email within one business day.

Try it free. If you love it, keep it. If it's not for you, we take it back, no questions asked.

We only use this to reply to you. Privacy policy