After data entry is complete for a Data Importer template, the Template Validation and Import form is used to validate the data in the Excel Workbook and import it into your Essential database. Email messages are sent to specified personnel that provide validation and import process result notifications, e.g., successful validation, successful import process, erroneous data.
For instruction on validating and importing template data, click the following links or use the scroll bar to scan the page.
During the validation process, the Excel Workbook data is checked against the validation rules established for the template to identify any data entry errors. An option is available to immediately import data after validation, or to wait until a later time. When the import process is executed, data is loaded into the database electronically. The appropriate tables are populated based on the criteria defined when the template was created. Email messages are generated to notify designated personnel of the validation and import process results, i.e., successful validation, successful import, or erroneous data.
For all templates, the following options are available for validating and importing data at one time:
Validate and Import If No Errors - When errors are present, no records will be imported. The data can be corrected in Excel and the file resubmitted for validation.
Validate and Import All Valid Records - Records that passed validation will be imported and records that failed validation will not be imported. Records can be corrected in Excel and resubmitted for validation.
Described below are three additional import options that are available for most templates; exception are noted.
Reject and Mark as Duplicate - will not load duplicate records. Duplicate records will be flagged during the validation process.
Add as a New Record - will add new records, including a duplicate without modification if allowed by the applicable Essential tables. Otherwise, duplicate records will be flagged.
Revise
Existing Record - will replace existing record values with
the new values, except for the following templates:
– Exposure Session
– Exposure Session Sample
– Material
– Material Component
– Material Minor Components
– Material Physical Property
– Material Synonym
– Material Vapor Pressure
– MSDS Information
– MSDS Publishing
– Process Unit
Multiple template files can be identified on the Template Validation and Import form for the validation and import processes. For Task Assignment template data, enter a Go Live Date. The Go Live Date is compared to the Task Due Date and only those records with Task Due Dates on or after the Go Live Date are accepted. Otherwise, a validation error is generated. One or more email recipient addresses can be specified in the Email Address field. Separate each address with a semi-colon. A copy of all emails is automatically sent to your Data Importer administrator. Refer to Email Notification and Messages for additional information.
Any circumstances that may require special consideration for optimum validation and import results are described below, per Data Importer template.
Note:
To avoid possible serious validation errors, all
templates should be re-created after an upgrade to a newer version of
Sphera Essential.
In addition to helping new Compliance Manager module users import existing compliance applicability determinations, the Compliance Applicability template can be useful in the following example business cases:
Assume a new well with similar characteristics as previous wells has been drilled. The applicability for one of the existing wells could be exported and after changing the entity, imported for the new well.
Assume new requirements have been received and applicability determinations made. This work can be shared by comparable facilities Other entities could review the exported updates and import the applicability that affects the entity.
Consider the following:
The entity, rule, and citation identify a unique compliance applicability record. When using the Add as New Record or Revise Existing Record import option, existing records will be updated with the data being imported; a new compliance applicability record is not created when one already exists.
A compliance applicability record can be associated with existing controls, task assignments, and document links via the template; new controls, tasks, and document links not already a part of your Essential database need to be created and associated with the compliance applicability record after it is imported.
Due to multiple data dependencies, any process units, emission units, tasks, and document attachments associated with an existing compliance applicability record must be updated on the Compliance Applicability form. However, compliance applicability records can be updated with additional units, tasks, and document attachments specified via the template; just existing unit/task/attachment associations cannot be changed or deleted using the template.
There are two default Contact/User template configurations:
Default - establishes a user login account, a contact and personnel record, and an association between the user login account and contact. The CyberRegs mapping process for user login accounts must be completed using the Resolve Mappings function available on the CyberRegs User Mapping form. Refer to Resolve Mappings for instructions.
Users Without Contact - creates a contact and personnel record if none exists and associates the contact with the user login account record. When a contact and personnel record exists, it is associated with the user login account record if the contact is not already associated with another user login account. An error occurs when a contact is already associated with another user login account; no Essential data is changed in this instance.
Note: Beginning with the Essential
7.7 release, the previously optional Contact
field on the User Manager form
became a required field. The Users Without Contact configuration is designed
to bulk update the user login accounts that are missing a contact. The
Essential - User Manager Contacts
release document provides additional information, along with specific
steps for the bulk update process.
The task work flow configuration for an event template can only be imported and built one time using the Data Importer tool. Any revisions need to be made via the Essential user interface. Refer to Event Templates for additional information.
There are two processes involved when importing data with the Generic Event Tasking template and the Entity Specific Event Tasking template. First the task templates are imported and validated; then the event template and associated task work flow configuration is built. When a task template is used for more than one node in the task work flow hierarchy, consider the following:
The Validate and Import All Valid Records option must be used. The first task template instance will be used to create the task template. Just the accompanying data (e.g., dependent on task, conditional answer, etc.) of all other instances of the same task template will be imported and used to build the task configuration for the event template. However, validation results will show just the first instance as imported; all other instances will be marked as a duplicate and return a validation error.
After all task template data is imported, the data in the Task Template ID, Dependent On Task, Conditional Answer, Event, and Task Statement fields is used to create the task template hierarchical work flow for the event template. As long as this data exists for each instance of the task template, the hierarchy will be correctly established and the duplicate validation errors can be ignored.
The External ID is required to import data with the External ID Parameter template and the Parameter Groups template. The ID is entered on the Parameter Definitions form and is used to identify/select parameters regardless of parameter level (entity, process unit material, emission unit, etc.) This ID must be unique per entity.
Additional considerations for Process Data Manager:
The External ID is not required for Process Data Manager. However, when the External ID field is blank and a tag is mapped to the parameter, the External ID field is automatically populated with the tag name.
If an External ID exists for a parameter, the parameter tag mapping process will not overwrite/modify the ID.
To identify/select parameters for the Parameter Groups template, an external ID must be assigned to the parameter. Refer to Parameter Definitions for additional information.
When a parameter group is established via the Essential user interface, the logged-in user is automatically added as an approved user for the group. With the Parameter Groups template, all approved users must be populated manually via the User field.
When using the Revise Existing Record import option, the revised data plus all of the existing data MUST be entered. If you enter just the data that has changed, the template default values will override existing data—which may not match the default values.
Just the entities associated with an entity type assigned the Create and/or Manage Permits functionality option are available as valid entries in Permit template Excel files.
Just the entities associated with an entity type assigned the Create and/or Manage Process Units functionality option are available as valid entries in Process Unit template Excel files.
When importing Rules template data with multiple citations, you may find that the Effective Date fields for some of the citations are not recognized as containing dates. (The "Effective Date is a required field to establish new citations" error message is displayed.) This occurs when the records in the Rules template contain more citations than the first record in the template. For example, if the first rule record in the Rules template (i.e., Row 2) contains 4 citations, the Effective Date for any citations greater than 4 will not be recognized as a date. To resolve the issue, move the Rule record with the most citations to Row 2.
If recurrence information is included on the Task Assignments template for a new task assignment record only, the recurrence information will be added to the new record. However, to ensure data integrity, recurrence information entered on the Task Assignments template for an existing task assignment record will not revise the record's recurrence data—whether or not any recurrence data already exists. Changes to recurrence information for an existing task assignment need to be made via the Essential user interface. Refer to Setting up Recurrence Information for a Task Assignment for additional information.
When importing Task Assignments template data with multiple citations, you may find that the Effective Date fields for some of the citations are not recognized as containing dates. (The "Effective Date is a required field to establish new citations" error message is displayed.) This occurs when the records in the Task Assignments template contain more citations than the first record in the template. For example, if the first task record in the Task Assignments template (i.e., Row 2) contains 4 citations, the Effective Date for any citations greater than 4 will not be recognized as a date. To resolve the issue, move the task record with the most citations to Row 2.
Just the entities associated with an entity type assigned the Create and/or Manage Task Assignments functionality option are available as valid entries in Task Assignments template Excel files.
The steps to run the validation process only or to run the validation and import process at one time are the same except for the last step—click the Validate button or Validate and Import button. Keep in mind a record with errors will not be imported until all erroneous data in the record is corrected.
To validate and import template
data
Click Tools > Data Importer > Validate/Import Templates in the Navigation Tree to open the Template Validation and Import form.
Enter the address for each recipient
of the validation notification email message in the Email
Address field. Separate multiple addresses with a semicolon.
Note: A copy
of the email message is automatically sent to the address of the administrator
identified in the Data Importer configuration file. Refer to Email
Notification and Messages for additional information.
Enter a Go
Live Date when validating Task Assignment template date, or
use the Calendar button and
select a date.
The Go Live Date is compared to the Task Due Date and only those records
with Task Due Dates on or after the Go Live Date are accepted. Otherwise,
a validation error is generated.
Enter the complete file path and name of the file to be validated or click the Browse button to select it.
Click Add to include the file in the process.
Repeat steps 4 and 5 to include
other files in the validate/import process.
Remove a file listed by selecting it and clicking the Remove
button.
Enter a Reason for Change when applicable.
Click Validate to run the validation process only, or click one or the Validate and Import buttons to run the validation and import process.
Click OK to close the confirmation message window. The process results will be provided in an email message to the recipients identified in the Email Address field. Refer to Email Notification and Messages for additional information.
Data Importer email notifications are sent when a template is validated/imported. The message provides validation and import process results. The email field on the Template Validation and Import form must contain at least one email address. However, multiple addresses can be specified by using a semi-colon to separate each address. A copy of the email is sent to your Data Importer administrator (at the address designated for the "AdminEmailID" key in the Data Importer configuration file). The address for this key also receives emails for server and service errors.
When the validation only process or validation and import process is complete, a status message is sent to the email recipients specified on the Template Validation and Import form. Listed below are the messages generated by each process. When an error is found, the affected record number is identified (represented by X in the following table). A link to the file is also included in the email. Review the file to determine the extent of the errors and correct invalid data.
Process |
Status Message |
Validation only, the validation is successful |
Valid |
Validation only, the validation is not successful |
Invalid Data |
Validation and Import, both are successful |
RecordX: Added (Primary Key value) |
Validation and Import, the import is not successful |
RecordX: Error Invalid Data |
Other errors |
RecordX: Error Message: (e.g., File not found) |
|
|
|
|
|
|
