DataLoad Window
Menu Functions
Using Spreadsheets
DataLoad Options
Importing Data
Data Validation
Sorting Data
Protecting Loads

User Guide Home
DataLoad Home

Data Validation

DataLoad includes powerful data validation functionality that enables the user to ensure data is in the correct format before it is loaded. The validation rules are applied automatically when data is entered or changed in DataLoad. Each column in a DataLoad spreadsheet may have one or more validation rules applied. Cells containing data that breaches a validation rule are shaded pink and the total number of invalid cells is shown in the status bar at the bottom of the DataLoad window. In some cases DataLoad will prevent invalid data from being entered or will fix invalid data where possible, e.g. making letters uppercase when required by a rule.

Rules are configured in the DataLoad Column Setup window, which is shown on the right. This window may be opened by either double clicking in the spreadsheet's title row (shaded grey) or by selecting Tools->Data->Validation from the DataLoad menus.

The Data Validation section in the Column Setup window has a row for each validation rule and a column representing each spreadsheet column. To enable or change a column validation rule, double click in the column's cell which is located on the row containing the desired rule. The double click will put the rule in to edit mode. Where the rule is either on or off, e.g. the Not Null rule, a checkbox will appear that should be checked or unchecked to enable or disable the rule. Some rules have multiple options, e.g. Data type, and for these a droplist will be shown containing all possible values. Finally, for free text rules, such as Max value, an edit box will be shown when the rule data may entered.

Validation Rule
Not Null Column cells must contain a data value, i.e. empty cells are not allowed.
Unique Each column cell must contain a unique value and values may not be repeated. The rule is case insensitive, i.e."dataload" and "DataLoad" are the same.
Data type Restricts the data that may be entered in the column. Data may be restricted to one of the following types:

Any - Any data is allowed. Same as not using any data type restriction
Alpha - Only alpha data, i.e. letters, is allowed
Numeric - Only numbers are allowed
Alphanumeric - Data must consist of only numbers and letters
Date - Column cells must contain valid dates

Format Works with the data type rule to further restrict the column data. Format may only be used where a data type is set. The following formats are available:

Any - Any data is allowed. Same as not using any format restriction
Uppercase - Letters must be in uppercase
Lowercase - Letters must be in lowercase
Whole - Only whole numbers allowed, i.e. no decimals
Decimal - Decimals and whole numbers allowed

Min value Specifies a minimum value for the column's cells. For alpha and alphanumeric data this validates the value alphabetically and by length. For example, to specify that data must contain at least 5 letters then the alpha type should be selected and Min set to "aaaaa".
Max value Specifies a maximum value for the column's cells. For alpha and alphanumeric data this validates the value alphabetically and by length. The value entered in the spreadsheet must be alphabetically no higher than Max value and also have no more characters than Max value.