Converting PDF Invoices to a Clean Spreadsheet

You receive invoices as PDF files. Accounting needs the line item data in a spreadsheet for reconciliation, expense tracking, or import into the GL. Manually retyping each line is slow and error-prone.

ExploreMyData can extract the table from a text-based PDF, clean it up through a pipeline, and export the result as Excel. The entire process runs in your browser.

Step 1: Load the PDF

Drop the invoice PDF onto ExploreMyData. The parser reads the text layer, detects the table structure, and loads it as a dataset. A typical invoice might extract like this:

The extraction is automatic, provided the PDF has a selectable text layer and a table with clear column boundaries. A scanned image of an invoice has no text layer, so there is nothing to read.

Step 2: Fix the column names

Headers come out of a PDF however they sat on the page: extra whitespace, two words run together, or something generic like Column1. The first row the parser detects in a PDF table becomes that table's header, so if it latched onto the wrong row you can end up with a header made of invoice numbers and a real header sitting in the data.

First place to look is the import itself. When a PDF yields more than one table, a Select tables to load dialog opens. Each detected table is a row: a name (Table 1, Table 2, and so on), a preview of its first three columns, and a row count, with a checkbox on each, a "Select all" link above and a "Load 2 tables" button at the bottom. The previews are usually enough to tell a header block apart from the line item table. If you picked wrong, load the file again and tick a different one.

What is not available here: the Configure CSV Parsing dialog, the one with "First row is header" and "Skip first N rows". Those options belong to .csv, .tsv and .txt imports. A PDF has no delimited text file behind it to re-parse, so the dialog cannot rebuild the table for you. Update Values will not rescue you either. It writes into cells, not into column names, so it cannot promote a data row to the header.

The honest fix after loading is two pipeline steps:

• Click the green + in the Pipeline panel and select Rename Columns from the Columns group. Each line is a "Choose column..." dropdown, an arrow, and a "new name" box; "Add rename" gives you another line. Four lines here: Item to item, Quantity to quantity, Unit Price to unit_price, Amount to amount. Columns you do not list are left alone.
• If the old header is now sitting in the data as a row, add a Filter from the Filter & Sort group: Action "Remove matching rows", condition item is Item. Same trick clears a "Subtotal" line.

Step 3: Convert types

PDF extraction often produces text columns for everything. The unit_price and amount columns in the example above carry dollar signs, so they come in as strings. Click the green + in the Pipeline panel and select Convert Type from the Transform group, pick the column, and set "Convert to" to numeric. It handles one column per step, so that is two cards.

You do not need a Find & Replace pass first. Text to numeric strips commas, currency symbols and percent signs on the way through, and reads an accounting negative like (500) as -500. The converted column keeps its name and its position in the grid.

After conversion, the data looks like this:

Step 4: Stack several invoices into one table

Load each PDF as its own file; the app keeps several open at once, each with its own pipeline. To put their line items in one table, open the invoice you want to build on, click the green + in the Pipeline panel and select Union All from the Data group. Two fields: "Append rows from", a dropdown of the other loaded tables, and "Column matching", where By name is the default and any column the other table is missing comes back NULL. Apply and the grid keeps the same columns with a longer row count: a 4-line invoice plus a 6-line invoice reads 10 rows. Nothing in those rows says which invoice they came from, so if you need that, add an Add Column step named invoice_no with the expression 'INV-4471' on each file before you union them.

Step 5: Check the total against the invoice

Click the green + in the Pipeline panel and select Group & Aggregate from the Aggregate group. Leave "Group by" empty, and add one aggregation row: function SUM, column amount. With no grouping columns the whole table collapses to a single row and a single column called amount_sum: the alias is always the column name plus the lowercased function, and there is no field to override it. For the four lines above that reads 1,632.50, which you can hold against the figure printed on the invoice.

If you stacked several invoices in step 4, put invoice_no in Group by instead of leaving it empty. Same aggregation, one total per invoice, and any row whose total is off announces itself.

Step 6: Export as Excel

Delete the Group & Aggregate card once the total checks out. An export takes whatever the pipeline currently produces, and leaving that card in place means exporting one row reading 1,632.50 instead of your four line items.

Then open the Export menu in the toolbar and choose Export as Excel (.xlsx). A toast confirms the row count and file size, and the download lands with your columns typed: text in item, real numbers in quantity, unit_price and amount, so Excel formulas and sorting work on arrival.

When this works best

This workflow is designed for invoices where the line item table is clearly structured: distinct columns, one row per item, no nested sub-tables. Most standard commercial invoices follow this pattern. For invoices with unusual layouts (items split across multiple lines, sub-totals mixed into the line item rows), expect to add a Filter step to drop the rows that are not line items, and an Update Values step from the Transform group to patch the cells that came through wrong.

For background on how the PDF parser works and its limitations, see Extracting Tables from PDF Files in Your Browser.

Convert an invoice now →