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:
| Item | Quantity | Unit Price | Amount |
|---|---|---|---|
| Widget A-100 | 50 | $12.00 | $600.00 |
| Widget B-200 | 25 | $24.50 | $612.50 |
| Connector C-50 | 100 | $3.75 | $375.00 |
| Shipping | 1 | $45.00 | $45.00 |
The extraction is automatic. If the PDF has selectable text and a clear table layout, the parser will pick it up.
Step 2: Fix column names
Sometimes the PDF's header row extracts with extra whitespace, concatenated text, or
generic names like Column1.
Two options for fixing this:
- Rename Column operation: rename each column individually.
- Update Values: if the actual headers ended up in the first data row, use this to promote them, then delete the row.
Step 3: Convert types
PDF extraction often produces text columns for everything. The
Unit Price and
Amount columns in the example above
contain dollar signs, so they extract as strings. Add a Convert Type
operation to cast them to numeric. The conversion automatically strips currency symbols and commas.
After conversion, the data looks like this:
| item | quantity | unit_price | amount |
|---|---|---|---|
| Widget A-100 | 50 | 12.00 | 600.00 |
| Widget B-200 | 25 | 24.50 | 612.50 |
| Connector C-50 | 100 | 3.75 | 375.00 |
| Shipping | 1 | 45.00 | 45.00 |
Step 4: Handle multiple invoices
If you have several invoices to process, load each PDF as a separate file. ExploreMyData supports multiple open files. You can work on each one independently, applying the same pipeline steps to each, then export them separately. For combining line items from multiple invoices into one table, use the Join operation or simply export each and combine in your accounting tool.
Step 5: Compute the invoice total
Add a Group & Aggregate operation with
SUM(amount) to verify
the invoice total. This is useful for reconciliation: compare the computed sum against the
total printed on the invoice.
Invoice total verification
- Operation: Group & Aggregate
- Aggregate: SUM(amount)
- Result: 1,632.50
If this does not match the invoice total, a line item may have been missed during extraction or a type conversion went wrong.
Step 6: Export as Excel
Click Export and select .xlsx. The cleaned, typed line item data downloads as an Excel file. Numeric columns will be formatted as numbers in Excel, so formulas and sorting work immediately.
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), you may need to add Filter or Update Values steps to clean the output.
For background on how the PDF parser works and its limitations, see Extracting Tables from PDF Files in Your Browser.