Excel Cheats for EnergyCAP Setup (Import) and Update Sheets

Today’s blog is specifically for EnergyCAP clients who are either in the implementation process or who are looking for better ways to make large-scale organizational changes to their EnergyCAP database.

We want to show you how to do things better—to “cheat” what could be a lengthy or tedious setup process by using Excel to import setup data or organization updates more quickly. And a big thank-you to EnergyCAP Senior Project Manager SJ Bergman for explaining these cheats to me!

Long-time EnergyCAP users know that it’s pretty convenient to move objects around in EnergyCAP using our drag-and-drop features right from the user interface. But that process can be time-consuming if you’ve got a large organization and you need to make a lot of changes.

A few years back, we came up with a way to do this by generating setup and update spreadsheets for each of our major categories of database objects, including accounts, meters, places, and vendors. Using database information, we figured out a way to generate a specially-formatted Excel spreadsheet file with “rules” that would help clients import their data into EnergyCAP from a without a lot of fuss, and with confidence that their new data met specifications/ranges appropriate for each data type.

Here’s how it works.

Import or Update?

First of all, you need to know that there are separate setup and update sheets in EnergyCAP. You can access them both from the Setup Manager under Administration.

Setup (Import) sheets are used to populate a blank database. The assumption when the setup sheet is generated is that the information for that category of objects is “new”—you’re not overwriting existing information. The setup sheets are designed to generate an error (“x is not unique”) if you try to import information for an object when that information already exists in the database. You saw an example of a setup sheet at the top of this blog.
Update sheets, on the other hand, are designed to help you update EXISTING information. The assumption here is that you are changing or appending information. Update sheets look much different from Setup sheets because they include shaded columns that display pre-existing data for the objects you are working with. Here is what an update sheet looks like:

The process for using setup and update sheets is straightforward:

Select the desired sheet type (Import or Update) and generate the spreadsheet template from EnergyCAP, using appropriate filter information. (Administration > Setup Manager)
Fill out the spreadsheet in Excel, and save the resulting file to a known location.
Import the completed template using the Import Template wizard (Administration > Setup Manager)

Cheats for Setup (Import) Sheets

Here are a couple of cheats for using setup sheets.

Always use the correct ORDER when setting up your organization. Here’s the best practice: first do VENDORS, then RATES, then COST CENTERS, then ORGANIZATIONS, then ACCOUNT/METERS. This is because each setup sheet will display drop-downs for any known information relating to that option.
Use drop-downs whenever possible to complete your setup. So if you are filling out the Accounts & Meters setup sheet, and you’ve already filled out the Vendor setup sheet, then it’s a snap to select the correct commodity associated with a meter related to the account, because that information is already in the “system” and in the spreadsheet dropdowns. Using the drop-downs consistently will help ensure reliability and consistency in your data.

Cheats for Update Sheets

Update templates for User-Defined fields are now available! This functionality is brand-new with EnergyCAP Online version 3.4.1, so if you use user-defined fields (UDFs), and you are looking for a way to get the information into EnergyCAP quickly, try these new templates.

There are four new UDF update sheets available in EnergyCAP:

Accounts User Defined Fields
Meters User Defined Fields
Places User Defined Fields
Vendors User Defined Fields

You’ll note that one new cheat is built right into the new UDF fields templates. If you want to REMOVE UDF information that is already present in the database, you can just put an “x” in the appropriate column of the update spreadsheet:

And here’s another cheat: For older update sheets, just type “null” (without quotation marks) in the relevant spreadsheet column. When you import the sheet information, the existing information will be wiped clean for that field. There is power in that four-letter word.

One more cheat: Use filters when generating your import/update spreadsheet templates. There is no need to generate gas meters if you are updating electric meter information. Don’t bother sorting through spreadsheet data from the west campus when you’re only interested in the east campus. There are lots of filter options, and you will save time and effort if you take advantage of them. You’ll see the available filter options once you select the type of data object that you will be generating the spreadsheet for.

To review, setup (import) sheets are used when the database is empty and you want to add stuff. Update sheets are used to change something that is already in the database. For instance, let’s suppose that you wanted to update square footage for all your buildings. You would use the Organizations & Buildings update sheet for that purpose.

On common way to use update sheets is to move meters and/or buildings around in your organization hierarchy. Although you could use the drag and drop functionality in EnergyCAP, it would also be easy and reliable to use an update sheet. For a building departmental change, it could be as simple as changing the Parent Place for that building (or group of buildings). And you could select the Parent Place using a dropdown.

Want to learn more about setup and update sheets? Come to our Catalyst Training for Savings Conference this spring! That session starts at 9:30 on Wednesday, April 27. Earlybird registration discounts for Catalyst are available until the end of January.