Skip to content
trending

AI Tools

Convert PDF to Excel and check the table before using the numbers

Import a PDF table through Power Query, spot repeated headers and broken rows, and validate totals before relying on the Excel output.

By 5 min read
Convert PDF to Excel and check the table before using the numbers

👁 111 reads

In a supported Excel installation, Power Query’s PDF connector can identify tables and bring them into a workbook. I would treat that output as an imported draft until the row structure, identifiers, and totals agree with the source document.

Start with the kind of PDF you have

A statement generated from software and a scan of a printed statement can look similar on screen while containing very different information. Try selecting a value and searching for a distinctive phrase in the PDF. These are useful first checks, although a searchable scan can also contain an OCR text layer.

If the required text is not available to the extraction tool, address text recognition before expecting a reliable table import. Keep the original PDF throughout the process. It remains the reference for checking values and investigating conversion errors.

I would also identify the exact pages and table needed. Converting a whole report when the task concerns one schedule creates extra cleanup and makes it easier to include a subtotal or explanatory table by mistake.

Import through Power Query

Open Excel’s data-import controls and choose the PDF source where available, then select the file. In Navigator, inspect the detected tables and choose Transform Data when the output needs cleaning before loading.

Microsoft documents the PDF connector for Excel and other Power Query hosts, while noting that capabilities can differ by host and rollout. If the PDF option is absent, check the installed product and supported connectors rather than assuming every Excel edition has the same menu.

For large documents, the connector supports page-range options through Pdf.Tables. Microsoft also documents MultiPageTables for combining similar tables on consecutive pages. Those controls can help narrow the import, but they do not replace checking that the intended rows were captured.

Decide what counts as a data row

PDF layouts often repeat headers at page breaks and place totals among ordinary rows. A spreadsheet needs a consistent meaning for each row before formulas can be trusted.

Consider this deliberately simple example:

Extracted description Extracted amount Interpretation
Service A 120 Detail row
Service B 80 Detail row
Page subtotal 200 Summary of the two detail rows
Service C 50 Detail row
Grand total 250 Summary of all detail rows

The intended detail total is 250, from 120 + 80 + 50. Summing every displayed numeric row gives 700, because both summaries are counted again. The figures are synthetic and supplied only to illustrate the validation problem.

I would keep summaries available as control values while excluding them from the detail table used for calculation. Deleting them before recording the expected total discards a useful check.

Inspect the errors that a clean layout can hide

Potential issue What to inspect Why appearance is not enough
Repeated header Rows containing column labels inside the table It may look like an ordinary text record
Wrapped description Adjacent rows belonging to one item A continuation line can become a false record
Identifier converted to a number Leading zeros and long codes Formatting cannot recover lost source characters
Decimal interpretation Source punctuation and imported numeric value A plausible-looking number can have the wrong magnitude
Negative value Minus sign or accounting parentheses A lost sign changes the meaning
Page boundary Last record on one page and first on the next The break may split one logical row

For wrapped rows, Microsoft discusses cleanup operations such as filling values down or grouping related rows. I would apply those only after identifying a reliable rule. Filling every blank cell can spread a value into records where it does not belong.

Keep original extracted columns until the cleaned result passes inspection. A helper column showing the proposed interpretation makes disagreements easier to investigate than overwriting the source values immediately.

Validate before the table feeds another process

My acceptance checks would include the number of detail rows, the total where a meaningful control total exists, and a selection of records from different pages. Include an awkward record with a wrapped description or unusual identifier rather than checking only the cleanest first row.

Compare the source and imported value for any high-consequence field. In a payment schedule, that could be account identifiers and amounts; in an inventory, product codes and quantities. The appropriate review depends on how the spreadsheet will be used.

If the PDF layout changes each month, do not assume last month’s query remains correct. A refreshed table can load successfully while selecting a different detected table or interpreting a new page layout badly.

I would keep a small import note: source filename, relevant pages, cleanup rules, control totals, and review date. The next person can then repeat the check without reverse-engineering the workbook.

Check the file type as well as the numbers

An .xlsx file is an Excel workbook format used on both Windows and macOS. The final x does not mean that the file was created on a Mac. A converter that produces CSV has given you a different kind of output: a text table, not a workbook with multiple sheets and its original formatting.

Before choosing an output, decide whether you need separate worksheets for separate source tables or one continuous table for analysis. Open the downloaded result and inspect the sheet names and row boundaries. A successful download tells you that a file was created; it does not prove that repeated headers, totals or page breaks became the right rows.

FAQ

Can Excel import a PDF without an online converter?

Supported installations can use Power Query’s PDF connector. Check the available connector in your Excel product.

Why is the imported table empty or unusable?

Inspect the PDF’s text and structure, the selected table, and any need for text recognition. A visually readable page does not guarantee extractable table data.

Why does the total become too large?

Look for subtotal and grand-total rows counted alongside detail records. The worked example shows how double-counting can happen.

Should I load every detected table?

Only if the task requires them. I would identify the target table and pages first, then verify coverage.

Can I reuse the same query next month?

Often the workflow is reusable, but check the refreshed result against the new source. Successful refresh is not evidence that the layout remained compatible.

Google · Your sourcesQuantslant higher in your Google - one clickAdd to preferred sources →

Sources

Kinetyra

// read next

More on this from QUANTSLANT

Piotr Olszewski

Editorial team

Independent AI publication. Models and tools are tested on real tasks, prices are checked against official rate cards, and articles are updated when things change.

read nextTurn a Microsoft Teams meeting transcript into a usable decision recordai toolsTurn a Microsoft Teams meeting transcript into a usable decision recordFind and download a Teams transcript, check names and decisions, and prepare a useful meeting record without treating automated text as approval.

Most read

  1. 1Best AI Video Generator Apps for Your Phone (2026)
  2. 2Claude Opus 5.5 Motion Graphics: 60s Showreel from One Prompt
  3. 3Wan 2.2 on RTX 4090: timings, VRAM and four test clips
// site map
Quantslant in numbers 1,525posts in archive31news from the last 7 days18video models in the observatory
🎬 AI Video Benchmarks: Higgsfield DoP vs OpenAI Sora • Higgsfield vs Runway Gen-3 • AI Video Credit Calculator
× ‹ enlarge ›