validate(var,profile,inputFile)

io » validate(var,profile,inputFile)

Description

This command validates any text file with the fields in each record (line) either as a ‘Fixed Length Fields’ type or ‘Delimiter Separated Fields’ type.

Various validations can be carried out as configured. The configurations includes, self description of each field and the validations to be carried out.

Validation Types supported

Each field value in the record will go through basic validations:

Data Type
- Numeric - only numeric characters
- Alphanumeric - only alphabetic and numeric characters.
- Alpha - only alphabetic characters.
- Alpha Upper - alphabetic characters in upper case.
- Alpha Lower - alphabetic characters in lower case.
- Blank - blank
- Person Name
  - common data type for First Name and Last Name.
  - accepts at least one or more characters and can be combined with a space, comma, dot, hyphen or apostrophe.
  - you can also use First Name for First Name and Last Name for Last Name to be more specific.
- Regex - syntax REGEX:<pattern>
- Any - any ASCII printable characters
Text Alignment
- Left
- Right

Other validations needs to be configured for each field of the record:

REGEX - Regular expression to check against actual field value
EQUALS - Equals to check against actual field value
IN - IN to check the presence of actual field value against a list of expected values
DATE - to do date field validation against given date pattern. See below for the date pattern syntax.
SQL - to check field value against a database query

Validations in progress (not yet implemented):

API
RANGE

Example of a SQL validation:

"validations": [
    {
        "field-name": "<FIELD_NAME>",
        "validation-methods": [
            {
                "type": "SQL",
                "params": [
                    "mydb",
                    "SELECT …  ${…} … "
                ]
            }
        ]
    }
]

The above example performs a SQL-based validation on the field marked as <FIELD_NAME>. The first parameter mydb refers to the database connection profile for executing the specified SQL (the second parameter). Below is an example of a database connection profile set up in a data file:

The SQL statement is expected as a SELECT statement which will return 1 or more rows as indication as a positive validation. No rows returned means that the desired validation has failed. It is possible to include other field values or data variables as part of the validation SQL statement. Use the standard ${...} syntax to construct a dynamic SQL statement.

Map Functions supported

Map Functions are to apply below functions on all the records on a field and get the value. Which could be later used to validate against any another field in any record.

COUNT - count to calculate the total count of the fields present
MIN - to calculate the MIN amount
MAX - to calculate the MAX amount
AGGREGATE - to calculate the aggregate amount
AVERAGE - to calculate the average amount

Parameters

var - variable name to store the results
profile - profile name that has to be defined in data file (see below example)
inputFile - input file path that is to be validated

Example

In the below example, the data file is defined with the variables with profile name. In this case, the profile name is project_profile.

Explanation:

Data file:

project_profile.mappingExcel: It is the excel file where the description of each field is defined.
project_profile.configJson: It is the configuration file in JSON format to define the validations to be executed
project_profile.reportType: It is the report type to be mentioned to generate the reports

Script:

Output:
The output file

Displays the total number of records processed with failed count
Also provides a link to open the generated report file. The generated excel report file will have all the details, that consists of ‘Summary’ sheet and ‘Error_Report’ sheet.