Non-EDI Bill Import Rules and Options

Click here for an example Excel file with some common errors.

Sort Order:

Typically, each line in the file will be for an Account-Bill Start Date-Meter group. Sort Order must be:

1. Ascending Account Code
2. Ascending Vendor Code (if included)
3. Ascending Start Date
4. Ascending End Date.

Account Code

The Account Code cannot be NULL.

Estimated/Actual/Corrected Bill Flag:

• Estimated: If "E," any existing bills on the same account with the same dates will be deleted and the bill will be entered with the estimated flag set to "True."
• Actual: If "A," any existing bills on the same account with the same dates will be deleted.
• Corrected: If "C," the bill is treated as a correction and existing bills on the same account with the same dates are NOT deleted.

Any other value, or no value, is interpreted as if it were an "A."

Meter Code

• If the Meter Code is NULL, the values will be applied to the Account Code ONLY
• If the Meter Code is NULL, the bill line item is assumed to be MONEY since no usage information can be applied to the account
• If the Meter Code is NOT NULL and the Meter Code exists on that account, then the values are associated with that meter’s commodity
• If the Meter Code is NOT NULL and the Meter Code = !AUTO!, then the importer will automatically assign the imported values to the meter that is assigned to this account PROVIDED that there is a usage value and only one meter on the account. If there is more than one meter assigned to the account OR the meter code does not exist, the record will be skipped and a log entry will be created. If there is no usage, the cost value will be associated with the account and not the meter.

In the above example, the importer will look for the meter on account 123456-1.  If it has JUST ONE METER, then the importer will assign all charges in this row to that meter.  If it has more than one meter, then it will not import the row.

• 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 in EnergyCAP.  This allows you to 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 will generate an error message indicating "Cannot determine meter code."
  
  Example:

In the above example, the importer will look for the ELECTRIC meter on account 123456-1.  If it has JUST ONE METER, then the importer will assign all charges in this row to that meter.  If it has more than one ELECTRIC meter, then it will not import the row.

To view the commodities in EnergyCAP, click Tools > Unit System

The full list of available commodities can be see from the Commodities tab in the Unit System Editor window.

Bill Data ("OBSERVATION TYPE:UNIT:CAPTION" Pairs)

Typically, each line in the file will be for an Account-Bill Start Date-Meter group. Thus, the file should be sorted by ascending Account Code, ascending Vendor Code (if included), ascending Start Date, ascending End Date.

The headers for the first five columns described above (Account code, start date, end date, actual/estimated, meter code) are mandatory columns. Every column after meter code is optional and therefore the column heading is what defines the column contents. These column headings must be in ALL CAPS except for the CAPTION portion.

• Data must be grouped by account code, bill start date, and bill end date if it is to be treated as multiple body lines on the same bill.
• Separating body lines in the same file may result in previously entered bills being replaced.
• If there are multiple meters on the same account, all meters MUST appear on consecutive lines and all meters MUST have the same bill start and end dates.
• For Observation type pairs, BOTH need to have a value, even if it is 0. Neither can be NULL.

Example of Bodylines in a Bill

To achieve the above results, the import file should be formatted in the following manner (Note: the first five columns have been removed from this example):

Since each row represents charges for a specific meter on a specific account, there will be occurrences where the row of data will not have values for some columns (ex. You have water and electric on a particular account.  The OBSERVATION TYPE:UNIT:CAPTION for water will be different than the electric - and so you need to just put values in the columns corresponding to that row’s meter).

The "OBSERVATION TYPE:UNIT:CAPTION" pair can appear multiple times in each row.  It is valid to define multiple Value, Cost, or Value immediately followed by Cost fields in the file header. For example, a file containing data for accounts with Electric Use and Cost, Electric Demand and Cost, and Natural Gas Use and Cost would have a header record of: "USE:KWH", "USE:USDOLLARS", "BILLEDDEMAND:KW", "BILLEDDEMAND:USDOLLARS", "USE:CCF", "USE:USDOLLARS". The body lines for Electric Use/Cost and Demand/Cost are expected to be associated with an electric meter and the body lines for Natural Gas Use/Cost are expected to be associated with a natural gas meter. The Natural Gas Use/Cost fields should not be populated in the electric meter line and vice versa.

To obtain bills with both Value and Cost entries on a single body line, you must enter the value first and immediately follow it with the cost. For example, a body line with usage in KWH and a cost in dollars would need the columns to be defined as "USE:KWH", "USE:USDOLLARS". ! Beginning with Release 6.0, NO NULLS are permitted in any observation type pair (such as USE/USE)! If a NULL is present, the record will not be imported and an error will be generated. !

If two consecutive columns have the same OBSERVATION TYPE, and the second column has a UNIT of cost, a single line item is created on the bill using both the value and the cost. For example, consecutive columns of "USE:KWH", "USE:USDOLLARS" will result in a single line on the bill having KWH and Cost (in US Dollars).
To obtain bills with a Cost Only body line, use one of the NOUN observation types that are associated with MONEY (Charge, SubtotalCost, Tax, etc.). For example, "CHARGE:USDOLLARS".

Example: 
In this example, the consecutive columns share the same observation type of USE.  Since the first column reflects the UOM of KWH and the second column shows a UOM  of USDOLLARS (which is associated with MONEY).

This will produce a single line in the bill view.

To import bills with a Value Only body line, use any of the NOUN observation types that are NOT associated with MONEY. For example, "DEMAND:KW".

This screenshot shows bodylines (underlined in red) which are Value Only.

Optional Caption Feature

Normally, the caption (the text label shown on the left side of the bill entry screen for each line item on a bill) is set equal to the observation type display name. You can set your own captions by making a third entry in the heading. Example: "USE:KWH:ON PEAK TIER 1 USAGE" is the column heading to insert the desired bill bodyline heading. The caption cannot have a colon in it.  If you choose to use a caption in situations such as USE:KWH and USE:COST, you want to specify a caption JUST FOR THE FIRST ITEM IN THE PAIR, and not the second.  So in the example, this is the correct way to modify the caption and still end up with the information being represented in the bill as one line and not two:

Correct:      USE:KWH:Electric Usage     and    USE:USDOLLARS
Incorrect:    USE:KWH:Electric Usage    and    USE:USDOLLARS:Electric Usage

Pay Amount

The "PAYAMOUNT:USDOLLARS" column specifies the pay amount for the bill. In earlier versions of EnerygCAP, if this column was not included in the import file, the bill pay amount would be the sum of all costs of all bodylines on the bill subject to the rules indicated in the Unit System for each observation type specified.  If PayAmount is included, but the sum of the charges on the bill calculated to a different total, the bill was saved but with a + or - balance. This was not good.  Therefore, beginning with Release 6.0, the PayAmount column is simply ignored by the Importer. This assures that all imported bills will balance.

EndOfRecord

IMPORTANT:  It is HIGHLY recommended to add an additional column called ‘*EndOfRecord’, that will be ignored in the import, so that the last column of every row in the import file has a value in it.  The value for that column can be anything, but is generally set to the letter ‘X’.  Again, this data is NOT imported to EC, but is used to indicate to the import utility that there are no further values to be processed in the current row and to proceed with the next row in the import file.  Every row in the import file should have a value of ‘X’ in this column.

Example Header:

ACCOUNT, STARTDATE, ENDDATE, ACE, METER, USE:KWH, USE:COST, *END_OF_RECORD_MARKER

The * in the column header ‘*END_OF_RECORD_MARKER’ tells the importer to ignore the contents of this column.  It is then recommended to give every row in the import file a value of ‘X’ for this column.  In that way each row is terminated and will be ensured to process correctly.

Suggestions

WHEN IN DOUBT, be CONSERVATIVE and EXPLICIT and test a sample of the file format with a small number of records.

Any column that has a header which begins with an * will be ignored. This allows you to minimize the reformatting of non-comforming spreadsheets by simply ignoring non-conforming columns.  See Pay Amount section for another suggestion of how this can be used.

Optional Fields

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.

• BILLPERIOD (format MM:yyyy, example: 09:2005 for Sep 05). This forces a bill to be assigned to a particular billing period; otherwise, the billing period is determined by the month in which the mid-point of the bill falls. MM is the calendar month, not the fiscal month, in the event that they are different for your organization. Column header is BILLPERIOD.
• DUEDATE (format YYYYMMDD)
• STATEMENTDATE (format YYYYMMDD)
• CONTROLCODE (format alphanumeric) This is to allow the inclusion of bill image location where EnergyCAP is set up to look in this field for that information. Maximum number of characters is 255.
• ACCTPERIOD (format MM:yyyy, example: 09:2005 for Sep 05). The Accounting Period associated with the bill. MM is the calendar month, not the fiscal month, in the event that they are different for your organization. Maximum number of characters is 10.
• VENDORCODE (format alphanumeric) The Vendor Code associated with the bill. The Vendor Code is not required, but if it is used, the VENDOR CODE must immediately follow the METERCODE.
• INVOICENUMBER

Reimporting bills with the Reimport Processor

In the event that a file aborts partially through processing, the bills which have been successfully entered will remain in EnergyCAP Enterprise. As long as the file does not contain Corrected "C" records, it can be corrected and processed again. If the file does contain Corrected "C" records and it is processed again, additional bills for the "C" records will be created and manual steps will need to be taken to remove/prevent duplicate bills from causing difficulties.  As an alternative to this, rows that generated errors can be re-imported through the Bill Reimport Processor which is available from within Workflow Manager.

Example: The problems listed in the Bill Import Processor folder are eligible for re-import by using the Bill Reimport Processor.  Unless the issues indicated in the Message column are addressed, the bills will not import successfully.

To run the Bill Reimport Processor, navigate to the Account Manager in EnergyCAP (Accounting/Accounts)and click Tools/Processor/Bill Reimport.

The Bill Reimport window will open. Click the Start button to initiate the Reimport. When done click Close.

Bill Work Flow for Electronic Bills

After bills are imported into EnergyCAP, they are given a status of Unverified.  In order for a bill to be eligible for export, it must have a status of Verified.  To verify bills you have imported, go to Work Flow Manager and verify bills contained in the Waiting to Verify folder.

The import results are reported in an on-screen log. You can save or print this log in order to maintain a record.  Additionally, any issues logged will be viewable from within Work Flow Manager in the Bill Import Problem folder.

Flat File Bill Import Using imptsk.exe

Bill Import external to EnergyCAP using the imptsk.exe program

It is also possible to import bills from the command line. See also the topic on External Tasks. Here is an example and explanation of each element:

The usage is:

imptsk -d [dsn] -u [user] -p [pwd] -c [converter] -f [path] -x -a [path] -l [path] -s [section] -v [verbose messages] -remote -t [traceID]

Where:

• -d [dsn] is the ODBC data source name
• -u [user] is the system user id
• -p [pwd] is the system password
• -c [converter] is the name of the converter
• -f [path] is the file to be imported
• -x will delete the input file after importing the data
• -a [path] will archive the data to the path specified
• -l [path] is the log file
• -s [section] is the profile section name
• -v [message text] when used, will add additional detail to the import log file.
• -remote is the command to connect to the HTTP data provider (use in conjunction with the -d switch, which identifies the Datasource from the Catalog Server (as displayed in the EnergyCAP Login window)
• -t [traceID] is the SQL server trace to initiate during verification

Usage Example:

"c:\Program Files\EnergyCAP Enterprise\imptsk.exe" -d retailer -u kvs -p kvs -c STANDARD:HISTORICAL_BILL_IMPORT -f "c:\b-test\*.csv" -l "log.txt"

1. "c:\Program Files\EnergyCAP Enterprise\imptsk.exe" - This is the file path for the importer program, imptsk.exe. It is typically located in your Energy CAP Enterprise program folder. Be sure to enclose the file path in quotation marks. IMPORTANT - You must execute the command from the EnergyCAP program folder, i.e. log onto this folder before executing the imptsk.exe file. Simply pointing to the program folder from another location may cause the imptsk.exe program to be unable to run required supporting DLL files.
2. -d retailer - This is the name of the database into which you wish to import the data.
   
   NOTE: It is possible to use an ADO Connection Object (encapsulated in quotes) for the connection string. An example of the usage for the ADO Connection Object is:
   -d "Provider=SQLOLEDB;Password=MyesuserPassword;User ID=esuser;Initial Catalog=MyDatabaseName;Data Source=MyOwnComputer\SQLEXPRESS"
3. -u kvs - This is the User ID with which you wish to import the data. The User ID is the username you use to login to EnergyCAP Enterprise. Be sure adequate permissions exist to create the records.
4. -p kvs - This is the password associated with User ID you are using to import the data.
5. -c STANDARD:HISTORICAL_BILL_IMPORT - If you follow the import file specifications in this bulletin, this is the correct converter to use.
6. -f "c:\b-test\*.csv" - This is the file path for the .csv document which you wish to import into EnergyCAP Enterprise.  Note the asterisk here will include ANY csv file located in the c:\b-test folder.  You may specify a file name to avoid processing files which may share the same or similar filename.
7. -l "log.txt" - The Importer creates a log file that tracks the changes made to EnergyCAP Enterprise. This is the file path to the location where that file will be saved.