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
- Required format
- Bill start date adjustment
- Required sort order
- Actual/Corrected/Estimated (A/C/E)
- Void/Preserve/Reject (V/P/R)
- How A/C/E and V/P/R work together
- Meter code
- Most often used bill line types
- Bill data TYPE:UNIT:LABEL column headers
- Common import scenarios
- Advanced file format common import scenarios
- Example file
Bill import basic information
- 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.
- Only the first sheet of an Excel workbook is imported.
- 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)
- 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.
- If a .csv file has a row with all commas, the import processor ignores the row.
- 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:
- A header row.
- The columns in the CORRECT order.
- ACCOUNT CODE: the alpha-numeric account code for the bill record, cannot be NULL
- STARTDATE (YYYYMMDD): The billing period start date
- ENDDATE (YYYYMMDD): The billing period end date
- 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.
- 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.
- VENDORCODE: Optional. If duplicate account codes with different vendor codes exist, vendor code must be included.
- VPR: Optional. Recommended to help manage how existing bills in the database are handled during bill import.
Bill start date adjustment
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.
- Ascending account code.
- Ascending vendor code (if included).
- Ascending start date.
- Ascending end date.
- 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
- A is the default code and indicates an actual bill.
- If blank is imported as Actual.
- 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
- C indicates a corrected bill.
- Any existing bills in the database with the same account and dates are NOT deleted
Estimated
- E indicates an estimated bill.
- Bill flag set to estimated.
- 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 |
Logic applied |
|
|
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
- If the meter code is NULL, the values are applied to the account ONLY.
- If the meter code is NULL, MONEY is assumed for the bill line item since no use information can be applied to an account.
- If the meter code exists for the account, the billing information is imported for the meter.
- If the meter code doesn't exist, a kickout is produced.
- If the meter code=!AUTO!
- If USE and only ONE meter exist for the account, the values are assigned to the meter.
- If more than one meter is assigned to the account a kickout is produced with the message too many matching meters.
- If no use, the cost is assigned to the account and not the meter.
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 Demand
Billed Usage
- Billed Use
Charge
- Charge
- Cost Adjustment Charge
- Fee
- Customer Charge
- Total Pay Amount
- Info_Cost (Informational, not counted)
Cost
- Total Cost
- Info Cost (information, not counted)
- Prior Balance (Informational, not counted)
Demand
- Demand
- Mid-Peak Demand
- Off-Peak Demand
- On-Peak Demand
Tax
- Tax
- State Tax
- City Tax
- Sales Tax
Usage
- Use
- Info_Use (Informational, not counted)
- Off Peak Use
- Mid Peak Use
- On Peak Use
Use Reading
- Reading Use (Informational, not counted)
Bill data TYPE:UNIT:LABEL column headers
After the mandatory columns, optional columns can be added with the column header defining the column contents.
- Data must be grouped by account code, bill start date, and bill end date to have multiple body lines on the same bill.
- If the file is not sorted properly the import produces inconsistent results.
- If multiple meters are on the same account, all meters MUST appear on consecutive lines and all meters MUST have the same bill start and end dates.
- To display use and cost on a single line on the bill (use/cost pair), you must enter the use column first and immediately follow with the cost. For use/cost pairs, if used, both use and cost must have a value, even if 0.
- If two consecutive columns have the same TYPE, and the second column is a cost UNIT, a single line item is created on the bill using both the use and cost.
- For example, consecutive columns USE:KWH and USE:DOLLARS result in a single line on the bill.
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: |
USE: |
DEMAND: |
ONPKDEMAND: |
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
USE: |
USE: |
BILLEDDEMAND: |
BILLEDDEMAND: |
USE: |
USE: |
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:
- USE:KWH:ON-PEAK TIER 1
Optional label rules
- Label cannot have a colon
- Only add the label to the first item in the pair
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 Level–No meter code in import file, matching serial number. |
Yes |
|
Bill with account level charge is created. |
Account Level–No meter code in import file, no serial number. |
Yes |
|
Bill with account level charge is created. |
Account Level–No meter code in import file, usage value included. |
No |
Unable to find meter. |
|
Example file
This bill is from the first line of the file above.