Checker
This section outlines the rules governing the structure and quality control of Excel files processed by our system. These rules help ensure consistency, completeness, and compliance with predefined data expectations.
Overview
This section outlines the rules governing the structure and quality control of Excel files processed by our system. These rules help ensure consistency, completeness, and compliance with predefined data expectations.
Technical Specifications
Sheet Rules
Sheet rules define the structural and quality control criteria for each table (sheet) in an Excel file. These rules ensure consistency and compliance with predefined expectations.
Table metadata
| Field Name | Description |
|---|---|
| Name | The identifier for the sheet rule. |
| Sheet Name | The expected name of the sheet in the Excel file. |
| Description (Optional) | A short explanation of the purpose or function of the sheet. |
Quality Control Settings
| Setting | Description | Behaviour (Enabled) | Behaviour (Disabled) |
|---|---|---|---|
| Is Required | Specifies whether the sheet must be present in the Excel file. | The sheet must be included in the file. If missing, it will be flagged as an issue. | The sheet is optional. If missing, no issue will be reported. |
| Check Column Order | Ensures that columns in the sheet appear in the same order as defined in the column rules. | The order of columns in the sheet must match the predefined column sequence. | Column order does not matter as long as all required columns are present. |
| Flag Extra Columns | Determines whether additional, undefined columns should be reported as issues. | Any columns in the sheet that are not listed in the column rules will be flagged. | Extra columns are allowed and will not be flagged. |
Row Rules
Row rules define the expected quantity and constraints for rows within an Excel sheet. They ensure that the sheet contains an appropriate number of rows and that data completeness requirements are met.
| Setting | Description | Behaviour |
|---|---|---|
| Min Rows | Minimum number of rows expected in the sheet (excluding the header row). | If the sheet contains fewer rows than the specified minimum, it will be flagged as an issue. |
| Max Rows | Maximum number of rows expected in the sheet (excluding the header row). | If the sheet contains more rows than the specified maximum, it will be flagged as an issue. |
Column Rules
Column rules define the expected structure, constraints, and relationships for individual columns in an Excel sheet.
Metadata
| Field Name | Description |
|---|---|
| Column Name | The exact name of the column in the Excel sheet. |
| Description (Optional) | Description of what this column represents. |
Basic Validation
| Setting | Description |
|---|---|
| Required | Whether this column must contain values (cannot be empty). |
| Allow N/A | Whether 'N/A' or 'n/a' values are allowed for numeric fields. |
Uniqueness Validation
| Setting | Description |
|---|---|
| Is Unique | Whether values in this column must be unique. |
Compound Key Validation
| Setting | Description |
|---|---|
| Is Compound Key | Whether this column forms a compound key with other columns. |
| Compound Key With | Comma-separated list of column names that, together with this column, form a compound key. |
Pattern Validation
| Setting | Description |
|---|---|
| Regex Pattern | Regular expression pattern that values must match. |
| Allowed Values | Comma-separated list of allowed values (enum). |
| Disallowed Values | Comma-separated list of values that must NOT appear in this column. |
Naming Convention Validation
| Setting | Description |
|---|---|
| Validate Prefixes | Check This If Values Must Start With Specific Prefixes |
| Allowed Prefixes | Comma-Separated List Of Allowed Prefixes (E.G., 'FCU-,DR-,AHU-') |
Relationship Validation
| Setting | Description |
|---|---|
| Has Relationship | Check this if this column value needs to be checked within another column (foreign key check). |
| Related Table Name | Name of the table containing the related values. |
| Related Column Name | Name of the column containing the related values. |
| Relationship Case Sensitive | Whether relationship checking should be case-sensitive. |
| Allow Comma Separated | Allow comma-separated lists of values, each validated individually against the related column |
Picklist Rules
| Setting | Description |
|---|---|
| Has Picklist | Check this if this column value needs to be validated against a picklist of reference data. |
| Picklist Field | Picklist field containing reference data for validation. |
| Picklist Case Sensitive | Whether picklist value matching should be case-sensitive. |
Data Type Choices
| Data Type | Description |
|---|---|
| String | A sequence of characters (text). Example: AHU-01 |
| Integer | A whole number (positive or negative) without decimals. Example: 42 |
| Float | A number that includes decimals. Example: 3.14 |
| Date | A date value in YYYY-MM-DDThh:mm:ss format. Example: 1900-12-31T23:59:59 |
| A text string that must follow an email pattern ([email protected]). | |
| Number | Can be either an integer or a float. Example: 100 or 45.67 |
| Boolean | A True or False value. Example: True |
| URL | A valid web address. Example: "https://example.com" |
| UUID | A universally unique identifier (UUID). Example: "550e8400-e29b-41d4-a716-446655440000" |
Value Constraints
| Setting | Description |
|---|---|
| Min Length | Minimum length for string values. |
| Max Length | Maximum length for string values. |
| Min Value | Minimum value for numeric columns. |
| Max Value | Maximum value for numeric columns. |
Date Validation Rules
| Setting | Description |
|---|---|
| Date Format | The expected format for date values. |
| Min Date | Minimum allowed date (inclusive). |
| Max Date | Maximum allowed date (inclusive). |
| Validate Business Days | Whether dates should be validated as business days only (Mon-Fri). |
| Allow Future Dates | Whether future dates are allowed. |
Date Format Choices
| Setting | Example |
|---|---|
| ISO 8601 | YYYY-MM-DD |
| ISO 8601 With Time | YYYY-MM-DDThh:mm:ss |
| UK Format With Slashes | DD/MM/YYYY |
| UK Format With Dashes | DD-MM-YYYY |
| US Format With Slashes | MM/DD/YYYY |
| US Format With Dashes | MM-DD-YYYY |
Record Rules
Record rules allow the system to identify and validate specific records within a sheet based on defined detection criteria. Using a combination of sheet name, column name, and a prefix ("begins with"), the system filters rows to which rules should apply. Record count validations ensure that the number of matched records falls within expected minimum and maximum thresholds. This targeted approach enables precise, rule-based quality control within complex or mixed data sheets.
Record Detection Fields
| Setting | Description |
|---|---|
| Sheet Name | Name of the sheet containing the records to validate |
| Column Name | Column name used for record identification |
| Begins With | Text value that the column should begin with to identify matching records |
Record Count Validation Fields
| Setting | Description |
|---|---|
| Min Record Count | Minimum number of records expected to match this rule |
| Max Record Count | Maximum number of records expected to match this rule |
On This Page
Need Help?
If you have any questions about this documentation, our support team is ready to help.
Contact SupportWas this documentation helpful?
Your feedback helps us improve our technical documentation. Let us know if this page was helpful or if you have suggestions.