Knowledgebase: EC Enterprise - v6
EC Enterprise: Setup Sheet Creation through Custom Spreadsheet
Posted by - NA -, Last modified by Joel Brickell on 14 January 2013 03:15 PM
Setup Sheet View required for Custom Spreadsheet:

Setup Sheet Custom Spreadsheet .xml File:

We have created a Custom Spreadsheet that allows a user to export their current tree structure in EnergyCAP into our Setup Sheet format. This spreadsheet requires the installation of a database view as well as the .xml file for the Custom Spreadsheet.

Reasons you may want to do this are:
  1.  Move many account, meter and building records from one database to another. Use this custom spreadsheet to export the data to an import spreadsheet, then import into the other database.      You can use a similar process to export and then import billing data.
  2.  Set up deregulated accounts (one meter is shared by separate LDC and supplier accounts).  Use this custom spreadsheet to export the LDC account, meter and associated data to an import  spreadsheet, modify it by substituting supplier account number, vendor and template code, then import back into the database.  This allows you to quickly create supplier accounts that mirror existing LDC accounts.

The order for the columns is very critical. You can find the order listed in our help documentation here:

Base import sheet to paste into:

Info/EnergyCAP Data Field should be your first column and also how you sort the spreadsheet. This field displays the number of levels deep in the tree structure that the account and meter are. (Sort Ascending A - Z/ 1 - 3)
Meter Code
Meter Display
Commodity Code
Template Code
Meter Serial
Account Code
Account Display
Account Address Line 1
Account Address City
Account Address State
Account Address Postal Code
Cost Center Code
Cost Center Display
Parent Cost Center Code
Parent Cost Center Display
Parents Parent Cost Center Code
Parents Parent Cost Center Info
Vendor Code
Vendor Display
Place Code
Place Name
Place Address Line 1
Place City
Place State
Place Postal Code
Place Size
Place Size Begin Date (Must be reformatted to MMDDYYYY style)
Parent Place Code
Parent Place Display
Parents Parent Place Code
Parents Parent Place Info
PP Place Code
PP Place Name
GL Code
GL Subcode 1 - 20 (In numerical order, 1, 2, 3, etc...)
Account Note (Is not tied to the account notes in EnergyCAP)
Rate Code

To import this custom spreadsheet follow the instructions in our help manual here for the .xml in this article. This custom spreadsheet will be found in the Account and Meter Lists Section when imported successfully.

Once you create your custom spreadsheet in this output order you will need to add at least one field to your filters. (Even if you want everything in the database a single filter is required.)

For help on designing your custom spreadsheet please reference our help manual here. When following these steps you will start with Account and Meter Lists Section, then choose Account and Meter Setup in the design wizard.
After you export the import into the Excel file you will need to reformat the Place Size Date information to be in the MMDDYYYY format as well as adding the X for the *EndOfRecord field.

Updated 08/31/2011
(93 vote(s))
Not helpful

Comments (0)
Post a new comment
Full Name: