To ensure a successful import of utility bill data, the import process must be followed carefully. The Bill Import Wizard provides an interactive tool to guide the process. Additionally, data in the import file itself must conform to certain specifications. Each bill record must include certain types of data.
Each bill record corresponds to a row of data in the spreadsheet file. In addition to Cost, each bill record must include data for three required fields: Account Code, Start Date and End Date.
Given that there are some EnergyCAP processes that rely on date-specific logic, such as any per day analysis, auditing, and data presentation, it is beneficial that billing start and end dates are entered in a consistent manner. EnergyCAP is designed to have sequential bills start on the same day that a previous bill ends to ensure that “gaps” between bills do not distort energy and cost analysis. The bill import processor has been enhanced to identify bills in the import file where the begin date is one day after the end date of the previous bill. Once identified, the start date of the new bill is adjusted such that it corresponds to the prior bill end date and a note is created on the bill as a record of the change. Modifying the start date of the imported bill when there is only a one-day difference is the most efficient method for maintaining proper functionality of some date-specific logic in key EnergyCAP processes. This enhancement was brought about as a result of utility vendors that do not list the start date of a new bill as the end date of the prior bill.
The two columns provide specific instruction to the bill import processor regarding how bills should be processed especially when there is already an existing bill in the database for the same account, same vendor, same start and end dates (see the A/C/E and V/P/R Rules section).
The A/C/E field is set for a bill using a column that contains an A, C or E for each bill record.
The V/P/R field is set for a bill using one of the three valid characters, V, P, or R. If this field is left blank the bill import processor will use the default handling based on the A/C/E field.
What happens when a bill is imported that is already in the database? In other words, the vendor, account, start date and end date all match an existing bill. The existing bill could have been keyed or imported previously, or even imported moments ago as an earlier record in the same import file. The table below provides a list of the various field combinations and the resulting process logic applied.
|A/C/E||V/P/R||Logic applied to the Bill in the Import File||Logic applied to the Bill in EnergyCAP|
|Creates bill in EnergyCAP||Existing EnergCAP bill is Overwritten|
|A||Creates bill in EnergyCAP||Existing EnergyCAP bill is Overwritten|
|A||V||Creates bill in EnergyCAP||Existing EnergyCAP bill is Voided|
|A||P||Creates bill in EnergyCAP||Existing EnergyCAP bill is Preserved|
|A||R||Rejects bill during import process||Existing EnergyCAP bill is Preserved|
|C||Creates bill in EnergyCAP||Existing EnergyCAP bill is Preserved|
|C||V||Creates bill in EnergyCAP||Existing EnergyCAP bill is Voided|
|C||P||Creates bill in EnergyCAP||Existing EnergyCAP bill is Preserved|
|C||R||Rejects bill during import process||Existing EnergyCAP bill is Preserved|
|E||Creates estimated bill in EnergyCAP||Existing EnergyCAP bill is Overwritten|
|E||V||Creates estimated bill in EnergyCAP||Existing EnergyCAP bill is Voided|
|E||P||Creates estimated bill in EnergyCAP||Existing EnergyCAP bill is Preserved|
|E||R||Rejects bill during import process||Existing EnergyCAP bill is Preserved|
In EnergyCAP, a bill is associated with an account and a meter. While Account Code, Start Date, and End Date are required, the import wizard/processor does not required Meter Code, Commodity, or Serial Number. EnergyCAP can attempt to find the correct meter if the Meter Code is not specifically provided. Dependent upon the information provided for the given bill record, EnergyCAP uses the following logic to locate a meter connected to the specified Account Code:
Example 1: Specific Meter Code is provided.
Example 3: Only Commodity is provided. The Serial Number field is blank and the Meter Code is either set to AUTO or is blank.
Example 4: Meter Code and Serial Number are provided.
Example 5: Meter Serial Number is provided. Meter Code and Commodity are left blank (leaving the meter code blank is treated the same a AUTO).
also considered to be
Example 6: Commodity and Serial Number are provided. The Meter Code is left blank, which is treated the same a assigning AUTO to the Meter Code.
NOTE: Required fields for the bill import file are Account Code, Start Date and End Date. The remaining fields provide information to better define the meter to receive the line item data. In short, a Meter Code field that is left blank is the same as one that is filled with !AUTO!
If a bill contains a charge that should be associated with the account only (a late fee for instance), this can be specified during the mapping processes. To accomplish this, add two additional columns to the import file; one for the charge (late fee) and one to signal that this line of the bill should be charged to the account, not a meter on the account. In the "service charge" column, place an "X" on any line that should be a service charge. This will cause the importer to ignore any Meter Code or Commodity provided.
Service Charge Example: This bill consists of two import lines, which the importer identifies because the lines are consecutive and have the same Account Code, Start Date and End Date. The first line is for an electric meter, the second for an account-level charge. Even though the Meter Code is given on the second line, it will be ignored because the Service Charge column has an "X" in it.
|Account Code||Start Date||End Date||Meter Code||Use (kWh)||Cost||Late Fee||Service Charge|
Suggestion: When using a new profile, test a sample of the import file format with a small number of bill records.
The goals are:
To accomplish these goals, EnergyCAP uses a series of logical rules to determine the billing period month of each bill, regardless of whether it is manually keyed OR imported:
Second Logic Step Scenerio Scenario 1:
Second Logic Step Scenario 2:
The Bill Period Column can be used with bill import files to specify the bill period or to adjust the bill period logic applied to determine the billing period.
|Bill Period Column Value||Bill Period Assignment logic|
|MM:YYYY||Forces the bill import bill period logic to set a fixed billing period based on the month (MM) and year (YYYY) provided, such as 10:2016 would result in a bill period of October 2016|
|!AUTO!||The default logic for billing period assignment will be used|
|!START!||The bill will be assigned the billing period with the month and year of the bill start date|
|!MIDDLE!||The bill will be assigned the billing period with the month and year of the middle date between the bill start date and end date|
|!END!||The bill will be assigned the billing period with the month and year of the bill end date|
|If the Bill Period Column is provided in the import file but the field is left blank for a given record, the !AUTO! logic will be used|
NOTE: If the Billing Period column is NOT provided, the !AUTO! logic will be used.
This is a list of optional fields which can be used in the bill import process. These fields can provide additional information for a bill that may be useful or necessary information for specific data needs.