Posting online payment files to utility billing automatically

Residents pay utility bills online, by phone and by autopay through a payment vendor. Each morning, that vendor delivers a file of yesterday's payments. Someone in utility billing downloads it, opens it, checks it and imports it. On a good day, that takes a few minutes. On a bad day, one unknown account number holds up the whole import.

Automating the routine part leaves staff with only the exceptions.

How the daily file usually works

Payment vendors that serve utilities, such as Paymentus and InvoiceCloud, commonly deliver a daily payment (remittance) file to an SFTP folder. The exact layout depends on your agreement with the vendor, but most files include:

• The utility account number.
• The amount paid, and any fee.
• The payment date and method.
• A confirmation or reference number.
• A status, such as accepted or returned.

Ask your vendor for the file layout document. It is the single most useful thing to have before you start.

The flow, step by step

• On a schedule, weekday mornings. After the vendor's file usually arrives.
• Read the file. New files from the SFTP folder, parsed by their layout.
• Keep accepted payments. Returned or failed items are handled separately.
• Check the account exists. Look up each account number in the billing system.
• Post payments. Good payments are posted by API, or written as the billing system's import file.
• Route unknown accounts. Payments for accounts that don't exist go to billing staff by email.
• Archive the file. So it isn't read twice.

The account check matters most

The most common problem is a payment for an account that doesn't match: a mistyped number, a closed account, or a new account not yet in the system. Without a check, the whole import may fail, or the payment may post to the wrong place.

Checking each account before posting means good payments go through and only the problem ones wait for a person.

File formats you may meet

Fixed-width files need a layout: which characters hold which field. Get it from the vendor and check it against a real file, not just the document.

Don't post the same file twice

Duplicates are the worst outcome, because each one has to be found and reversed. Guard against them in more than one way:

• Archive or move each file after it is read.
• Keep track of which files have been processed.
• Use the vendor's reference number to detect payments already posted, if the billing system allows it.

Balance it every day

Posting payments is half the job. The other half is confirming the totals:

• The file's own total should equal the sum of its rows.
• Posted payments plus exceptions should equal the file total.
• The day's posted total should match the cash receipts entry in the ledger.

Send a short summary to billing each morning with the counts and totals. If nothing went wrong, the email still confirms the job ran.

What staff still do

Automation handles the routine. People still handle:

• Payments for unknown or closed accounts.
• Returned payments and their follow-up.
• Anything the vendor flags as unusual.

That is usually a handful of items a day, instead of the whole file.

Before you go live

• Run the flow against a test copy of the billing system, or in preview, with real files.
• Compare its results with a manual import for several days.
• Agree who receives the exception email, and who covers when they are out.

In SentraBridge

SentraBridge reads payment files over SFTP with host keys pinned, including CSV, fixed-width, NACHA and BAI2 layouts, and a ready template posts Paymentus payments to utility billing with an account check. The Paymentus and InvoiceCloud connectors are built from public material and confirmed with your account during setup. Payment records pass through in memory and aren't stored. See utility billing.