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.csv5) run validation: C:\Program Files\Flat File Checker\FlaFi {Schema Path}
-campaign SRP54ALet's have a look at the most important function of the script - ValidateFileUsing the code
Download the solution and see instructions filefor 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:
Posts
RSS Feed