Bill import configuration

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 your 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

201080228

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.

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.

Actual

  1. A indicates an actual bill.
  2. 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.

Corrected

  1. C indicates a corrected bill.
  2. Any existing bills in the database with the same account and dates are NOT deleted. This could result in duplicate bills (the original bill and the corrected bill).

Estimated

  1. E indicates an estimated bill.
  2. 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.

Void

V indicates the existing bill should be marked as Void and the new bill is created.

Preserve

P indicates any existing bill should be preserved, NOT deleted, and not voided and the new bill is created.

Reject

R indicates any existing bill should be 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

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.