The purpose of File Header Validations is to ensure that:

1. The correct input files have been uploaded and placed in the correct File Upload slots
2. The format (structure) of these input files is consistent with that required by the business process

The step is designed as an early identifier of any inconsistencies in the file structure, i.e. before the Input files are manipulated in the SolveXia process.

1. Configuring the Validation Template

A.    Sample Headers

Download the File Headers Validation from this article. Copy each of the valid file headers individually into each Sample Header (highlighted in orange below). You should have the same number of Sample Headers as the number of file headers you would like to validate.

B.    Error Excel Formulas

In Cell A12, input the formula: ‘=TRIM(A6)=TRIM(A9)’ (see below).

Fill the formula across all the columns in that row (i.e. Row 12) where value exists in the ‘Sample Header’ row.

• TRUE – indicates an identical match between the ‘Sample Header’ and ‘Header in the Uploaded File’
• FALSE – indicates a mismatch/inconsistency between the ‘Sample Header’ and ‘Header in the Uploaded Files

Sum of Errors

In Cell A13, input the Excel formula: ‘=IF(COUNTIF(A12:Z12 FALSE)>0, 0, 1)’

Note: Adjust the above formula for each individual file header as A12 and Z12 represent the cell addresses where your ‘Sample Header’ for that file (e.g. File 1) begin and end.

The formula should give a value of:

• ‘1’ if there are no inconsistencies between the ‘Sample Header’ and ‘Header in the Uploaded File’.
• ‘0’ if there are one or more inconsistencies between the ‘Sample Header’ and ‘Header in the Uploaded File’.

Error Message

In Cell B13, input the formula: ‘=IF(A13=0, “File 1 appears to have incorrect format.”, “”)(see below).

Repeat all the above steps for all File Headers where a ‘Sample Header’ has previously been copied in Step 1A.

C.    Total Error Formula

In Cell A2 of the ‘File Headers Validation’ file, configure an Excel formula which takes the product of all the Sum of Errors for each File Header.

E.g. ‘=PRODUCT(A13, A25, A37, A48, A59, A70)’

In Cell B2, configure an Excel formula which aggregates all the Error Messages for each File Header.

E.g. ‘=B13&B25&B37&B48&B59&B70’

2.     Configuring the Validation Steps in SolveXia

A.    Data Steps - Upload Validation File

Within the SolveXia database, create a data step called ‘Validation Files’.  Add a ‘File Upload/ Download’ property called ‘File Headers’. Upload your File Headers Validation workbook here.

B.    Action Step – Copy cell values

Create a new group called ‘Validation’. Add an action step called ‘Validate input file’. Using the ‘Copy cell values’ step, copy each individual file header into the correct ‘Header in the Uploaded File’ row in the ‘File Headers Validation’ file that corresponds to the ‘Sample Header’ (highlighted in yellow).

The formulae in the ‘File Headers Validation’ file will automatically update to pick up any inconsistencies.

C.    Decision Control Step

Create a decision control step and link the ‘File Validation’ workbook as ‘Excel file to test’.

Configure the ‘Set Conditions’ as Cell ‘A2’ ‘not equals’ ‘0’ and set the minimum number of condition to pass as ‘1’ (see below).

As configure the outcome so if the test passes the step completes successfully. Otherwise, the step will fail: 

D1.    Validation passed - Continue the process 

If the validation in point C passed, we want the process to continue. For this, go to the next action step and configure  the step dependencies as: 

D2.    Validation failed - Stop the process and send email

If the validation in point C failed, we want the process to stop and email the user. For this, create a send email step after the decision control step and configure the dependencies as: 

In the body of your email, type an appropriate email message notifying the recipient of the errors in the File Headers and the next steps to be taken to rectify the error. In the ‘Attachments’ section, attach a copy of the File Headers Validation file to the email to allow the recipient to identify where the inconsistent file headers lie.