Introduction

The Data Edit Spreadsheet Activity is useful for editing multiple Items at the same time.

It also has the ability to export different Items and their properties to CSV; allowing users to change fields in a Spreadsheet application and re-import them back into the Sigma database.

**Click here for a pre recorded training session for additional help and guidance -  Editing Data in Bulk which includes how to export Sites, Meter etc and how to update data and change Contract and Account numbers

Opening the Activity

• Click on Data Edit Spreadsheet Activity

Selecting the Item details

You can only pick one type of Item to be edited at a time. 

For example, you could choose a selection of Sites to view/edit.  The information displayed could be the Site name, address, Post Code, Site ID etc

• Select the Items from the Data Selector (for example, Sites, Accounts or Meters)
• Click  and  the Data Selection pop up appears

The popup has 3 tabs:

• Fields
• Properties
• References 

Complete as follows:

Fields tab

• Tick all the required fields you want to see from the Fields tab

or

• Click Select All to select all fields

or

• Click Unselect All to deselect all fields

          Site Fields example                                                                                   Meter Fields example                                                                                                                                                               

          Periodic Channel Fields example                                                             Account Fields example

Properties tab

The Properties tab allows you to output any Property Keys associated with that Item.

• Click on the Properties tab
  • Tick all the required fields you want to see from the Fields tab

References tab

The References tab allows you to output additional database objects (such as parent items) however, these are not editable.

• Click on the References tab
  • Tick all the required fields you want to see from the Fields tab

          Site Example                                                                                           Meter Example

Note:  You will have the option to include Commodity when you are editing the Account, Meter, Periodic or Non Periodic Channel

When you have completed all three tabs:

• Click 

Main Window details

All the selected fields will be displayed in the main window.

Columns can be moved around by dragging and dropping the Column header to the correct place

Note:  Any columns with # in the Column Header, indicate they are fields that can not be edited as the are Reference fields

Entering / Editing Values 

You can edit the fields directly from the table or use the export/import options 

Entering / Editing values on screen 

• Click into a field to enter new data or edit existing data

For some fields, there is a tick box to check. 

• Tick any tick box

or

• Right click on any check box and choose Check All to tick all of the fields

In some instances there is a dropdown list to choose from.

• Click on the downward arrow and choose from the dropdown

Save once you are  happy with your amendments:

• Click  

The data will either Save successfully, or if Validation is applicable, with either Pass or Fail (see below for more details)

Validation Status Column 

The Validation Status column is a default Column.  If it has been removed, it can be added back in:

• Click on the white downward arrow on the Column Header
• Click Columns
• Tick Validation Status

The Validation Column is applicable to Contract Containers, Periodic and Non Periodic Channel data. 

Initially, the Column will show as N/A until you Save the data and will then change to Passed or Validation Error - Right Click for Details.

Note:  All Items that show Passed will save, those with Validation Errors will not.

Viewing the Validation Error(s)

• Right click on the line
• Click View Validation Error(s)

Example Validation Errors

• Channel Data Quality error

• Channel is set as Max is Last error

• 2 errors on one Channel

• Click OK to return to the Data Edit Spreadsheet

Resolving the Validation Error(s)

Once you have viewed the error, you will then need to resolve it order to save the Channel.

Viewing / Editing the Channel

• From the Data Selector, right click on the Channel
  • Click Edit
• Resolve the issue, as in the above example the Data Type is not compatible with the Data Measurement

• Change the Data Type to Energy Consumption
• Click OK to save

Refreshing the Data

• From the Data Edit Spreadsheet, click  
  • This Channel will change from Validation Error(s) to Passed

Note: All errors will need to be rectified before a Channel can be saved.

Exporting, Adding and Editing values in Excel

If you do not want to update the data in the Data Edit Spreadsheet Activity, you can export the data shown on screen to a CSV file, make amendments and then reimport the data.

Exporting the Data to a CSV file

• Click on  to download the CSV file
• Save to your own drive 

Note: Do NOT amend the Column A (the Sigma ID) or Row 1 (the headers) as these are both needed when importing the data back into Sigma

Adding / Editing the Values in Excel

• Open the saved CSV file
• Make the amendments to the data and save again as a CSV file
• Open Data Edit Spreadsheet Activity

Importing the revised Values into Sigma

• Click  
• Click Yes to the prompt

• Choose the file and the following prompt will appear

The amended data has now been saved if there are no Validation Errors.

If there are Errors, the following will show and a CSV file will be downloaded

• Open the CSV file to show the errors

Resolving the Validation Errors

Finding the Channels in Sigma

• Open the CSV File
  • Copy the Item Names
• Open the Data Edit Spreadsheet Activity
• Change the Item Finder to the Item you are updating, ie the Periodic Channel
• Click on the Multi Search icon to expand the Search field

• Paste this list of Periodic Channels into the Search field
• Click on the Magnifying Glass

Resolving the Errors 

Take each line in turn and resolve the error(s) for each one:

• Right click on one of the Channels in the Data Selector
  • Click Edit
• Amend the Error, ie duplicate ID etc
  • Click Save

Checking the Data

• Highlight all the Periodic Channels in the Data Selector
• Click Edit Items
• Select the data you want to see on the Spreadsheet
• Click Edit Fields

Initially, the Validation Errors column will show as N/A. 

To check the data:

• Click Save and the column will show either Passed or Validation Error

Exporting Values to Excel for editing

If you want to see the amendments before saving them, you can use the Export to CSV option, then import using the Edit from CSV option.

Exporting the Values

• Click on  to download the CSV file
• Save to your own drive 

Note: Do NOT amend the Column A (the Sigma ID) or Row 1 (the headers) as these are both needed when importing the data back into Sigma

• Make the amendments to the data and save again as a CSV file
• Open Data Edit Spreadsheet Activity
• Click 
• Choose the file
• Review the data on screen

• Click   to save the data in Sigma

Exporting to CSV

The Export to CSV option will export the current data shown in the main window into a .csv file.

Editing from CSV

The Edit from CSV option allows you to upload an amended Spreadsheet, review the data and then click Save

Importing from CSV

The Import from CSV allows only the correct data edit Spreadsheet format files to be imported into the database without any review

• If any of the data fails Validation, the follow error will show:

Exporting for reference only

You can export all the data shown on screen to an Excel spreadsheet if required.

• Click on   to download the data

Property Keys

If you have existing Property Key that you want to replicate on other Items, you can do this easily with the Data Edit Spreadsheet Activity.

From the Data Selector, choose the Items you want to add the Property Key to, for example the Site, Meter or Account.

Click here for more information on Property Keys.