Setup Sheet Import

Overview: To add a large number of meters, facilities accounts and/or vendors to your organization in EnergyCAP®, it may be convenient to use an Excel setup sheet provided by EnergyCAP, Inc.. This spreadsheet is comprised of 50+ columns; spreadsheet data provides a guide for the imptsk.exe importer to create the meters, accounts, and locations in their respective trees in the database. The Setup Sheet Import option is used most often for initial setup of the organization.

To download the current Import Setup spreadsheet, click here.

! IMPORTANT NOTE: The setup sheet’s columns are not customizable. They must not be re-ordered, removed, or supplemented  Do NOT use commas in any of the setup sheet data!

*We recommend that you use the meter code in the Meter GL
**If you have user-defined field information or rate code information we can import it at this point in one of the extra GL Subcodes.

Notes:
There are several items to consider when using the account setup function of the importer. It is recommended that you consult with your Implementation Engineer or Project Manager before you perform a setup import. Some of the things to consider are: 

• There can be no commas in any of the data! Commas will cause the importer to look for the end of record marker in the wrong area and will have unpredictable results. 

• The data must start on row 6 of the setup spreadsheet. Use the spreadsheet we provide to minimize chances of error. 

• If you are running this import to perform a mass creation of meters and accounts in an existing structure, only the first parent needs to be referenced. 

• This is not a tool to update accounts and meters. Use this only to create new objects. 

• If you need to create any tree structure with more levels than what we provide, you must manually create the levels in the facility and accounting trees prior to running the import. 

• After you save this file to .csv for import, it would be a good practice to open the .csv file into a text editor, such as Notepad or Wordpad, to view the data without the automatic formatting that Excel performs.

Example: 

-Company(s)
-Region(s)
-District(s)
-Facility(s)
-Place(s) or Cost Center(s)
- Meter(s) or Account(s) 

You must create the Company, Region, and District objects on the facility and accounting trees prior to import. Reference the District object as Parent’s parent Place Code & Name or Parent’s parent CC Code & Name.

There are two ways to import the Setup Sheet information to EnergyCAP.The easiest way is to use the import command from the File menu as follows:

1. Run the EnergyCAP software.
2. Navigate to the Facility Manager (Setup>Facilities).
3. Click File>Import. The Open window will be displayed. 
   
   
4. From the Files of type ... drop-down box, select Setup Import (*.csv).
   
   
5. Use the window browse features to navigate to the desired setup file and highlight to select it for import.
6. Click Open. The setup sheet information will be imported.

It is also possible to import setup sheet external to EnergyCAP by running the following command from a command line prompt. Follow the format of other documentation of external tasks: 

"Location of imptsk.exe file" -d ODBC DSN name -u EnergyCAP UserID –p Password -c STANDARD:ACCOUNT_IMPORT -f "Location of .csv file for import" -l "log.txt file location" 

The usage is:

Imptsk –d[dsn] –u[user] –p[pwd] –c[converter] –f[path] [-x] [-a path] [-l path] [-s section] [-v]
where
-d dsn -ODBC data source name
-u user -system user ID
-p pwd -system password
-c converter -name of the converter which is: STANDARD:ACCOUNT_IMPORT
-f path -file to be converted. Setup template saved in CSV format
-x -delete the import file after importing the data
-a path -archive the data to the specified path
-l path -name of the log file
-s section -profile section name
-v -verbose messages 

Example: "C:\Program Files\EnergyCAP Enterprise\imptsk.exe" -d ECE_Test -u ImportUser -p u$er1d -c STANDARD:ACCOUNT_IMPORT -f "c:\temp\TempSetup.csv" -l "C:\Temp\log.txt" 

1. "c:\Program Files\EnergyCAP Enterprise\imptsk.exe” – The file path for the importer program, imptsk.exe. It is typically located in your EnergyCAP 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 the folder before executing the imptsk.exe file. Simply pointing to the program folder from another location may prevent the imptsk.exe program from running required supporting DLL files.
2. –d ECE_Test –The name of the database into which you wish to import the data.
3. –u ImportUser – 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 u$er1d - The password associated with User ID you are using to import the data.
5. -c STANDARD:ACCOUNT_IMPORT – If you follow the import file specifications in this bulletin, this is the correct converter to use.
6. -f " c:\temp\TempSetup.csv " – The file path for the .csv document which you wish to import into EnergyCAP Enterprise.
7. -l " C:\Temp\log.txt " – The Meter/Account/Place (MAP) 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.

After you run the importer you will see the following:

Prior to importing, the first screen will come up and perform error checks on the file.

After the error checks are performed, files that pass will proceed to the import process.

If any errors are found, you will receive an error message.

From this point, please reference the log file specified in the –l section of the command.

NOTES:

• If you are appending the same log.txt file each import, the latest import will be at the bottom of the file.
• Notice the “verifier column missing” error. This is not what caused the error but these items were skipped.
• This file errored out, because the meter code exceeded 16 characters.

In addition to assisting with initial organization setup, the import Setup Sheet can also be used to quickly add large groups of Places, Meters, or Vendors to an existing organization. However, since the importer has been designed only to add NEW database objects and not to overwrite existing data, it is impossible to update existing organization properties,

This limitation can actually be an advantage in many instances.

To import a list of new vendors, for instance, prepare the csv file by simply pasting the vendor codes and display names into the appropriate columns in the Setup Sheet and then insert existing organization data into the remaining required fields.

Then import the data. Since the importer will not overwrite existing data, only the NEW information (in this example, vendor code and vendor display) will be added to the organization database. The image below shows the result of the import above:

The same technique used for vendors can be used to import places, accounts, or meters. It is also possible to create additional layers in the Account Manager or Facility Manager hierarchy by correctly referencing a previously-created Parent Place and/or Cost Center.