Handling Multi-Record Flat File Bill Re-Imports in EnergyCAP

EnergyCAP provides two methods for importing billing data into the EnergyCAP database using a flat file format.

• Import from the EnergyCAP Work Flow Manager (preferred) or Account Manager using the Import Processor (File > Import)
• Import externally using an external task file (imptsk.exe)

This topic describes the import process from the EnergyCAP Work Flow Manager. For more detailed information on flat file preparation than is provided in this topic (including acceptable data formats, observation pairings, etc.), see the topic on bill import rules and options.

The Bill Import Process

The actual bill import is part of an import process that may vary from organization to organization, but will generally include all or most of the following steps:

1. Prepare import flat file
2. Create a bill batch
3. Import bills
4. Reimport bills if necessary
5. Verify imported bills
6. Audit bills
7. Edit/fix problems
8. Close bill batch
9. Approve bills
10. Export bills

Below, each of these steps are explained in more detail.

1. Prepare the import flat file. 
   
   Regardless of the bill import method, the same flat file must be prepared. EnergyCAP has rigid requirements for the acceptable flat file format. Each line of the file corresponds to a complete bill record.
   
   NOTE: For summary bills, the Bill Import Processor recognizes all records with the same account number, start date and end date as belonging to the same bill. 
   
   

REQUIRED file information/formats:

1. The import file must include a header line.
2. The import file must include the following as the first five columns for each bill record, in the order indicated:
   1. ACCOUNT—the alpha-numeric Account Code for the bill record
   2. STARTDATE (YYYYMMDD) —The billing period Start Date
   3. ENDDATE (YYYYMMDD) —The billing period End Date
   4. ACE—The bill type (A=Actual, C=Corrected, E=Estimated)
   5. METERCODE—the Meter Code of the meter associated with the bill.
   6. (OPTIONAL)VENDORCODE—the Vendor Code is not required, but if it is used, the VENDOR CODE must immediately follow the METERCODE.
3. The import file records MUST be sorted in the following sequence:
   1. 1. Ascending Account Code
   2. 2. Ascending Vendor Code (if included)
   3. 3. Ascending Start Date
   4. 4. Ascending End Date.

For additional information, see the topic on import rules and options.

1. Create a bill batch.
2. Import the flat file created in step 1 by clicking File>Import from the Work Flow Manager and selecting the import file using the Import File window functions. 
   
   

After the import, check for problem bills from the batch in the Bill Import Processor Problem folder. 



Bills appearing there should be edited and then re-imported (see next step).

1. If necessary, correct and reimport corrected bills from the Bill Import Processor Problem folder as follows:
   1. Navigate to the Work Flow Manager (Accounting > Work Flow Manager).
   2. Open the Bill Import Processor Problem Folder.
   3. Update/edit individual bill records as necessary by double-clicking the bill record line (cut/paste Windows functions do operate in the kickout).
   4. When corrections have been completed, click Tools > Processor > Bill Reimport. 
      
      
      
      The Bill Reimport window will open.
   5. Click Start. When the reimport is complete, the Log window will appear.
   6. Review the log and click the Close button(s).
2. Verify imported bills. As a user convenience, all imported bills are displayed in the Waiting to Verify folder. They can be audited and edited, or they can all be deleted at once and imported afresh. Verify the imported bills as follows:
   1. Set Work Flow Options (Options button from the Work Flow Manager title bar) to display only the imported bills for the relevant batch.
      
      
   2. Open the Waiting to Verify folder in the Work Flow Manager and check the control totals for the number of bills and the total dollar amount.
      
      
   3. Compare the totals with those from the original flat file. After control totals have been matched successfully, select the entire bill batch from the bill list in the Waiting to Verify folder. Then right-click and select the Verify option. The bills will be removed from the Waiting to Verify folder.
      
      NOTE: Verified imported bills will not be displayed in the Unapproved folder until the batch has been closed (See step 8).
3. Audit the imported bill batch from the green folder using your organization’s approved audit procedure.
4. Edit/fix any problems revealed in the audit through the customary bill edit process.
5. Close the bill batch. Imported bills will then be visible in the Unapproved folder in the Work Flow Manager.
6. Approve the bill batch using established organization procedure(s). 
7. If interfacing with A/P, export the approved bill batch.

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.

• Include data for multiple meters on the same account and bill AND/OR
• Include an account-level charge

The Bill Import Processor in EnergyCAP contains intelligence logic that enables processing of multiple records in a single bill. However, when there are import problems, EnergyCAP stores the problem records independently of one another in the Bill Import Processor Problem folder. This can cause problems with some accounting system workflows when the problem records are RE-IMPORTED because the Re-import Processor has no built-in logic to distinguish which of the independent records should be included in the re-imported bill. That is, what was one bill in the original import can become two or more bills in the re-import process. Future releases of EnergyCAP will enhance the logic of the Import Processor and the Re-import Processor. For the time being, the following work-around is recommended:

1. Before the initial import, mark the multi-record problem bills (bills with mis-matched dates, or a new account, or multiple meters of same commodity), perhaps by inserting a “KO” in front of the account code. The new account code will cause the bill records to be routed to the Bill Import Processor Problem folder in EnergyCAP.
2. From the Bill Import Processor Problem folder, identify the multi-record bill records (these will be bill records with the same account number, start date and end date).
3. Choose one of the records as the ‘master’ record for the bill.
4. Carefully copy the data row from the remaining kickout records with the same account number, start date and end date and paste it into the ‘master’ kickout record to recreate the structure of the original bill. Then delete the copied kickout record(s).
5. Then run the Re-Import Processor and the single re-import record will create a single bill. See below:

The example below presents two kickouts, each with a header row and a data row:

To join the records, copy the data row from Kickout #2 to the data row of the ‘master’ kickout record for the multi-record bill. Then delete Kickout #2. Repeat the process for any additional kickout records bearing the same account number and start/end dates. In the examples below, the data was corrected in the kickout record (in this case, the account code ‘test2’ was changed to ‘test1’ which is an existing account):

After correcting the kickout records, re-import bills using the Re-Import Processor: