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.

Required format

The import file must include:

• A header row.
• The columns in the CORRECT order.

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

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).

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.

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

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.

Common import scenarios

Example file

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