The verification log file provides multiple columns that allow you to track down all verification exceptions that occurred during your script execution. The column values are:

• OccurredAt - A timestamp for the time the exception took place

• BatchId - A pseudo random ID that is created when the CSVTable is created to provide the same ID for each verification exception for a specific run

• JoinId - blank for the master table or the join Id used when creating the join

• VerificationId - ID of the registered extension, this will be empty for Column Value Processors implemented as closures

• FunctionName - The extension function name if applicable

• RowIndex/ColumnIndex - The 0 based row and column where the exception occurred

• Offsets - The <begin>:<end> byte offset within the file for the row. This allows you to read the line from your file using a script

• Message - The exception message

It is important to realize that rowIndex and columnIndex values are 0 based numbers. When analyzing a verification log, remember that a rowIndex != lineNumber.

To find the physical line in the source file, you must take the rowIndex and add 1 if there is a header line and 1 more to account for row indexing being 0 based.  For the first example above, the verification occurred on rowIndex 43. That means you should look on line 43+ 2 = 45 in the 4th column as the source file had a header line.

If there is a mismatch between the number of columns vs column headers in a line, you will received the following parse error and no value in the output file. It is critical to review your logfile to make sure all errors are resolved.

Download the parseVerificationLog.groovy script attachment to parse through the verification log. Filters are provided to process a limited number of lines:

• batchRunId - The batch run ID you want to focus on

• rows - Zero or more rowIndex values

• rowIndexBetween - List size of 2, for example: [1,100] for rowIndex between 1 and 100

• verificationIds - Zero or more verification IDs, for example: "FileViewTable_0001"

• excludeVerificationIds - Zero or more verification IDs that are not to be included

Examples:

batchRunId = 721216942
rows = [35,92]
rowIndexBetween = [1,1000]
verificationIds = [FileViewTable_0001,FileViewTable_0002]
excludeVerificationIds = [FileViewTable_0001,FileViewTable_0002]

A file name batchRunFiles_<batchRunId>.map will automatically be created in the same directory as your log file and contains the master table and all of the join tables. This file allows the script to find the input file(s) for a specific exception automatically to print out the line details along with the exception message. These files can be deleted over time as they are only required for log analysis using the script.

For this example run I was interested in a few specific "rows" that had non-ascii values. Using the rows filter to filter on rowIndex and pickup specific verification IDs, I was able to quickly verify the line information. Notice that "character at index" gives you a zero based offset into the column where the non-ascii character was found.

rows = ["274","3138458"]
verificationIds = ["FileViewTable_0001"]

Row at index 274, column 2 is "LA CAￃﾱADA FLￃﾱINTRIDGE" which has an invalid character at index 5 (and a few more) as shown.

• (7 KB)