Activity data files can be produced by your suppliers or within your organisation. These files are for upload into ESP CSR and each file will usually have a different configuration, e.g., file types, formats and content can vary across files.

Data configurations provide flexibility catering for a range of different file sources, rather than requiring one standardised file configuration.

This functionality allows you to map information in your activity data files to the correct locations, and inventory within ESP CSR.

To find these, on the menu bar, select Data Collection > Data Configurations

Table of Contents

View Data Configurations

The grid on the Data Configurations page displays a list of all your organisation's configurations in alphabetical order.

The details of a selected data configuration include:

  • The file format (e.g., Excel) and the location of activity data in the file, e.g., sheet 1
    Currently only Excel format files are fully supported
  • Information which enables identification and mapping of the activity data file contents to ESP CSR location(s) (e.g., business units, assets etc.,) and/or inventory item(s)
  • Information which enables identification of the activity data in the file and, in conjunction with the identifying information, populates the relevant activity data in ESP CSR

Add, Edit and Delete Data Configurations

Adding Data File Configurations:

  1. Select Add a new data configuration located above the grid on the Data Configurations page

  2. Select the correct type of data configuration, displayed below.
    Click this link for more advice on choosing the correct data configuration

2. Enter the data configuration's name and description
It is good practice to use a name that allows easier searching, e.g., Air Travel (Tandem).  Also include identifying information in the description

3. Select a data configuration file type from the drop-down and the sheet number that contains the activity data

4. Identify whether your file has any location data. If it does, tick the Does this file contain location related data box and select one of the options listed below in the drop-down

The most commonly used options are:

  • Location identifiers: Please ensure the location identifiers in the file matches the relevant location identifiers in the system
    This field can be updated in the Location Management Page or the Location Smart Excel Template
  • Match in a look-up list (location): Map data in the file to the correct location in the system using lookup lists
    Configure the location look-up list by following the article instructions linked here

5. Once the above option is selected, you can do one of the following:

  • Location identifiers: choose the column and cell reference for the location identifier  
    This should be the first cell that the identifier appears in, e.g., C3 

  • Match in a look-up list (location): select a look-up list from the drop-down and match the column and cell reference from the lookup list to the file

6. Select the Does this file contain inventory related data checkbox and identify what type of inventory data exists within the file

7. Select one of the following from the drop-down

The most commonly used options are:

  • Inventory identifiers: Please ensure the inventory identifiers in the file match the relevant inventory identifiers in the system
    Identifiers for inventory can be updated via the Inventory Management Page or the Inventory Smart Excel Template
  • Match an existing inventory item: Maps all data within the file to ONE location and inventory
    This is for files that contain activity data for only one location and inventory
  • Match in a look-up list (inventory): Map data in the file to the correct inventory in the system using lookup lists
    You can configure the inventory look-up list by following the article instructions linked here

8. Once the above option is selected, you can do one of the following:

  • Inventory identifiers: choose the column and cell reference for the inventory identifier. This should be the first cell that the identifier appears in, e.g., C3  
    In this section, if a match is not found, you can choose to either ignore the data and alert the user or add this data to another inventory item

  • Match an existing inventory item: search for the inventory item in the search box and select it
    Ensure you have chosen the correct location and inventory


  • Match in a look-up list (inventory): select a look-up list from the drop-down and match the column and cell reference from the lookup list to the file

9. Once the location and inventory data has been identified and configured, select next 
10. Additional options for Identifying usage, cost and dates in your data should now appear as below. Enter a column and cell reference.
This should be the first cell that the information appears in, e.g., C3
The options within this box can vary depending on the Data Configuration Type chosen

Fill in the options as shown below:

Row by Row Identifying Usage, Cost and Dates in your Data:

Invoice File's Identifying Usage, Cost and Dates in your Data:

Month by Column Identifying Usage, Cost and Dates in your Data:

10. Once the above information has been completed, identify how the data configuration knows when to stop reading the file by selecting a column and cell reference

11. Click the Save button at the bottom of the page to save the data configuration

Edit Data Configurations:

To edit a data configuration:

1. Go to the Data Configurations page, locate the data configuration on the grid and select the name
2. The Configuration Details page is displayed. This is similar to the Add a new data configuration page and will be fully populated with the configuration information specified and save during add
3. To edit, click in the field to be updated and change the information as needed, e.g., modifying usage data column and cell reference C3 to D3 if the column in the file has changed
4. Once all necessary changes are made, click the Save button at the bottom of the page

Delete Data Configurations:

To delete a selected data configuration, go to the grid on the Data Configurations page, find the configuration and select Delete.

 

Data Configuration Types

Choosing the Correct Data Configuration Type

Choosing the correct data configuration type from the outset is critical as this determines the options that are available. The following suggestions and examples may assist you.

Row by Row Files

Row by row files are the most common and familiar structure of data files and are aligned with database formats. The column names are at the top of the file and data flows down.

A common situation is that data for a single month may be spread across several rows requiring the data to be summed up into their corresponding months. This is referred to as Transactional Style Data as many individual transactions make up a single months data.  This option can be selected by ticking the checkbox.

Monthly Data by Column Files

Monthly data by column files have a 2 dimensional format. Typically, the identifying information is in the initial columns from the left flowing down the file. Activity data (e.g. kWhs, litres, kgs etc) then appears in monthly columns with a date as a heading. e.g. Jul-16 in column Q, Aug-16 in column R and so on per the screenshot below.

Monthly file icon

In some cases information can include activity data and cost, e.g., Jan 2012 could have activity data in column B, cost data in column C.

Invoice Files

Invoice files are commonly used by utility suppliers. In most instances, each invoice and its details are presented on a single row. Invoice activity and cost data for a date period is determined by either a combination of a Read From date and a Read To date or a combination of a Read To date and a Number of Days value.

The column names are usually at the top of the file and invoice data flows down. In some cases invoices may spread over more than one row in the file and contain a mixture of fixed and variable costs.

For data sources to understand the contents of an invoice file certain types of data must be present:

  • A unique identifier e.g. ICP
  • A read to date
  • Either a read from date or number of days

LBG Files

LBG files, or more broadly corporate community investment (CCI) files, often have a row by row format. They typically contain an extensive number of data fields related to individual CCI contribution, leverage or management cost transactions, often as specified by LBG. Transaction details can be uploaded and recorded in the ESP CSR Corporate Community Investment module for value add analysis and reporting purposes.

 

Handling Dates

Specifying Date Formats

The format of dates in an activity data file can vary and its important to check the file to verify the date format used, e.g., dd/mm/yyyy.

Often, a date is modified to display in a more user friendly format, e.g., a date displays as Jul-2011, but placing the cursor in the cell will show the date as dd/mm/yyyy or mm/dd/yyyy in the fx (Insert Function) box. It is this dd/mm/yyyy or mm/dd/yyyy format that should be entered in the Data Configuration.

Specifying Date Formats in CSV and Text File Configurations:

If viewing CSV or text files in excel, the format of a date in a cell may be altered to reflect a more user friendly format and/or the format is based on the settings of you computer's locale. The safest way to determine date formats in CSV and text files is to open the file in Notepad. This is the format to enter when creating a data configuration.

Good to know:

  • To add and edit data configurations, a user needs to be assigned the role of Data File Configuration or Company Administrator.
  • CSV and text files are not currently fully supported.  It may be necessary to save the file using an Excel format.