Sunday, February 7, 2010

Batch Data Validation with Flat File Checker

Title: Batch Data Validation with Flat File Checker
Author:      Bulat Yapparov, Chris Ellison
Email:       byapparov@gmail.com
Member ID:   5425273
Language:    VB.Net, VBScript
Platform:    Windows
Technology:  VBScript, Flat File Checker, Scheduled Tasks
Level:       Intermediate
Description: This is a real life example of data validation using conslole version of Flat File Checker and VBScript
Section      Database
SubSection   Database
License:     CPOL (default - select your desired license from our list)

Introduction

In my previous article I have mentioned that data validation with Flat File Checker can be done through command line.

Chris Ellison wrote a brilliant VBScript based solution (download) that allows validating multiple data files using different Flat File Checker schemas depending on file name prefix.
This script can be used as a source of inspiration if you want to automate your data validation process.


Background

Sometimes it is required to validate many similar data files, which is not possible through Flat File Checkers GUI.
However, there is an easy solution for that with console version (FlaFi.exe).

Main console tokens are parameters that you can pass to console application:
-silent - executes schema without echo to console window and closes console when validation is complete.
-errors - echoes data errors to console window.

Custom tokens are user specified tokens that can be used to pass values to control schema's attributes. Custom tokens allow parameterising the schema and reusing it for similar data files. You can pass values through command line and change parameters of validation i.e.: file name suffix, product code, dates, etc.
All you need to create a custom token is to:
1) add Global Variables to you schema.
2) call FlaFi with command that has the following format:

C:\Program Files\Flat File Checker\FlaFi {Schema Path} -{Variable Name} {Variable Value}
3) add global variable named "campaign" to the schema.
4) use "Constant" function in VB Expressions for any attribute in the schema:
FileName=[Constant('campaign')]-import.csv
5) run validation:
C:\Program Files\Flat File Checker\FlaFi {Schema Path} 
                                                   -campaign SRP54A
Let's have a look at the most important function of the script - ValidateFile

Using the code

Download the solution and see instructions file
for more details on how to try the code.
ValidateFile function has following parameters:
strFolder - Folder that contains schema files
strSchema - Schema file name
strFileDir - Directory that contains files
strFile - File Name
strLogFileDir- Folder that contains logs

Private Function ValidateFile (strFolder, strSchema, strFileDir, _
                               strFile, strLogFileDir)
 'Execute FFC and return 1 of 4 return values
 'FFC ERRORLEVEL 0 = "VALID" File Valid
 'FFC ERRORLEVEL 1 = "DATA" Data Exception
 'FFC ERRORLEVEL 2 = "EXEC" Execution Exception
 'FFC ERRORLEVEL > 2 = "SEVR" Severe Error
 dim sh
 dim strCommand
 dim return

 ValidateFile = "NO_SCHEMA"

 if SchemaFileExists(strSchemaFolder, strSchema) then
 Set sh = CreateObject("WScript.Shell")
 WriteLogEntry objLogFile, "Validating File " & strFile & _
               " against FFC Schema " & strSchema
 strCommand = Chr(34) & strFlatFileExe & Chr(34) & " -silent " & _
   strFolder & strSchema  & " -InFileName " & strFile & " -InFileDir " & _
   strFileDir & " -InLogDir " & strLogFileDir
 WriteLogEntry objLogFile, "Running Command " & strCommand
 'wscript.echo "Running Command " & strCommand

 return = sh.Run(strCommand, 0, True)

 if return = 0 then
       WriteLogEntry objLogFile, _
         "File Successfully Validated Return Code = " & return
    ValidateFile = "VALID"
 else
  if return = 1 then
   WriteLogEntry objLogFile,"Data File Validation Failed, Return Code " _
    & return
   ValidateFile = "DATA"
  else
   if return = 2 then
    WriteLogEntry objLogFile,"Execution error, Return Code " & return
    ValidateFile = "EXEC"
   else
    WriteLogEntry objLogFile,"Serious, Return Code " & return
    ValidateFile = "SEVR"
   end if
  end if
 end if
 set sh = nothing

 else
 ValidateFile = "NO_SCHEMA"
end if
end function


Points of Interest

It will be useful to transfer this solution to VBA which provides better environment for debugging and logging.
See forum for more information about Flat File Checker command line execution: