Import Menu

Bill Payment Data

This option imports bill payment data (such as check number and date paid) in order to update EnergyCAP from your accounting system.

Required import file format is CSV, seven fields. Columns 1-4 are required; null is acceptable for column 5. If building the import file from MS Excel, a delimiter must be included in column 6 (space is OK). In column 7, data can be null and will be ignored.  The Billid is the key lookup field. A log file identifies any records that cannot be processed due to (1) BillID not found or (2) improper data format.

Log file:

The Log file lists the bad records and then in field 7 shows the error message, so user can fix bad records and re-import the log file. If anything exists in field #7, it gets overwritten with the error message when the log record is created. Example:

Original record: 12345,9876,20050128,paid,20050130,200501,optional comment

Format:                      a,       b,              c,     d,              e,          f,              g

Record written to log file: 12345,9876,20050128,paid,20050130,200501,Bill ID not found.

Possible error conditions are: Bill ID not found, invalid date format, invalid record format (not 7 data elements found before CR/LF), invalid accounting period format.

1. BillID
2. Check number (text, 32)
3. Check date, format yyyymmdd (datetime, 8)
4. Pay status (text, 10)
5. Cleared date, format yyyymmdd (datetime, 8) (may be null)
6. Accounting period, yyyypp (pp=period number, 01-13, see db table AccountPeriod); accounting period is optional and may be null. Use Tools – Work Flow Wizard to activate Accounting period/year fields on bill entry screen.
7. Comment field, ignored by import (commas not allowed)

Bill Image Data

Instructions for importing bill image records into the BillImage table:

Required import file format is CSV, three fields.

1. Control Code
2. Image filename
3. Comment field

How it works:

The Administrator importer looks for the Control Code field in the Bill table and uses that field to match the bill record with the specified bill image.

CAUTION: The Importer does not force the Control Code to be unique, so if the import file contains duplicate control codes for different bills, one bill image could be tagged to more than one bill record. The log file rules described in the previous section on Bill Payment Data also apply to the bill image import.

Possible error conditions are:

• Control Code not found in Bill table,
• Image filename format not allowed,
• Invalid record format (not 3 data elements found before CR/LF)

Budget Import

Budgets are imported through Administrator, a stand-alone executable file that allows a number of functions to be performed on your EnergyCAP database that are not available from within EnergyCAP itself.  For example,  Administrator imports budget data from an external budget creation process (usually Excel).

Prior to importing budget data, a new MASTER budget for the desired year must be created in EnergyCAP, for example, "FY2009."

1. Click Budget > New > Budget.
   
   The Budget Wizard window opens.
   
2. Complete the Budget Wizard forms as desired and click Generate.
3. Create and save the external budget file, using the current budget import template.
• Budget data must be meter-by-meter, month-by-month.
• Required import file format is CSV with 38 fields. Make certain to save the file to csv. format before importing it. The first column is for the Meter Code; then Account Code; then a budgeted cost, use (expressed in COMMON  UNITS for the commodity being tracked) and rate (unit cost) for each of the 12 months. The first month must correspond to the first month of the fiscal year set up in EnergyCAP (usually January or July).
• Budget values must be formatted WITHOUT dollar signs and commas.
• The meter code is the EnergyCAP Meter Code value.
4. Import the new MASTER budget:
1. Open Administrator.
2. Click Import > Budget Import.
3. Select the Budget Code (the name of the Master budget you created) and Version fields, and click Import. The dropdown fields will be populated with however many versions are available.
4. Select the file and click Open. The budget data will be imported into EnergyCAP. During Import, the Administrator takes the information from the CSV file and replaces the values within EnergyCAP for the selected 12-month budget. Imported budget values for each budget month are stored in the BinData table (value,uValue and ucValue fields).
5. Modify the budget as desired.

Budget modifications are made through EnergyCAP Budget and Forecast Manager (Analysis > Budgets). For more information on budgets, see the Budget and Forecast Manager topic.