EC Pro: Electronic Bill Import
Posted by Matthew Heinz, Last modified by Joel Brickell on 14 July 2015 05:11 PM
Electronic Bill Import
You can import bills provided you are able to format an Excel file per these precise rules.
Use the provided Excel Bill Import template (located in the EnergyCAP program folder).
When you import, you will prompted to enter a 6-digit PIN code of your choosing. The PIN is used only in the Delete process. Upon review, if you find that you need to erase all imported bills, modify the format and re-import, the PIN is needed to enable the mass erase of an imported file.
Bill import rules:
1. Accounts and meters must be set up before importing. If not found in EnergyCAP, the bill record will kick out to LOG.
2. You cannot update account numbers or meter codes by this process. If the account or meter is not found, it will kick out to LOG.
3. Missing bill data will cause that row to kick out to C:\Documents and Settings\<user>\Local Settings\Application Data\EnergyCAP\KICKOUT.xls IMPORTANT NOTE: This KICKOUT.xls file can be conveniently edited and re-imported. Be sure to change the file name; otherwise it will be overwritten by subsequent import kickouts.
4. If you have two bills for the same month for an account, you will get two bill records in EnergyCAP. They will not be combined.
5. There is no checking of data for reasonableness or duplicates. You must verify data first. Running audits after importing is recommended.
6. The purpose of the Delete function is to allow you to delete all bills after import should you discover a formatting or data error that affects many bills. You can Delete all and then re-import.
7. The Details columns L - U: Leave the cells BLANK (i.e. null) when you do not want or need a detail line item to be created. When you enter any value in the cell, even a zero, a detail line item will be created.
8. The importer only allows numeric data (except coln H). You can never import alpha or special characters (no $ signs, for example).
9. DO NOT ENTER A COMMA in any cell. Commas will confuse the importer since this is a CSV (comma separated value) file!
10. Processing ends when the first record (Excel row) without an X (End of Record Indicator) in column Y is encountered.
11. In order to see the Details window in EnergyCAP (the blue line items in the table below, columns L-U), the meter must be on a rate template that has at least one detail selected. When the rate template has no detail tracking line items, the detail icon will not appear on the bill entry screen.