Bill import configuration

Last updated October 16, 2026

You can import utility bill data with Bill Import Formats. After you map the columns in your file, you can reuse the mapping each time you import your file.

The advanced file format is another option to import bills and it has slightly different rules.

A good practice is to test your file import using a small number of bill records and a new batch each time. This lets you easily delete the test batch and bills.

Steps to create a bill import format

  • Review your bill import file. What columns do you want to import?
  • Create a bill import format. This format maps the columns from your file to create bills.

In the Bills module, go to Bill Imports and then Import Bills.

  1. Click Open bill import formats.
  2. Click the plus icon to Add a bill import format.
  3. Give this format a descriptive name to help you know when to use it.
  4. Choose the date format used in your bill import file.
  5. Map the required header information and add additional header information.
  6. Map the bill line types from your file.
  7. Save.

Minimum required columns for a bill import file

To import billing data the following columns must be included:

  • Account number
  • Start date
  • End date

Commodity code

To see the commodity code, click on Settings and Commodities and Units. Hover over the commodity name to see the commodity code.

Recommended (optional) bill header information 

  • A/C/E
  • V/P/R
  • Meter code

AccountNumber

StartDate

EndDate

ACE

VPR

 Meter code

USE

COST

123

20180201

20180228

A

V

 meter123

2534

254.24

 

Column header

Information

BILLPERIOD

MM:YYYY

Forces the billing period assignment.  See Determine Billing Period for more information.

If blank uses UtilityManagement logic.

DUEDATE

YYYYMMDD

STATEMENTDATE

YYYYMMDD

CONTROLCODE

Used to associate a bill image with a bill when UtilityManagement is configured for this option.

ACCTPERIOD

MM:YYYY

Enter the accounting period number, not the accounting period name.

VENDORCODE

Not required, but if used it must immediately follow METERCODE (Advanced File only). If duplicate account codes with different vendor codes exist, vendor code must be included.

INVOICENUMBER

The invoice number for the bill.

METERSERIAL

Serial number of the meter associated with the bill. A mismatch between the import sheet and UtilityManagement does not prevent a successful import. A bill audit can be configured to flag bills with a mismatch.

RATECODE

A mismatch between the value in the import sheet and UtilityManagement does not prevent a successful import. A bill audit can be configured to flag bills with a mismatch.

BILLINGPERIOD

!AUTO!, !START!, !MIDDLE!, !END!  Determining Billing Period provides more details.

BILLNOTE

Import notes associated with the bill. Remember you are importing a CSV file and your comment cannot include a comma.

A/C/E (Actual, Corrected, Estimated) flags

The A/C/E flag helps the system decide how to handle bills with the same start date, end date, and account code when importing bills to an open batch. (All bills in a pending batch are marked as void until the batch is closed.) It also works with the V/P/R flag to manage duplicate or overlapping records.

You can use these flags with a bill import format or with the advanced file format.

How it works

The A/C/E column tells the import processor whether a bill is:

  • A = Actual
  • C = Corrected
  • E = Estimated

Import rules

  • If the A/C/E field contains anything other than A, C, or E, the line is invalid and the bill record is kicked out.
  • If the field is blank, the system defaults it to A (Actual).
  • Audits cannot flag corrected (C) bills.
  • Audits can flag a bill if there are too many consecutive estimated (E) bills on an account.
Value Description
A
  • Actual bill. 
  • Any existing bills in the database with the same account and dates are deleted.
  • This imported bill is assigned a new bill id (not the same id as the one it is replacing), this may impact bill export and cause the bill to be exported again.
C
  • Corrected bill.
  • Add a new bill.
  • Does not delete old one.
  • This could result in duplicate bills (the original bill and the corrected bill).
E
  • Estimated bill. 
  • Any existing bills in the database with the same account and dates are deleted.
  • This imported bill is assigned a new bill id (not the same id as the one it is replacing), this may impact bill export and cause the bill to be exported again.

Void/Preserve/Reject (VPR)

How it works

This column tells the import processor what to do:

  • V = Void the existing bill, new bill is created
  • P = New bill is created, existing bill is preserved
  • R = New bill is NOT created (rejected), existing bill is preserved

Import rules

  • If the V/P/R column is blank the bills are imported using the A/C/E logic.
  • If the column has any character other than V, P or R the line is invalid and the bill record is kicked out.
Value  Description
V Existing bill is voided. New bill is created.
P Existing bill is preserved, NOT deleted, and not voided. The new bill is created.
R Existing bill is preserved and a new bill is NOT created. A kickout is produced for the bill being imported.

How ACE and VPR work together

A/C/E

V/P/R

Logic applied
to bill in the import file

Logic applied
to bill in database

 

 

Create the new bill in database

Existing bill is deleted

A

 

Create the new bill in database

Existing bill is deleted

A

V

Create the new bill in database

Existing bill is voided

A

P

Create the new bill in database

Existing bill is preserved (possibly 2 overlapping bills)

A

R

Reject the new bill

Existing bill is preserved

C

 

Create the new bill in database

Existing bill is preserved  (possibly 2 overlapping bills)

C

V

Create the new bill in database

Existing bill is voided

C

P

Create the new bill in database

Existing bill is preserved (possibly 2 overlapping bills)

C

R

Reject the new bill

Existing bill is preserved

E

 

Create the new estimated bill in the database

Existing bill is deleted

E

V

Create  the new estimated bill in the database

Existing bill is voided

E

P

Create the new estimated bill in the database

Existing bill is preserved  (possibly 2 overlapping bills)

E

R

Reject the new estimated bill

Existing bill is preserved

Sort order for import file

To ensure no gaps in your billing data, EnergyCAP is designed to have bills start on the same day as the previous bill ends.
As discussed in Bill Start and End Dates, some utility vendors do not follow this logic. An enhancement has been made to the bill import processor to identify bills with this one day gap (the begin date is one day after the end date).

The importer adjusts the start date of the bill to the end date of the prior bill.

The import file MUST be sorted in the following sequence.

  1. Ascending account code.
  2. Ascending vendor code (if included).
  3. Ascending start date.
  4. Ascending end date.
  5. Ascending meter code (if included).

How to create account charges

To create account charges the Meter Code and Commodity columns must be empty.

excel fileview of a bill

Bill import scenarios

Description Bill Created?
Correct meter code, no serial number Yes
Correct meter code with single matching serial number Yes
Correct meter code with incorrect serial number Yes, because meter code is correct.
Incorrect meter code with single serial number match. Yes, because a single serial number match.
Incorrect meter code with serial number matching multiple meters No, too many matching meters.
Commodity  
!AUTO!:  Only one meter of commodity on the account Yes
!AUTO!:  More than one meter of commodity on account and no serial number in the import file No, too many matching meters.
!AUTO!:  No meter exists for the commodity in import file. Import file contains serial number for a meter of a different commodity. No, unable to find meter with commodity.
!AUTO!:  More than one meter of commodity on account. Correct serial number provided for the meter. Yes
!AUTO!:  More than one meter of commodity on the account. Serial number matches multiple meters on the account No, too many matching meters.
Meter Code  
!AUTO!:  Single meter with no serial number provided Yes
!AUTO!:  Multiple meters on account, no serial numbers in import file No, too many matching meters.
!AUTO!:  Single meter on account, incorrect serial number Yes, only one meter on account. Bill message is added stating serial number mismatch.
!AUTO!: Multiple meter on account, nonmatching serial numbers in import file. No, too many matching meters.
!AUTO!:  Multiple meters on the account, multiple matching serial numbers. No, too many matching meters.
!AUTO!: Multiple meters on account, single matching serial number. Rate code in import file does not match. Yes. Bill message added stating the rate schedule in file does not match.
   

Example import with !AUTO!: commodity

Account Code

Start Date

End Date

ACE

Commodity

123456-1

20181201

20181231

A

!AUTO!:ELECTRIC

In this example, the importer looks for the electric meter on account 123456-1. If only one meter exists the importer assigns all charges to the meter. If the account has more than one electric meter the import fails.