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.

Last Updated: May 26, 2025

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 Rules

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.

Column rules

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
Email 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 rules

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

Need Help?

If you have any questions about this documentation, our support team is ready to help.

Contact Support

Was this documentation helpful?

Your feedback helps us improve our technical documentation. Let us know if this page was helpful or if you have suggestions.