Click here to Skip to main content
Click here to Skip to main content

Validating data with Flat File Checker

, 30 Oct 2009
Rate this:
Please Sign up or sign in to vote.
An article on data validation with a flat file schema generated in Flat File Checker.

Introduction

It is often required to validate flat files before importing them to the database, or after exporting data from the database. Flat File Checker allows you to validate an inter-related set of flat files (*.csv, *.psv, *.txt, etc.) without having to parse them directly in your code.

The business rules against which you must validate the files must be made available to the FLaFi library in the form of an XML file, which is created through the graphical user interface of the FlaFi application. Here are examples of such rules:

  • Value in the field is Required
  • Field is a date with a given format and within a given range
  • Field structure must match a Regular Expression
  • Two files are linked [relational links]
  • Field must be compared to another field
  • Unique constraint is imposed on one or several fields
  • Validation must be made against values stored in a database

The task of validation can be now addressed in a few easy steps:

  • Create a Flat File Schema (XML file that contains business/data rules for files that you want to validate)
  • Add the Flat File Checker library to your .NET application
  • Run validation with minimum code within your application

Background

The idea behind Flat File Checker is to create an application that can validate structured data in a text file based on schema stored in XML, in a way similar to the way XML files are validated. This approach separates data/business rules from the validation itself, and thus considerably less coding is required.

Creating the schema

Here is a quick way to create a schema file to use for validation by FlaFi:

  • Download the installer of the latest version of Flat File Checker from SourcForge.net
  • Install the application and start the application
  • Create a new schema using the application and save it locally in an XML file

When the schema is created, you can view it in browser as a readable data exchange specification:

schema_sample.jpg

Add the Flat File Library to your solution

Add the Flat File Library (see link at the top) project to your solution in Visual Studio.

Run the validation and process errors

To use the Flat File Library, you will need to use these main classes:

  • FlatFileSchema - The class that contains a collection of files
  • File - The virtual class that gives access to several objects associated with a flat file: columns, errors, etc.
  • DataError - The class that contains details of the data error

You can also include system threading to your project as validation is done in asynchronous mode:

Imports System.Threading

The following code shows how to start validation (the RunValidation sub) and process errors if needed (the FileSetValidated delegate sub).

'-----------------------------------------------------------
'                 VB.Net Example
'-----------------------------------------------------------
Private WithEvents _files As FlatFileSchema
Private do_checks As AutoResetEvent
' Start validation
Sub RunValidation()
 ' Use Flat File Checker user interface to create Schema file.
    _files = New FlatFileSchema(<Schema file path>)
      do_checks=_files.RunChecks()
End Sub

' Feedback errors when file(s) validation is complete
Private Sub FileSetValidated(ByVal sender As Object, _
ByVal e As SchemaValidatedEventArgs) _
                  Handles _files.Validated
 Dim file As FlatFile
 Dim err As DataError
 For Each file In _files.Files
       For Each err In file.Errors
          ' Feed back error to users here
          ...
       Next err
 Next file

End Sub

Points of interest

The console version of Flat File Checker (code attached) makes it possible to run validation from command line with just one line:

c:/program files/flat file checker>flafi.exe <schema path>

cmd_sample.jpg

License

This article, along with any associated source code and files, is licensed under The GNU General Public License (GPLv3)

Share

About the Author

byapparov
Database Developer
United Kingdom United Kingdom
No Biography provided

Comments and Discussions

 
QuestionValidation errors Pinmember.NET_Labview11-Oct-11 4:00 
Excellent Article apparov. But I am running into an issue. When I run the my validation through FlatFileChecker API I get no issues. But When I run though my C# application I get this execution error
 
"
 Flat File Schema Error Message: Failed to load the file due to wrong line format. Column index is not defined in file. Please check column names in the XML Schema definition. File format is wrong. 
"
 
Here is how my schema look like :
 
<?xml version="1.0" encoding="ISO-8859-1"?>
<?xml-stylesheet type='text/xsl' href='http://www.flat-file.net/schema/file-set.xslt'?>
<ValidationSchema ErrorLog="data-erros-[Format(Now,'yyyy-MM-dd-hh-mm-ss')].htm" ExecutionLog="execution-errors-[Format(Now,'yyyy-MM-dd-hh-mm-ss')].htm">
  <File Alias="SampleFile" Name="SampleFile.txt" HasHeader="False" Path="****************\My Documents\" Type="Fixed">
    <Columns>
      <Column Name="RebateReportingYear"  StartAt="1" Length="4">
        <Required />
      </Column>
      <Column Name="SourceSystemPlatform" StartAt="5" Length="20">
        <Required />
      </Column>
      <Column Name="RosterID" StartAt="25" Length="15">
        <Required />
      </Column>
      <Column Name="CustomerSubscriberRosterID" StartAt="40" Length="15">
        <Required />
      </Column>
      <Column Name="MLRCaseSize" StartAt="55" Length="7">
        <Required />
      </Column>
      <Column Name="LegalEntity" StartAt="62" Length="10" />
      <Column Name="LegalEntityDescription" StartAt="72" Length="100" />
      <Column Name="SitusState" StartAt="172" Length="10" />
      <Column Name="PolicyNumber" StartAt="182" Length="15" />
      <Column Name="CustomerName" StartAt="197" Length="60" />
      <Column Name="CustomerAddressLine1" StartAt="257" Length="60" />
   
    </Columns>
  </File>
</ValidationSchema>
 
I am so close to automating my validation process. Please help
 
Thanks
AnswerRe: Validation errors Pinmemberbyapparov1-Nov-11 7:47 
GeneralFlat File Checker API Pinmemberbyapparov1-Feb-10 9:18 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140814.1 | Last Updated 30 Oct 2009
Article Copyright 2009 by byapparov
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid