Bill import with advanced file format

Bill import is a process to import multiple bills into UtilityManagement using an Excel or .csv file.

Required format

The import file must include:

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

1. ACCOUNTCODE: 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: If blank A is used. 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! when 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 is left 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.

Required sort order

The import file records MUST be sorted in the following sequence. 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.

1. Ascending account code.!
2. Ascending vendor code (if included).
3. Ascending start date.
4. Ascending end date.
5. Ascending meter code. 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.

Bill start date adjustment

Meter code rules

• 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 because 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, an error (kickout) spreadsheet is created.
• 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 an error (kickout) spreadsheet is created with the message too many matching meters.
  • If the account has more than one meter on it of different commodities you can use !AUTO!:<COMMODITY> where COMMODITY is the commodity code of the meter. This lets you use the !AUTO! lookup feature for accounts with multiple meters of different commodity types. If the account has more than one meter of the commodity type in the !AUTO!:COMMODITY value, then the importer generates an error message indicating "Cannot determine meter code".

!AUTO!:<COMMODITY>

• If the meter code=!AUTO!:<COMMODITY> the application will try to match the bill import information to a meter by going through a series of steps.
  1. First the system checks to see if there is only one meter of that commodity linked to the account, if more than one meter continue.
  2. The system checks for a matching serial number within that commodity, if there is no match continue.
  3. Check to see if there is only one meter with a commodity in the same commodity category of the specified commodity, if there is more than one meter continue to the next step to try and find a match
  4. Check for a matching serial number within that commodity category. 
     
• If there is no matching serial number within the commodity category the bill fails to import and a error (kickout) is created.

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 available codes)

Billed Demand

• Billed Demand - BILLEDDEMAND

Billed Usage

• Billed Use - BILLEDUSE

Charge

• Charge - CHARGE
• Cost Adjustment Charge - COSTADJUSTCHARGE
• Fee - FEE
• Customer Charge - CUSTOMERCHARGE
• Total Pay Amount - TOTALPAYAMOUNT
• Info_Cost (Informational, not counted) - INFO_COST

Cost

• Total Cost - TOTALCOST
• Info Cost (information, not counted) - INFO_COST
• Prior Balance (Informational, not counted) - PRIORBALANCE

Demand

• Demand - DEMAND
• Mid-Peak Demand - MIDPEAKBILLDEM
• Off-Peak Demand - OFFPEAKDEM
• On-Peak Demand - ONPEAKDEM

Tax

• Tax - TAX
• State Tax - STATETAX
• City Tax - CITYTAX

Usage

• Use - USE
• Info_Use (Informational, not counted) - INFO_USE
• Off Peak Use - OFFPEAKUSE
• Mid Peak Use - MIDPEAKUSE
• On Peak Use - ONPEAKUSE

Use Reading

• Reading Use (Informational, not counted) - READINGUSE

Example column headers

This example import file produces the following bill. (If you are using the default label it does not need to be included.)

For example, consecutive columns USE:KWH and USE:USDOLLARS result in a single line on the bill.

Bill line item column header format

Line Item Type:Unit:Label

To show 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. 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 use/cost pairs, if used, both use and cost must have a value, even if 0.

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 includes a header record of three use/cost pairs:

• USE:KWH
• USE:USDOLLARS

• BILLEDDEMAND:KW
• BILLEDDEMAND:USDOLLARS

• USE:CCF
• USE:USDOLLARS

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 produces 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
• If using a use/cost pair, only add the label to the first item in the pair

EndOfRecord

A column *END_OF_RECORD is HIGHLY recommended as the last column for your import file. Use a value of X for the column.

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

Optional fields

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

Common import scenarios

Example file

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