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:

Table 1. Spreadsheet validation macros
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:

Table 2. Conditions that are verified by the spreadsheet validation macros
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:

Table 3. 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.