Batch Data Validation with Flat File Checker

Introduction

In my previous article, I 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 your 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 the 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 a better environment for debugging and logging.

See forum for more information about Flat File Checker command line execution:

• Command line execution
• Automate data validation process

CodeProject