Bill import with advanced file format

Bill import is a process to import multiple bills into EnergyCAP using an Excel or .csv file. This topic covers the features of the advanced file format.

Bill import basic information

  1. Use a .csv or .xlsx file. To upload multiple .csv and/or .xlsx files at one time they can be combined into one zip file.
  2. Only the first sheet of an Excel workbook is imported.
  3. Each row corresponds to one account/meter relationship. If you receive one bill for two meters (Electric and Natural Gas), you need two separate consecutive rows.

    The importer assumes rows for the same bill if all these items match in consecutive rows:
  • Account code
  • Start date
  • End date
  • Vendor code (if it exists)
  1. Each bill line item is represented in a column. Therefore, use for electric meters and use for natural gas meters exist in two separate columns.
  2. If a .csv file has a row with all commas, the import processor ignores the row.
  3. An asterisk (*) as the first symbol in the column header causes the import processor to skip and not import the column.

Meter codes, serial number and commodity rules

While account code, start date, and end date are required, the import processor does not require meter code, serial number or commodity. The processor attempts to find the correct meter with the information provided. More details are provided below.

Account

  • Must be active.
  • Must include the vendor code, if duplicate account numbers exist for different vendors.

Bill start and end date

  • Must fall between the account service begin and end dates.
  • Must be the same format for the entire file.

Account Alert messages are honored. Alerts can be configured to prevent bill entry:

  • If a bill has an active alert, the message is added to the bill.
  • If the account alert is set to a failed status, the bill is not imported.

Required format

The import file must include:

  1. ACCOUNT CODE: the alpha-numeric account code for the bill record, cannot be NULL
  2. STARTDATE (YYYYMMDD): The billing period start date
  3. ENDDATE (YYYYMMDD): The billing period end date
  4. ACE: Optional. If not used A is assumed. The represents the bill type (A=Actual, C=Corrected, E=Estimated). Recommended to use C, if you may have overlapping history and to preserve any existing bills in the database.
  5. METERCODE: The metercode column is required, but can be populated with !AUTO! if  only one meter per commodity exists or if you add a column for serial number and include the meter's serial number.  If entering an account level charge this column should be blank.
  6. VENDORCODE: Optional. If duplicate account codes with different vendor codes exist, vendor code must be included.
  7. VPR: Optional. Recommended to help manage how existing bills in the database are handled during bill import.

Bill start date adjustment

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 processer 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 and adds a bill note.

Required sort order

The import file records 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).

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

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

If the field is not blank and has any character other than A, C, or E the line is invalid and the bill record is kicked out into an error file.

Actual

  1. A is the default code and indicates an actual bill.
  2. If blank is imported as Actual.
  3. 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

Estimated

  1. E indicates an estimated bill.
  2. Bill flag set to estimated.
  3. 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 this column doesn't exist or is blank the bills are imported using the A/C/E logic.

If the column is not blank and has any character other than V, P or R the line is invalid and the bill record is kicked out into an error file.

Void

V indicates the existing bill should be marked as Void.

Preserve

P indicates any existing bill should be preserved and NOT deleted.

Reject

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

Meter code

Most often used bill line types

The following are a list of the most often used Bill Line Types grouped by category. 

(Entire list of codes)

Billed Demand

Billed Usage

Charge

Cost

Demand

Tax

Usage

Use Reading

Bill data TYPE:UNIT:LABEL column headers

After the mandatory columns, optional columns can be added with the column header defining the column contents.

bill line items

The corresponding import file is shown below (the first five columns are hidden). If you are using the default label it does not need to be included.

USE:
KWH:Usage

USE:
USDOLLARS

DEMAND:
KW:Demand

ONPKDEMAND:
KW:On Peak Demand

18000

100.00

30

0

The TYPE:UNIT:LABEL column headers can appear multiple times in the header row.

Each row represents charges for a specific meter, some rows of data do not have values for some columns. The TYPE:UNIT:LABEL for water is different from electric, therefore some columns are empty.

For example, a file containing data for accounts with Electric Use and Cost, Electric Demand and Cost, and Natural Gas Use and Cost would include a header record of three use/cost pairs:

USE:
KWH

USE:
DOLLARS

BILLEDDEMAND:
KW

BILLEDDEMAND:
USDOLLARS

USE:
CCF

USE:
DOLLARS

1800

100.00

30

25.00

 

 

 

 

 

 

100

15.00

The body lines for Electric Use/Cost and Demand/Cost are typically associated with an electric meter and the body lines for Natural Gas Use/Cost are typically associated with a natural gas meter. The Natural Gas Use/Cost columns should not be populated in the electric meter row.

NULLS are not permitted

Using the example above, each half of the use/cost pair must exist in the spreadsheet and must be populated.

For example, the following would produce an error:

Optional label

Normally the label is equal to the TYPE.

To create your own label use a third entry in the heading. For example:

Optional label rules

EndOfRecord

It is HIGHLY recommended to include a column *END_OF_RECORD as the last column for your import file with the value X.

Begin the column header with an asterisk (*) to skip the import of the column.

Optional fields

Optional fields can be used in the import file.

 

 

BILLPERIOD

MM:YYYY

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

If blank uses EnergyCAP logic.

DUEDATE

YYYYMMDD

STATEMENTDATE

YYYYMMDD

CONTROLCODE

Used to associate a bill image with a bill when EnergyCAP 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

INVOICENUMBER

The invoice number for the bill.

METERSERIAL

Serial number of the meter associated with the bill. A mismatch between the import sheet and EnergyCAP does not prevent a successful import. A bill message is created for the bill. (A bill message is the result of a bill audit and visible in version 3.)

RATECODE

A mismatch between the value in the import sheet and EnergyCAP does not prevent a successful import. A bill message is created for the bill.

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.

Common import scenarios

Description Billing Period Assigned
A bill for March exists in the application.  
Billing period column set to !AUTO!, start date is 4/15/2018 and end date is 5/20/2018. (middle day is 5/2) 201804
   
A bill for March exists in the application.  
Billing period column set to !AUTO!, start date is 4/15/2018 and end date is 5/21/2018 (middle day is 5/2) 201804
   
A bill for March exists in the application.  
Billing period column set to !AUTO!, start date set to 4/30/2018 and end date is 5/5/2018 201804
   

A bill for April exists in the application.

 
Billing period column set to !AUTO!, start date set to 4/16/2018 and end date set to 5/16/2018 (middle day is 4/30) 201805, logic creates a May bill as an April bill exists
   

A bill for April exists in the application.

 
Billing period column set to !AUTO!, start date set to 4/26/2018 and end date set to 5/1/2018 (middle day is 4/28) 201804, logic does not move the bill to May because the bill has no days in May
   

A bill for April exists in the application.

201804
Billing period column set to !AUTO!, start date set to 4/26/2018 and end date is set to 5/2/2018 (middle day is 4/28) 201805, bill has a May values
   
Account level - No meter code in import file, matching serial number. Bill with account level charge is created.
Account level - No meter code or serial number in import file. Bill with account level charge is created.
Account level - No meter code in import file, use value included. No bill created, unable to find meter.

 

Advanced file format common import scenarios

Description

Bill Created?

Error Message

Result

Account LevelNo meter code in import file, matching serial number.

Yes

 

Bill with account level charge is created.

Account LevelNo meter code in import file, no serial number.

Yes

 

Bill with account level charge is created.

Account LevelNo meter code in import file, usage value included.

No

Unable to find meter.

 

Example file

excel file screen shot

This bill is from the first line of the file above.

bill lines