How to Convert a Utility Bill PDF to Excel

Start by checking the PDF. If you can select the text and pdftotext produces readable output, Excel’s PDF connector may be enough for a repeated layout. If the PDF is a scan, run optical character recognition (OCR) first. Bills from several utilities, multi-meter statements, and long charge tables need validation regardless of the extraction method.

For a small batch, manual entry can take less time than building a parser. For a recurring batch, the spreadsheet design matters as much as the tool: use one row per meter and service period, not one row per PDF.

Method comparison

First, find out what kind of PDF you have

Everything downstream depends on whether there’s a text layer. Two commands answer it:

pdffonts bill.pdf          # lists embedded fonts; empty output = scanned image
pdftotext -layout bill.pdf -   # dumps text preserving column positions

Run pdftotext -layout before opening Excel. If the output contains readable labels and values, Power Query may be useful. Empty or garbled output means you should inspect the pages and test OCR instead.

1. Retyping by hand

Manual entry is reasonable for a small, one-time batch. A query takes time to build and test, and that setup cost may exceed the work it saves.

The risk rises with volume and repeating meter blocks. Decimal shifts are easy to miss: 4,832 typed for 48,320 still looks like a valid number, and the bill’s total charge does not change to warn you.

If you retype, use a fixed template with data validation on the numeric columns, and enter usage before charges. Doing usage first means you notice when the implied unit price looks wrong.

2. Excel’s built-in PDF connector (Power Query)

Excel for Microsoft 365 on Windows includes the PDF connector. Microsoft’s current Power Query availability table does not list it for standalone Excel 2016 or 2019, or for Excel for Microsoft 365 on Mac. Check that table if your version is newer, because connector availability can change.

The sequence:

1. Data > Get Data > From File > From PDF
2. The Navigator pane lists detected objects: Table001, Table002, and a Page001 object per page.
3. Click through until you find the table holding the charge detail. Rarely the first one.
4. Transform Data, not Load.
5. Promote headers, set types, drop the junk rows.

The generated Power Query M looks like this:

let
    Source = Pdf.Tables(
        File.Contents("C:\bills\PGE_3812446075_2025-03-12.pdf"),
        [Implementation = "1.3"]
    ),
    Table003 = Source{[Id = "Table003"]}[Data],
    Promoted = Table.PromoteHeaders(Table003, [PromoteAllScalars = true]),
    Cleaned = Table.SelectRows(Promoted, each [Description] <> null and [Description] <> ""),
    Typed = Table.TransformColumnTypes(Cleaned, {
        {"Description", type text},
        {"Usage", Int64.Type},
        {"Amount", Currency.Type}
    })
in
    Typed

For a folder of bills, wrap it:

let
    Source = Folder.Files("C:\bills"),
    OnlyPdfs = Table.SelectRows(Source, each [Extension] = ".pdf"),
    Extracted = Table.AddColumn(OnlyPdfs, "Tables", each
        Pdf.Tables([Content], [Implementation = "1.3"])),
    Expanded = Table.ExpandTableColumn(Extracted, "Tables", {"Id", "Kind", "Data"}),
    ChargeTables = Table.SelectRows(Expanded, each [Kind] = "Table"),
    WithSource = Table.AddColumn(ChargeTables, "SourceFile", each [Name])
in
    WithSource

What actually happens on a real multi-page bill

Pdf.Tables detects table-shaped content; it does not understand utility-bill fields.

A multi-page commercial bill may produce several Table and Page objects. The remittance stub can be detected cleanly while the useful charge detail is split across objects. Electric and gas sections may also produce different column counts, so appending them without inspection can put values under the wrong headers.

Many bills use label-value blocks rather than table cells: “Total Amount Due” in a box, “Service Period” near a meter heading, and taxes as right-aligned text. Power Query may return those as page text instead of a usable table. You can split and reshape that text in M, but a layout change can break the query.

Where the connector earns its keep: interval data and rate-detail appendices. If your utility attaches a monthly consumption table with real gridlines, Pdf.Tables reads it cleanly and the refresh-on-folder pattern above is excellent.

The connector does not provide OCR for an image-only PDF. Microsoft’s Pdf.Tables documentation describes table extraction from the PDF binary, not image recognition.

3. Adobe Acrobat export to Excel

File > Export To > Spreadsheet > Microsoft Excel Workbook. In Acrobat Pro, the settings dialog lets you enable OCR when the page is an image.

Acrobat often preserves more of the visual layout than Power Query. That can help with a one-off conversion, but it also creates cleanup work:

• Currency may arrive as text, so $1,067.14 does not sum until it is converted to a number.
• Merged cells can prevent sorting and filtering.
• Headers, footers, and page numbers may appear as data rows.

Depending on the export settings, a multi-page bill may also become several worksheets. Test one representative file before committing to a batch.

4. OCR for scanned bills

If the PDF has no usable text layer, rasterize a test page and run OCR. Start at 300 DPI; raise or lower it after checking small rate-table text and file size.

pdftoppm -r 300 -png bill.pdf page
tesseract page-1.png out --psm 4 -l eng

Tesseract’s default --psm 3 performs automatic page segmentation. Mode 4 assumes one column of text of variable sizes; mode 6 assumes one uniform text block. Utility bills do not fit any of those assumptions neatly, so test several modes on a representative page. The mode definitions are in the Tesseract documentation.

The costlier failure is subtler: values read correctly and land in the wrong place. A rate table like this:

Summer Peak      12,450 kWh   @ 0.14827      1,845.98
Summer Part-Peak 21,310 kWh   @ 0.11204      2,387.60

comes out of plain-text OCR as a flat token stream, and if a line wraps or two rows merge, 12,450 pairs with 0.11204. Each number is individually correct, but the row is wrong. Plain-text output provides no automatic warning.

Use positional output when row and column relationships matter:

tesseract page-1.png out -l eng tsv

The tab-separated values (TSV) output contains left, top, width, height, and conf for each word. Those coordinates let you rebuild rows and columns. The Tesseract command-line guide documents the format. Expect custom grouping rules for each substantially different bill layout.

Budget for character confusions too. 0/O, 1/l, 5/S, 8/B are the usual set, and inside account numbers they are unrecoverable without a check-digit rule. Worse is comma-versus-period on a poor scan: 1,234 read as 1.234 is a thousand-fold usage error. Filter on conf and route low-confidence words to review.

5. AI and vision-model extraction

A vision model can use labels and page layout, which helps when a value sits in a box or beside a meter heading instead of in a table. It can reduce the amount of utility-specific parsing code, but it does not remove the need for checks.

Possible failures include omitted rows, a computed total substituted for the printed total, collapsed meter blocks, and content lost at a page break. Estimated-versus-actual flags are also easy to omit when the schema does not ask for them.

Ask for printed values rather than calculated ones, then perform arithmetic checks downstream. Store a source page with each value so a reviewer can find it quickly. The AI extraction validation guide covers the main checks.

Which fields to capture

Bill-level fields, recorded once and repeated across the meter rows:

vendor, account_number, statement_date, due_date, total_amount_due, previous_balance, payments_received, service_address, source_file, source_page

Meter-level fields, one set per meter per period:

meter_number, service_agreement_id, commodity, rate_schedule, service_start_date, service_end_date, billing_days, usage, usage_uom, demand_kw, line_charges, read_type

Watch the identifier columns. Account number, meter number, and service ID are three different things, labeled inconsistently, so getting the distinction between account number and meter number right early avoids a painful re-key later. There is a fuller treatment of which fields to pull from a utility bill and the label variants: “Service Period” on PG&E, “Billing Period” on Con Edison, “Read Dates” or a bare “From / To” on smaller municipal utilities.

Store dates as YYYY-MM-DD or true Excel dates rather than the MM/DD/YY string the bill prints. Store usage as a bare number and put the unit in its own column.

Lay it out one row per meter per service period

This decision determines whether the spreadsheet is still usable in six months.

One row per bill fails when a statement covers several meters with different service periods, commodities, and rate schedules. Flattening them into meter_1_usage, meter_2_usage, meter_3_usage columns means a four-meter bill forces a schema change, sorting by usage becomes meaningless, and pivots must be rebuilt when a site adds a meter.

One row per meter per service period gives you a table with a consistent grain. Pivots and filters work without meter-specific columns. Year-over-year by meter is one formula. Bill-level fields repeat across the rows belonging to a statement, which looks redundant and is correct. Do not SUM a repeated bill-total column.

Illustrative worked example

The following fictional PG&E-shaped statement shows how to structure a multi-service bill. The account, address, meter numbers, and charges are invented; the arithmetic is exact.

Printed on the bill:

Total Amount Due: $11,763.20

The gas period is offset one day from electric. A one-row-per-bill layout cannot preserve that difference cleanly.

Converted:

Three rows, one statement. bill_total_due repeats and must not be summed. demand_kw is blank on the two non-demand-metered agreements, and should stay blank rather than 0.

Validating the output

Run these before the data goes anywhere. Each one catches a distinct error class.

Charges reconcile to the bill total

Sum line_charges grouped by source_file and compare to bill_total_due. Here: 9,412.66 + 1,283.40 + 1,067.14 = 11,763.20. Exact match. A mismatch means a dropped meter row, or a genuine bill-level item (late fee, prior balance, credit) that needs its own column instead of being buried in a line charge.

Date continuity per meter

Sort by meter_number, then service_start. The prior electric period on meter 1009374412 ran 01/07/2025 to 02/06/2025, and the current one starts 2025-02-06. Same-day handoff, no gap. Some utilities set the new start equal to the prior end; others use prior end + 1 day. Pick the convention per utility and apply it consistently. A gap means a missing bill. An overlap means a duplicate, an estimated period later rebilled, or a misread year, and misread years cluster in January statements.

Related check: service_end − service_start should match the billing-day count printed on the bill under the same boundary convention. Both periods above compute to 30. A very short or long result deserves review.

Usage and implied unit price

Two arithmetic checks help catch decimal errors.

Usage per day against history: 48,320 / 30 = 1,610.67 kWh/day, versus 46,910 / 30 = 1,563.67 the prior month. Three percent up, seasonally reasonable. A decimal shift shows up here as a clean 10x and is otherwise invisible, because the dollar total stays correct.

Implied unit price: 9,412.66 / 48,320 = $0.1948/kWh, and 1,067.14 / 812 = $1.3142/therm. Compare those results with the same account’s history or its tariff. A result 100 times above or below the account’s normal range points to a decimal or unit error.

Units of measure

Gas may arrive as therms, CCF, MCF, or MMBtu. CCF-to-therms is not a fixed 1:1 conversion; use the heat-content factor on the bill. Electric bills generally use kWh, while some larger accounts use MWh. Record the original unit before converting.

Row count against meter count

Count distinct meter_number per source_file and compare it to the number of service agreements on the bill’s summary page. Three agreements, three rows. This catches collapsed multi-meter extraction, and it is the check people skip.

Send failed checks to review with the source page attached. Passing the checks lowers risk; it does not prove that all fields are correct.