Bill import configuration

Use Bill Import Profiles to import utility bill data. After mapping the columns in your file, you can reuse the same mapping for future imports.

You can also use the advanced file format, which follows slightly different rules.

No matter which method you choose, it's best to test your import with a small number of bills in a new batch. This makes it easy to delete the test batch and bills if needed.

Steps to create a bill import profile

Before you create the bill import profile, review your bill import file. What columns do you want to import?



  1. In the Bills module, go to Bill Imports and then Import Bills
  2. Click Open bill import formats.
  3. Click the plus icon to Add a bill import profile.
  4. Enter a descriptive name so you'll know when to use this profile.
  5. Choose the date format used in the bill import file.
  6. Map the required header information and add additional header information.
  7. Map the bill line types from your file.
  8. Click Save.

Minimum required columns 

To import billing data your file must include:

  • Account number
  • Start date
  • End date

Commodity code

  1. Click Settings (gear icon), then go to Commodities and Units.
  2. Hover over the commodity name to see its code in parentheses.

Recommended (optional) bill header information

Field Example Value
A/C/E A
V/P/R V
Meter code meter123

Example file

AccountNumber

StartDate

EndDate

ACE

VPR

 Meter code

USE

COST

123

20180201

201080228

A

V

 meter123

2534

254.24

 

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

The A/C/E and V/P/R flags help you import bills with the same start date, end date, and account code.
These two columns provide specific instructions to the import processor on how to process bills already existing in the database.

  • If A/C/E column is included in the file and the field has any character other than A, C, or E the line is invalid and the bill record is kicked out.
  • If the field is left blank A is the default.
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 (V/P/R)

  • 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 A/C/E and V/P/R 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, UtilityManagement 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

These examples show when a bill is created based on meterd code and commodity information.

Meter code 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 scenarious with !AUTO!  
!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 with !AUTO!  
!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!:electric

Account Code

Start Date

End Date

ACE

Commodity

123456-1

20181201

20181231

A

!AUTO!:ELECTRIC

If account 123456-1 has only one electric meter, the importer assigns charges to it.?
If the account has more than one electric meter, the import fails.