How to Convert a Utility Bill PDF to Excel

Short version: under about twenty bills, retype them. A few hundred digital PDFs from one utility with a stable layout, use Excel’s built-in PDF connector (Get Data > From File > From PDF). If the bills are scanned, come from more than three utilities, or lay charges out in visual blocks instead of tables, no general-purpose converter works reliably and you are choosing between OCR-plus-scripting and an AI extraction pass with human review.

The conversion is the easy half. Where people go wrong is the layout of the resulting spreadsheet: one row per bill breaks the moment a statement covers two meters, and that describes most commercial statements.

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 you touch Excel. If the output reads like a bill with charges roughly in the right columns, Power Query has a chance. If it prints nothing, you are in OCR territory and can skip the next two sections.

1. Retyping by hand

Not a joke. A single-meter residential-format bill has maybe eight fields worth capturing, and an experienced person keys one in about a minute. Twenty bills is half an hour. Building and debugging a Power Query flow for the same twenty takes longer, and you will do it again when the utility changes its statement template.

Retyping breaks in three specific ways. Volume, obviously. Multi-meter bills, where transcription errors cluster because the eye loses its place in a repeating block. And decimal shifts: 4,832 typed for 48,320 is the single most common manual error, and it doesn’t announce itself, because the total dollar figure is unaffected.

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 on Windows can read PDFs natively, but the connector arrived with Microsoft 365 rather than with the perpetual releases. If you are on a standalone Excel 2016 or 2019 licence, Get Data > From File has no From PDF entry and no update will add one. Excel for Mac does not have it either, on any version. The PDF connector being Microsoft 365 and Windows only is the thing that surprises people mid-project.

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 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 infers table boundaries from ruling lines and whitespace alignment. It has no idea what a utility bill is.

On a three-page PG&E commercial statement you typically get eight to fourteen objects. Table001 is the remittance stub: genuinely tabular, completely useless. The electric charge detail on page 2 comes back split in two, because the “Delivery Charges” and “Generation Charges” sections are separated by a shaded band that Power Query reads as a table terminator. Gas detail on page 3 arrives with a different column count than either electric table, so appending them produces Column1 through Column7 with values under the wrong headers.

The deeper problem is that most utility bills do not lay charges out as tables at all. They use label-value ladders inside visual blocks: “Total Amount Due” in a box top-right, “Service Period” captioned under a meter icon, taxes as a right-aligned run of text. Power Query returns those as Page001 text objects, one row per line, split at arbitrary character positions. You can salvage it with Table.SplitColumn and delimiter logic, but you are now writing a parser in M, which is a bad language for writing parsers.

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.

It will not touch a scanned bill. On an image-only PDF, Pdf.Tables returns an empty result or a page object with zero rows. There is no OCR in the connector.

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’s table detection beats Power Query’s, because it uses layout analysis rather than ruling lines alone, so visual blocks survive closer to intact. Three things go wrong consistently.

Currency comes across as text. $1,067.14 lands in the cell as a string with the dollar sign baked in, and SUM returns 0. Fixing it means a find-and-replace on $ and a paste-special multiply by 1, per sheet.

Merged cells everywhere. Acrobat mirrors the visual layout, so a service-period caption spanning two columns becomes a merged cell, and merged cells break sorting and filtering.

Header and footer text becomes data rows. Page numbers, the customer service phone number, and “Continued on next page” arrive interleaved with your charges, showing up between every meter block on a multi-page bill.

Acrobat also writes one worksheet per page by default, so a 4-page bill becomes 4 sheets you have to stitch. For a handful of bills, fine. For a hundred, the cleanup exceeds the retyping time.

4. OCR for scanned bills

If pdffonts came back empty, you need OCR. Rasterize at 300 DPI. Below that, the small print in rate tables degrades badly, and plenty of bills arrive as 150 DPI scans.

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

Page segmentation mode matters more than anything else here. Tesseract’s default (--psm 3) does automatic column detection. On a utility bill, where the left side holds account information and the right side holds the amount-due box and neither is a true column, --psm 3 frequently interleaves them and you get an account number spliced into a dollar amount. --psm 4 (single column, variable size) and --psm 6 (uniform block) both order more predictably. Try all three on one representative bill before running a batch.

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. Every number is individually correct. The row is wrong. Nothing signals it.

The fix is positional output instead of plain text:

tesseract page-1.png out -c tessedit_create_tsv=1

That gives you left, top, width, height, and conf per word. Cluster by top to rebuild rows, then by left to assign columns. It works, and it is a real afternoon of code per bill layout, plus maintenance every time the utility reflows its template.

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 reads the bill the way a person does, so it does not care whether “Total Amount Due” sits in a table, a box, or a sentence. That is the real advantage. It absorbs layout variation across utilities without per-format rules, associates a meter number with the usage figure printed near it, and copes with scans that would otherwise need tuned OCR.

The failures are specific. Models drop rows from long rate tables: a 14-line time-of-use breakdown comes back with 11 lines and no error. They compute. Asked for a total, a model will sometimes return the sum of the line items instead of the printed figure, hiding exactly the discrepancy you needed to catch. Tightly repeating multi-meter layouts get collapsed into one meter. Blocks continuing across a page break get truncated at the boundary. And estimated-versus-actual read flags, which change how you should treat the usage figure entirely, are routinely ignored.

Two rules make it usable. Tell the model to transcribe only, never to calculate, and check the arithmetic yourself downstream. And require a page number alongside every extracted value, so a reviewer can verify a suspicious figure in three seconds instead of hunting. The ways AI extraction fails on utility bills are consistent enough to build checks for most of them.

Which fields to capture

Bill-level fields, recorded once and repeated on every row:

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, never 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 because a single statement routinely 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 every pivot has to be rebuilt whenever a site adds a meter.

One row per meter per service period gives you a table where every row is comparable to every other. Pivots work. Filters work. 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. Just never SUM a repeated column.

Worked example

A PG&E commercial statement, account 3812446075-4, service address 1420 Harrison St, Oakland CA. Statement date 03/12/2025, due 04/02/2025. Three service agreements on one bill.

Printed on the bill:

Total Amount Due: $11,763.20

The gas period is offset one day from electric. Normal, since meter readers do not hit every meter on the same day, and exactly the detail a one-row-per-bill layout destroys.

Converted:

Three rows, one statement. bill_total_due repeats and must never 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. Both periods above compute to 30. A span of 3 days or 71 days is a date typo, almost always a wrong month.

Usage and implied unit price

Two arithmetic checks catch nearly every decimal error.

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. Both sit in a plausible band for California commercial service in early 2025. A unit price of $19.48/kWh means the usage lost a digit. A rate of $0.0019 means it gained one.

Units of measure

Gas arrives as therms, CCF, MMBtu, or occasionally CF depending on the utility, and CCF-to-therms is not 1:1. It depends on the heating value factor printed on the bill, usually between 1.00 and 1.10. Electric is nearly always kWh, but very large accounts sometimes print MWh. Never convert without recording the original unit.

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.

Anything failing goes to a review queue with the source page attached. Anything passing all of them is usually fine.