Data load spreadsheet validation macros
The data load spreadsheet template includes a set of macros that you can run to validate business object data before you load the data into IBM® TRIRIGA®. Running the validation macros verifies data conditions.
When the spreadsheet is created, you can download the file, input data and run any of the macros that are identified in the following table:
Macro | Keyboard shortcut | Run from | Description |
---|---|---|---|
ValidateDataSheet | Ctrl-Shift-V | Fields or data sheet | Validates data on a single sheet in the workbook. Uses the fields sheet to validate data that is specified in the corresponding data sheet. All conditions that are specified are checked. Detailed error messages are added to the VALIDATION_ERRORS column in the data sheet and are also added to a log file. A tab delimited file is automatically generated for the data sheet. |
ValidateAllDataSheets | Ctrl-Shift-A | Any sheet | Validates data on all sheets in the workbook. |
ClearAllValidationErrors | Ctrl-Shift-C | Any sheet | Clears the error messages that are contained in the VALIDATION_ERRORS column of all data sheets. |
GenerateTextFile | Ctrl-Shift-T | Data sheet | Generates a tab delimited text file for a data sheet. The drive and path parameters that are specified on the Overview sheet are used to define the location of the file. The default location for the files is in the C:\TRIRIGA folder. If the folder does not exist, it is created. The file that is generated can be used to load data into TRIRIGA. The generated file follows the naming format:BOName.txt |
GenerateDCPathAllSheets | Ctrl-Shift-G | Any sheet | Generates DC_PATH values for all data sheets in the workbook. It uses the DC_PATH_PARENT field to generate DC_PATH values. The DC_PATH_PARENT field identifies the name of the parent record by using the value of TRINAMETX field in parent sheet. All records in the hierarchy path must be included in the spreadsheet to generate the DC_PATH. The Parents row in the corresponding fields sheet is used to determine which sheets to search for a TRINAMETX value that matches the value in the DC_PATH_PARENT column. The ObjectHierarchy sheet identifies the root record in the hierarchy. |
HideFieldSheets | Ctrl-Shift-H | Any sheet | Hides all field sheets in the workbook. |
UnHideFieldSheets | Ctrl-Shift-U | Any sheet | Unhides all field sheets in the workbook. |
ConvertDateToUnixFormat | Ctrl-Shift-E | Fields or data sheet | Converts all date field values to UNIX Epoch numeric values that represent the date in the number of milliseconds from 1 January 1970. |
ConvertUnixFormatToDate | Ctrl-Shift-D | Fields or data sheet | Converts all date field values that contain UNIX Epoch numeric values, that is, the number of milliseconds from 1 January 1970, to the corresponding date values. |
The following table provides details about the conditions that are verified when data validation macros are run:
Condition | Macro description |
---|---|
Required field | When the Required property specified on the fields sheet is true, the macro checks to see if all values for this field in the corresponding data sheet are not null. |
Numeric field type | Checks to see if all values for this field in the corresponding data sheet contain numeric values. |
Date data type | Checks to see if all values for this field in the corresponding data sheet contain either valid date values or valid epoch values, that is, numeric values greater than 0. |
Boolean data type | Checks to see if all values for this field in the corresponding data sheet contain boolean values. The macro converts the column value to lowercase and validates that it is either trueor false. |
Classification lookup column exists | Checks to see if the Classifications sheet contains a column with a header value that matches the Classification value in the Fields sheet. |
Classification value | Verifies that the field value in the data sheet matches a value in the corresponding column in the Classifications sheet. |
List lookup column exists | Checks to see if the Lists sheet contains a column with a header value that matches the List value in the Fields sheet. |
List value | Verifies that the field value in the data sheet matches a value in the corresponding column in the Lists sheet. |
Text field length | Checks to see if any values for this field exceed the size that is specified for the field size. If the value for field size is blank, the validation is not performed. |
Locator field lookup column exists | Checks to see if the Locators sheet contains a column with a header value that matches the Locator value in the Fields sheet. |
Locator field value | Verifies that the field value in the data sheet matches a value in the corresponding column in the Locators sheet. |
Staging table key field | When the value for the staging table key is true for one or more fields in the Fields sheet, the macro uses those fields to generate the unique key for each row in the data sheet and identifies any rows that contain a duplicate key. |
Parent hierarchy | Checks to see if the DC_PATH values in a data sheet are specified in one of the parent data sheets. The parent data sheets are identified by the Parents row in the corresponding field sheet. The ObjectHierarchy sheet identifies the top level in the hierarchy. |
Duplicate paths | Checks all records in a data sheet and identifies the condition where a row exists with the same DC Path that is used to load this record. |
The following table provides details about the properties in the Fields sheet of the data load spreadsheet:
Property name | Property description |
---|---|
Data sheet column | Identifies the name of the corresponding header column on the data sheet. |
Field type | Identifies the TRIRIGA data type. |
Field size | Identifies the maximum size for text fields. The value is blank for all other fields. |
Classification | Identifies the name of the classification and
the unique identifier. The format is classificationName:identifier . |
List | Identifies the unique identifier for the list. |
Required | A value of true is specified to indicate that the field is required. |
Staging table key | A value of true is specified to indicate that the field is a staging table key. |
Locator | Identifies the name of the locator and the unique
identifier. The format is locatorName: identifier . |
Parents | Identifies all potential parent forms for this business object. Values are only available if the module contains hierarchical data. |