Flat File Checker - Blog

Flat File Checker Validation using C#.NET

bulat@flat-file.net (Bulat Yapparov) — Thu, 22 Dec 2011 06:35:00 -0800

Business Case

The Data Feed project had a requirement to design and develop an interface to accept and parse the data file from ABZ Company.

The Data File was generated with data from multiple types of systems. Hence it was decided to use a Fixed-width file.
The data file format was defined to be fixed-width format. Here is an example of a fixed width file :

01732Juan Perez 004350011052002

00554Pedro Gomez 123423006022004

00112Ramiro Politti 000000001022000

00924Pablo Ramirez 033213024112002

Each field in the line has a start position and length defined. The challenge with a fixed width file is that each field has a defined length and any unwanted character can get easily ignored and result in an incorrect data.

So validation of data was very crucial for the success of the project.

Goals

To design a data-validation tool to validate the incoming data on a continuous basis:

validation engine must be configurable --- validation rules must not be hard coded.
data errors in a file must be documented and be available to be emailed to the sender.

Solution

The business rules against which you must validate the files must be made available to the FlatFileLibrary in the form of an XML file, which is created through the graphical user interface of the Flat File Checker (FFC) 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 that can be used for validation with FFC:

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

Add the Flat File Library to your solution

Add a reference of Flat File Library (FlatFilelibrary.dll (version 0.7.3.2) and Eval3.dll (version 0.6.4.0)) to your solution in Visual Studio. (http://www.flat-file.net/download.html)

Run the validation and process

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

using FlatFileLibrary;
using System.Threading;
using FlatFileLibrary.DataSources;


//Define the variables
private FlatFileSchema _files;
private AutoResetEvent do_checks =new AutoResetEvent(false);

//Add the event handler in the class constructor or during form initialization

_files = new FlatFileSchema(schemaPath);
this._files.Validated += new EventHandler<schemavalidatedeventargs>(FileSetValidated);

        public void  ValidateFile()
        {
            try
            {
                RunValidation();
            }
            catch (Exception e)
            {
                log.Error("Data Validation failed because :" + e.Message);
            }            
        }
        private void RunValidation()
        {
            // Use Flat File Checker user interface to create Schema file.

            do_checks.Reset(); //reset state    
            _files.RunChecks(); //don't store event from the lib     
            do_checks.WaitOne(); //Wait for FileSetValidated to set this event 
        }

Issues and Resolutions

Below is a list of the issues and how they were resolved:
1) Version differences: There were some changes in class names and namespaces between various versions of FFC. The code above works with the following versions of the 2 FlatFilelibrary dlls. FlatFilelibrary.dll (version 0.7.3.2) and Eval3.dll (version 0.6.4.0)
2) Writing errors to the database: FFC generates a text file with errors by default. In order to write errors to the database/customize the error file, you can use the following code:

public void FileSetValidated(Object sender, SchemaValidatedEventArgs e)
        {
            ValidationComplete = e.Result;
           try
            {
                foreach (FlatFile file in _files.DataSources)
                {
                    if (file.Errors.Count > 0)
                    {
                        IsDataValid = false;
              
                    foreach (DataError err in file.Errors)
                    {
                        foreach (IDataRule check in err.Checks)
                        {
                            //parse through errors 

                            StringBuilder errorLine= new StringBuilder();
                            errorLog = new SurveyDataFileErrorLog();
                            errorLog.Row = err.RowInSource;
                            errorLog.ColumnName = err.Column.Name;
                            errorLog.Value = err.Value;
     //Customize error messages.. 

                         if (check.DataRuleMessage.Contains("REGEX"))
                            {
                                errorLog.ExceptionMessage = "Invalid Format";
                            }
                            else {
                                errorLog.ExceptionMessage = check.DataRuleMessage;
                            }
                        }
                    }
                }
                    else{
                        IsDataValid = true; }               
            }
                }
            finally {
                do_checks.Set(); }
}

3) Asynchronous Data Validation: The Flat File checker validation is done asynchronously. The project requirement was to be able to do it synchronously and depending on whether the file was valid or invalid next function will be executed.

private void RunValidation()
       {
            // Use Flat File Checker user interface to create Schema file.

            do_checks.Reset(); //reset state    
            _files.RunChecks(); //don't store event from the lib     
            do_checks.WaitOne(); //Wait for FileSetValidated to set this event 
        }

FFC – Wish list & Next Steps

One of the features that I would like to see in the upcoming versions of the FFC is the ability to check if a file was valid or invalid in the RunValidation() method. Currently I had to set it during the event handler and read it from a global variable. Next Step: Assign the File Name to be validated at runtime. The following code was sent by FFC author FlatFilefile = (FlatFile)Schema.DataSource(); file.Path= ; Next Step: Validate file data with the existing data in SQL database .

Results

The Flat File Checker is a complete file validation tool with configurable validation rules. I was able to validate the files on a continuous basis and generate emails/error files without much effort.
Author: Pooja Lnu

v. 0.7.3.0 - New User Interface on the Main Form

bulat@flat-file.net (Bulat Yapparov) — Thu, 7 Apr 2011 14:16:00 -0700

Interface of the main form has been changed completely with a more intuitive and convenient layout:

source and fields are presented in the single tree which makes it easier to navigate and change schema;
rule builder made it to the main form and is not hidden within column details any more - this is a major change for everyday users.

I hope this new version of Flat File Checker will make a big difference to the usability and first impression of the application.

London Marathon 2011 for IT4CH

bulat@flat-file.net (Bulat Yapparov) — Mon, 24 Jan 2011 14:38:00 -0800

I decided to take a challenge of running my first marathon to raise £1500 for IT4CH that provides local hospitals with computers and IT support. Thanks to charity's effort children will continue to study and reach out to their friends and family via internet during the difficult period of their life.

Preparation for the marathon is very hard so any support will be very appreciated.

Thanks
Bulat

Data Quality as a set of consistent measures

bulat@flat-file.net (Bulat Yapparov) — Sun, 16 Jan 2011 18:04:00 -0800

Introduction
Managing Data Quality (DQ) of the information system is a complex and resource demanding task. Though it is often clear why cleaner database will bring more profit, it is not easy to take required steps to get desired result. I have been working on DQ initiative for some time now and though we are nowhere near the final goal, we were doing a good progress. Here I decided to share with you how I understand basic principles of DQ Management. If you think that data quality is the problem of your organisation then you can start from here. I am not getting into the project management aspects of DQ initiative here, as this article is just about the main DQ concepts.
Data Quality is a broad discipline that includes many tasks and preventive measures:

Standardisation

Profiling

Data Quality Assessment (DQA)

Matching and Merging (M&M)

Correction

Monitoring

Though all this measures are well known each organisation has to develop a consistent approach to implement these measures. The good way to start is to draw a broad DQ Strategy document that will explain how DQ will be measured, improved and monitored.

Data Rules or Business Rules
These are rules that define or restrict your business processes. Any data quality project should start from formal definition of these rules.

So the first thing you should do is to go to your users and ask them:

What business processes heavily depend on the database?

What data users need for their business processes?

What data will disturb or break business processes?

After you got answers to these questions you can:

Write a standard for data entry into the system;

Create a catalogue of rules that your data must conform to.

Catalogue of business processes and business rules is the most important part of Data Quality Management Initiative as it provides you with a clear view on what should be done.

Data Standards
While mainly working with CRM system I have created a standard for Customer Data which includes thorough definitions for all data for contact, address and telephone details:

Contacts

Title

First Name

Second Name

Sex / Gender

Date of birth

Addresses (depends on the country)

House Name

Address Lines (depend on countries if you have multi-national database

Postcode/Zip Code

Town

Country

Telephone

Country Code

Region Code

Number

Each data item should have a description for business users who key customer details plus technical definition i.e. regular expression.

It is worth mentioning that most contemporary CRM systems will have such standards imbedded in their functionality. However it can be the case that these rules are still ignored by users or data loads.

Data Quality Metadata

When your business rules are defined through the formal requirements gathering exercise it is time to:

Assess the quality of data in your system;

Correct data errors where possible to get improve results that you got through assessment stage;

Set up monitoring activity that will be base on regular DQA reports.

To get all this right you will require a meta-database that will store all data rules and also statistics for DQA;

The main feature of such database is that it will generate SQL for all required steps or store pre-written SQL as part of metadata.

Let's have a look at main tables that can be found in any DQ repository.

Scores or Processes

List of business processes that were chosen for DQ

Rules

List of rules that were formulated in requirements capture stage for DQ

This table can be extended by tables that store details for rules of specific types:

Not Null value

Regular expression

Relational integrity – searches orphan records

Custom rule – stores custom SQL that returns a set of erroneous records

Field comparison – checks that two fields have consistent values i.e. [Start Date]>=[End Date]

Any other type of rule if it frequently appears in the list of business rules.

The purpose of these extensions is to make it possible for the DQ solution to generate SQL basing on minimum of information in the metadata database. So if you want to set a Not Null constraint on one of the fields you just need to specify the attribute for that rule and system will generate SQL that picks erroneous records:

SELECT * FROM MyTable WHERE MyField IS NULL;

All you have to store in the extended table is the attribute ID that is linked to Attributes table.

Tables or Entities

Contains the list of all entities from the database

Fields or Attributes

Contains the list of attributes in entities from the database

Data Quality Reports

There are several reports that can be very useful for monitoring of DQ:

Process Level – Shows how many times business rule is violated in the database.

Entity Level – Shows how many records in table contain errors or missing.

Sub Entity Level – Shows how many records in table contain records within a specified group of records (i.e. Country in Addresses table).

Errors report – Contains details of erroneous records that violate business rules.

Reporting is essential for DQ management as it allows prioritising data corrections activities basing on number of errors and importance of business rule. It then allows to monitor the progress of DQ initiative and provides easy means to support initiative continuation or cancellation.

How to fix CSV data file

bulat@flat-file.net (Bulat Yapparov) — Sun, 9 Jan 2011 16:20:00 -0800

New functionality in Flat File Checker allows very intuitive and quick way to correct data in the text file (csv, etc.).

To correct data in a file you can set up validation rules in Flat File Checker and run validation (see how to set up validation of CSV file in previous post).

Then you can open data preview form by clicking on Preview in pop-up menu item of the the file:

Figure 1. Right click on the file and select Preview

This will open the Data Preview form that shows records from the source file with erroneous records being highlighted:

Figure 2. Form shows source file.

Now you can edit the values in the table to correct the source file. When you change values in the cells "Apply Changes" button becomes active:

Figure 3. Apply Changes button becomes active if values were changed

When you click on this button changes are save to the source file:

Figure 4. Changes were applied to the source file

Now you can re-run you schema and check whether previously erroneous values are still highlighted in the data preview form.

You can also delete records from the file by right-clicking on the corresponding records and selecting "Delete Record":

Figure 5. Delete record from the source file.

Record selected for deletion will be crossed out in the table and will be permanently deleted from the source file if "Apply Changes" button is clicked:

Figure 6. Record will be deleted from the source file if
"Apply Changes" button is clicked.

Preview Data with Error Highlighting

bulat@flat-file.net (Bulat Yapparov) — Sat, 18 Sep 2010 05:53:00 -0700

Data Error Preview and Error Highlighting are new features that will make application much easier to work with. This functionality allows to test data rules in the intuitive way plus it allows to test calculated columns.

This post shows how this new functionality can be used.

1. Preview delimited text data file before it is added to the schema:

Drag & drop sample data file into the new schema and click on the Preview Data button at the bottom of the File Definition from:

Figure 1. File Details Form

This will open data preview form:

Figure 2. Data Preview Form

2. Test rules after the validation has been run

Add rules to you schema and click on Run Checks button. After execution is complete right click on the file in the in the main form of the application and select Preview in the pop-up menu. This will bring the same form with errors being highlighted:

Figure 3. Data Preview with Highlights

If you have calculated columns in you file they will appear in the right-end of the table highlighted blue if value is correct or light violet if it contains error (see column DaysBetween in Figure 3 above).

Match Records on Number in the Address Line

bulat@flat-file.net (Bulat Yapparov) — Fri, 11 Jun 2010 15:10:00 -0700

Introduction

Usually Address Line 1 in the customer data contains house or flat number and we can safely match records using these numbers if postcodes are provided. This approach can significantly improve the match rate with minimum risk of false matching.

VB Expressions

You can easily extract the number and use it for matching in Flat File Checker with those functions:

LeadingNumber(string) - gets the number from the beginning of the string if it starts with number, returns original string otherwise;
TrailingNumber(string) - gets the number from the end of the string if it ends with number, returns original string otherwise;
GetNumbers(string) - gets all numbers from the string if it contains numbers, returns original string otherwise.

Address Line Example

Original file with customer details contains [Address 1] field. From this field we want to create a derived field that will contain either leading number or the original value if address line does not start with number. To create a field:

Right click in the Fields area on the Main form and select Add->Calculated Field in the pop-up menu;
Fill in field details following example on the Figure 1;

Figure 1. Extract Leading Number from Address Line.

Match Records

Unique constraint must be used to match records using our new derived field:

Figure 2. Unique Constraint Form.

Thesaurus Class (VB.Net)

bulat@flat-file.net (Bulat Yapparov) — Fri, 28 May 2010 09:52:00 -0700

Introduction
In some cases two values with similar meaning should be matched. I have created a Thesaurus class based on Hashtable to realise this functionality.

Source Code(vb.net)
Download thesaurus classes here.

Thesaurus File
The thesaurus source file is text file that contains a group of synonyms in each line separated by the specified char (see example file for nicknames with > used as value separator here).

Using the Code
Here is an example in which I compare two forenames using the Nicknames thesaurus file:

Public Sub test_thesaurus() As Boolean
 Dim t As New Thesaurus("Nicknames","c:\nicknames.txt", ">"c)
 t.Load()
 Dim s1 As String = "Robert"
 Dim s2 As String = "Bob"
 Return  (t.GetKey(s2) = t.GetKey(s1))
End Sub

Similarity Distance
One thing that can be also useful is distance between synonyms. Now you can specify distance value enclosed in square brackets in the thesaurus file. This functionality can be extended for score based matching.

AnyValue and NullValue

bulat@flat-file.net (Bulat Yapparov) — Wed, 26 May 2010 13:58:00 -0700

I came a cross a difficulty with matching duplicate contact records due to the issue with special values in some of the fields. For example Dr in the Titles field should match any other value - Mr/Sir/Mrs/Ms. On the other hand if Unknown was found in the address line it should not be matched to any other value including itself.

To overcome this problem I have added functionality that supports following parameters in Column node of the Unique constraint:
1. AnyValue - value that equals any other element --- if this parameter is set, then value in the field that equals to parameter's value will match values in all other records. By setting this parameter you will get more matches and therefore can increase the number of false matches.
2. NullValue - value that does not equal any element including itself --- if this parameter is set, then row where field equals to parameter's value will not match any other row in the file. By setting this parameter you will get more unique records and therefore less false matches.

Schema example for contact details matching:

<?xml version="1.0" encoding="ISO-8859-1"?>
<?xml-stylesheet type='text/xsl' href='http://www.flat-file.net/schema/file-set.xslt'?>
<Files ErrorLog="data.log" ExecutionLog="execution.log">
  <Synonyms>
    <Synonym Name="Nicknames" File="C:\match\nicknames.txt" Delimiter=">" />
    <Synonym Name="Titles" File="C:\match\titles.txt" Delimiter=">" />
  </Synonyms>
  <File Alias="Contacts" 
Name="contacts.csv" 
HasHeader="True" 
Path="C:\match\" 
StringQualifier='"' 
Delimiter=",">
    <Columns>
      <Column Name="ContactKey" Index="0">
        <Unique>
          <Action>
            <DupesReport Path="C:\match\duplicates.csv" />
          </Action>
          <Column Name="Address_Line1" />
          <Column Name="Postcode" Index="Hash" />
          <Column Name="NickName" NullValue='' />
          <Column Name="SoundSurname" />
          <Column Name="Gender" AnyValue='U' />
          <Column Name="Initials" />
        </Unique>
      </Column>
      <Column Name="Title" />
      <Column Name="Forenames" />
      <Column Name="Initials" />
      <Column Name="Surname" />
      <Column Name="Address_Line1" />
      <Column Name="Postcode" />
    </Columns>
    <Expressions>
      <Expression Name="NickName" Value="[Synonym('Nicknames',Field('Forenames'))]" />
      <Expression Name="SoundSurname" Value="[Soundex(Field('Surname'))]" />
      <Expression Name="Gender" Value="[Synonym('Titles',Field('Title'))]" />
    </Expressions>
  </File>
</Files>

The the source file for the schema above must contain the following fields in the header:

ContactKey,Title,Forenames,Initials, Surname, Address_Line1, Postcode

You can use both AnyValue and NullValue in the same field.

If you want to define more then one value of special type, then check thesaurus functionality which can be used in combination with AnyValue/NullValue to achieve this.

You can try thesaurus files that were used in the sample schema:

Match synonym values via custom thesaurus

bulat@flat-file.net (Bulat Yapparov) — Wed, 5 May 2010 09:42:00 -0700

Introduction

Sometimes different values have same or similar meaning making it more difficult to find duplicates with exact or even approximate string matching.

To help you to find more duplicate records a functionality that supports thesaurus files was implemented. Thesaurus files are the text files with groups of synonyms at each line separated by a specified character:

...
alex,alexandr,sasha
bob,robert
...

Similar approach was used by Oracle in search functionality for AquaLogic.

To get synonym from the thesaurus file for specific value the Synonym(string, string) should be used with following parameters:

name - name of the thesaurus file (see Figure 1);
value - string value for which synonym needs to be found in thesaurus.

Adding thesaurus functionality to the schema

1. Create thesaurus file(s) for titles, forenames, countries, etc. (see examples: Nickname, Titles)
2. Add thesaurus file to the schema.

Before using Synonym function you will need to add thesaurus file to the schema.

Open "Thesaurus Files" form through main menu Options->Thesauruses;
Specify thesaurus logical/alias name, file path and value delimiter (see Figure 1).

Figure 1. Thesaurus Files Form

3. Create calculated field.
After the thesaurus file is added to the schema, add new calculated field that will contain results of Synonym() function.

Figure 2. Calculated Field Details

4. Check computed field in the unique constraint

add Unique constraint to the primary key of the file;
double click on the constraint node and check the fields to be included in the data rule.

How to validate CSV file

bulat@flat-file.net (Bulat Yapparov) — Fri, 9 Apr 2010 09:32:00 -0700

Introduction

It is often required to validate a CSV file or other pseudo formatted data against a set of business rules. Though MS Excel offers data validation functionality for simple checks, it is often not enough for advanced data quality assurance.

Flat File Checker is the best option for data validation if you are facing any of those tasks:

Validation of large data file
Regular validation of files with same or similar structure
Validation against a set of complex data rules
Automated data validation / batch processing

It takes few easy steps to validate a file.

1. Install Flat File Checker

1.1 Download Windows installer from SourceForge.net.
1.2 Install application (click OK on all screens through installation process).
1.3 Open the application (FlaFi ->FlatFileChecker in the Start menu)

2. Create schema with a file

2.1 New Schema

Click on Create New Schema button in the main toolbar which will activate files and fields areas of the form:

2.2 Add File

Now Drag & Drop your data file to the files area of the form:

This will open a File Details form where you must provide your file details in the underlined fields:

After you click OK in File Definition form, File will appear in the Files area and fields from the header will be created and appear in the Fields area:

Now we can save the schema.

3. Add Data Rules

Here I will show how to add most common data rules.

3.1. Required

To disallow Empty/Null values in the field double click on it in the Fields list which will open Field Details form.

Then right click on the Rules node in the rule builder and select Add->Required in the pop-up menu:

3.2 Dates

Dates validation can be a bit tricky as time "goes" ahead and we need to appreciate its dynamic nature.

Let's say for example, that all contacts in our file should be at least 18 years old and we want data rule to work today, tomorrow or any time.

Therefore, we cannot just put static value, i.e. 08/04/1982 in the data rule. Thanks to Flat File Checker's support of VB Expression we can easily overcome this issue.

Double click one of the date fields in (if you have any in your file), which is Date_of_Birth in this example;
Right click on Rule node in rule build and select Add->Value Constraint;
In the appeared fill-in rule details:

Notice:

In this example value is enclosed in square brackets to indicate that it contains VB Expression:

Format(Now.AddYears(-18),'dd/MM/yyyy')

Finally we need to populate Date Format field and check Allow Null Values is it is OK for Date_of_Birth field to be blank:

Now click on OK button to keep all changes in the schema.

3.3 Regular Expressions

To add regular expression validation to the schema:

Double click on the field to open Field Details form;
Right click on Rules node and select Add->Value Constraint;
Select REGEXP as operator and provide regular expression (UK Postcode in the screenshot):

Click on Y button of the control and OK button of the form to save changes.

3.4 External Lookup

It is also common requirement that all values in the data file belong to a defined set of values.
If you have a lookup file that contains this values (vocabulary or primary data) then you can easily validate data against this file with Flat File Checker.

Drag & drop lookup file to files are of the Main form and populate file details (tick on Lookup checkbox).

Click on Links button in the main menu after the file is added to the schema.

In the Links form select MyData.Title as Foreign Key and Titles.Title as Primary Key and tick Enforce:

Click Add button to add link to the schema. Then press OK to close File Links form and save changes.

4. Execute schema

To validate file click on Run Checks button in the toolbar:

You will see progress bar while file is being validated which will be replaced by "CHECK COMPLETE" in the end of the processing:

5. View and correct errors

Now you can click on Show Errors button in the toolbar to open Data Errors form in which you can see and correct errors.

6. Other information sources on how to use Flat File Checker

6.1 How to thread on the Forum.
6.2 See online documentation here.
please feel free to contact me with any questions about Flat File Checker - info@flat-file.net

Version 0.7.0.6 Release

bulat@flat-file.net (Bulat Yapparov) — Mon, 15 Mar 2010 15:46:00 -0700

New Features:
1. Computable fields. This feature currently can only be used through direct editing of Schema's Xml.
To add field that will contain values calculated from other fields add Expressions and Expression nodes after Columns node before File node:

    </Columns>
    <Expressions>
      <Expression Name="UpperName" Value='[Upper(Field("Name"))]' />
    </Expressions>
</File>

In this example additional field will be added to the schema with Upper-Cased values from Name field.
This field is currently only available from Unique Constraint form in the GUI. But I am planning to make Expression fields similar regular fields, so they will be available anywhere in the GUI.
2. Soundex function added to functions that can be used in VB expressions.
3. Duplicate reporting action is added. This feature currently can only be used through direct editing of Schema's Xml.
To create report of duplicate records in the file add Action and DupeReport nodes in the Unique node:

4. Xslt transformation that allows to see schema in browser was slightly improved.

Bugs Fixed:
1. File lookup check box does not work
2. Field copy to file failed
3. Data validation stopped on data format
4. Command Line no longer taking Global variables

Download from:
1. User Group
2. Forum

Development plans for 2010. Make a Wish!

bulat@flat-file.net (Bulat Yapparov) — Fri, 26 Feb 2010 19:46:00 -0800

I want to get feedback to plan development in 2010.

From the end of 2009 I have stopped developing new features without user request, so if you think something is missing make sure you tell about it. There are still a lot of things that can be done to improve Flat File Checker.
I have number areas that I think should be developed/improved in 2010, but I am not sure whether they are relevant to you:

See descriptions for each point bellow.

1. Match & Merge (De-duplication)
a. Duplicates Report - implemented - see User Guide->Add Actions->Report Duplicates.
b. Fuzzy Logic / Heuristic
- Soundex - implemented - see example of a derived field with Soundex function in the User Guide->Calculated Field->Add Calculated Field;
- Levenshtein (distance between two strings)
- Dictionaries (matches between official names and nicknames, i.e.: Robert - Rob - Robbie - Robin - Rupert - Bob - Bobby - Bert.) - implemented - how to match records using special values;
c. Some kind of conditional logic if it is not too complicated, i.e. If Forename is missing, then check whether Initials are the same - implemented - see special values for matching.

2. Web Services (WS)
a. Basic functionality to upload Flat File Schema to the server.
b. Basic functionality to upload data files against Schema that is already on the server.
c. Run validation of the file set against the schema.

Thanks to Chris (RacerX64), who already spent his weekend (man, I hope you got bonus for that) to write the most of needed functionality, so it should take couple of days to accomplish this task. The biggest challenge with WS is to set up public server which could be available to everybody. If somebody can help with IIS hosting, it will be highly appreciated. IF WS is UP and running I will think of basic web interface for file upload and error reporting.

3. Data Error Log & Execution
a. Make HTML or XML+XSLT version of report that will be user friendly enough to send reports to data providers. - implemented. Now the output format of the log depends on its extension and can be Text, Xml or Htlm (see User Guide->Create New Schema->Log Formats)
b. Replace current execution logging with standard Tracing mechanism which is more flexible and robust way of logging.

4. Data File Preview
a. Implemented - Add a form to GUI that will show data from the file in the table with errors being highlighted.
b. Add a form for managing fields setting of the fixed position file.

5. Documentation
a. I want to make it easy to use and read. Please help me with this!
b. I have started shooting short videos to build up a tutorial. I think there is space for about 6 more movies. Please leave your feedback or produce your own video.
c. I need to make a sample schema and data set that everybody could see and play with.
d. If you use Flat File Checker extensively, please write an article or a business case and share it with other users. You can post them on the forum, in your blog, or here.

6. XSLT Schema
a. I don't know whether somebody uses IE to view the schema, but I find it quite handy. I want to change the way schema is presented slightly. Your thoughts on ways to improve transformation will be appreciated.

7. Data Rule Templates
a. The last, but not the least I need to create a GUI to create Query rules template files. This is a very powerful peace of functionality and I use it for data QA of marketing selections. Though it is possible to write template file from scratch or use Custom Query Xml as a base, it is not the easiest task and not something I like to do. Feel free to ask about Template Queries, while GUI functionality for them is under development consideration.
b. I’m also thinking about generic rule templates that will cover other then Query Rule templates.

Please post your wishes or vote here. You feedback is much appreciated!

UPD - June 2010:
I have done almost everything I wanted on duplicate records matching (releases after v.0.7.0.9), so will start working on improving of error logs and execution logs. Any requests are welcome.
UPD2 - June 2010:
Logs are now available in Html and Xml formats (releases after v.0.7.1.0)
UPD3 - Jan 2011
Now data preview is available which allows to edit original data files directly in Flat File Checker (see details here).

Video Tutorials for Flat File Checker

bulat@flat-file.net (Bulat Yapparov) — Thu, 11 Feb 2010 10:06:00 -0800

I am not sure whether this will be very helpful but I decided to make a number of videos that show how to use Flat File Checker.

Firstly, I want to thank guys who made and support CamStudio which was used to create these videos.

Secondly, I want you to see the amazing microphone that was used to capture my voice:

I hope you are impressed and ready to watch the videos:

How to create a new schema and add file:

How to add lookup and link files

How to create data rule

How to add regular expressions

I hope this will be helpful to start worknig with Flat File Checker. I am planning to create onther 4-6 video tutorials on other topics. If you have suggestions for these topics please let me know.

Batch Data Validation with Flat File Checker

bulat@flat-file.net (Bulat Yapparov) — Sun, 7 Feb 2010 12:28:00 -0800

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:

Eval3 wrapper

bulat@flat-file.net (Bulat Yapparov) — Sun, 7 Feb 2010 08:46:00 -0800

Title:       Eval3  wrapper
Author:      Bulat Yapparov, Chris Ellison
Email:       byapparov@gmail.com
Member ID:   5425273
Language:    Vb.Net
Platform:    Windows
Technology:  dotNet
Level:       Intermediate
Description: Evaluator for multiple VB expressions based on Eval3 library
Section      Type the Code Project Section you Wish the Article to Appear
SubSection   Type the Code Project SubSection you Wish the Article to Appear
License:     CPOL (default - select your desired license from our list)

Introduction

Eval3 library writen by Pascal Ganaye allows to parse and evaluate VB code, which is great but often it is not enough.
What I needed was a class that would interprit a string that contains multiple expressions inclosed in square brakets.
For example this string:
GIS_[Format(Now, 'yy-MM-dd')]-[Field('ID')]-[Field('Source')]
Will be evaluated to something like this:
GIS_10-01-13-5324523-DFS235F

Background

Lets assume we have an exprsion similar to the shown example above which we need to evaluate multiple times.
I have writen a class that will parse the whole expression once and store "formulas" for future evaluations.
This sighnificantly reduces execution time while keeping it really simple

Using the code

Download Code: Eval3 Wrapper; Evalutation functions

It is very easy to use EvalFormulaCollection class once you have Eval3 added to your project.
You just need to:

Create new instance of EvalFormulaCollection class with expression that you need to evaluate
Add "environment" object that provides functions that can be used in expression
Call Initiate() method that will make reusable array of parsed "formulas".
Call Value() property to get the interpreted value of the expression which is evaluated without parsing!

Code example:

' Test evaluation of the expression with multiple VB Expressions
Sub Test()
 Dim c As New MyClass
 dim e as New evaluationFunctions
 Dim expression As String = _ 
     "GIS_[Format(Now, 'yy-MM-dd')]-[Field('ID')]-[Field('Source')]"
 Dim ev as New EvalFormulaCollection(expression)
 ev.AddEnvironment(c)
 ev.AddEnvironment(e)
 ev.Initiate()
 MsgBox ev.Value()
End Sub

Points of Interest

The best thing about this code is its size. It is quite impressive what you can do with Regex class from RegularExpressions library with one line of code:

' Find all expression in square brackets
Public Sub New(ByVal expression As String)
     ...
    _matches = Regex.Matches(_expression, "[\[][^\[\]]{1,}[\]]")
End Sub

Version 0.7.0 is released

bulat@flat-file.net (Bulat Yapparov) — Fri, 5 Feb 2010 08:24:00 -0800

New Functionality

1. Option to overwrite schema attributes in command line
Now you can parameterize you schema and control any of its attributes through command line parameters.

I you want to pass value to the schema through command line you need to do the following:
1) Add Global Variable (in Options) to your schema to define a part of the file name i.e.: filesufix
2) Change file name in the schema to something like this:
Name="MyFile-[Constant('filesufix')].csv"
3) Call FlaFi in command line like this:
C:\Program Files\Flat File Checker\FlaFi "SchemaPath.xml" -filesufix 20100124

Does any other application have something cool like that?

2. Drag and Drop Rules
Now you can drag & drop rules in the rule builder which is an amasing experience!

3. StdOut Show Errors

Now you can use -errors token in the console version of the application to echo data errors.

Bug Fixes

Command line execution not returning errorlevel
"Has Header" checkbox not preserved
Global Variable without value - unhandled exception
Start and End positions are not saved.
Start Column default value
Custom Query Screen - improved interface
Mid function in the VB Expression
Constant not working in data and error file specifications

See project's issue tracker for details.

Join project group to dowload this version!

How to make data exchange work?

bulat@flat-file.net (Bulat Yapparov) — Sat, 30 Jan 2010 12:40:00 -0800

Introduction

Organisations need to get data from external sources and load it to a database. With outsourcing of processes this need is growing fast. Customer databases (CRM) base the core for business processes and data management becomes the priority. It is common that data is exchanged within text files in pseudo formats (.csv, .psv, fixed position) and loaded with special utilities.

Though the process may look simple, it often fails to work. The reason behind this is the difference between business rules and database schemas between organisations.

The ETL (Extract, Transform, Load) process is not that simple because tables, fields and constraints are not the same for two different systems. Moreover, data quality of external sources is often inadequate and causes problems.

Prepare lookup files

Create data files that contain internal codes or master data from your system against which data providers will be able to validate exported data. Such file could contain:

Product codes
Titles
Marketing campaigns
And other reference data.

Create the specification

Data exchange is based on a specification that can be used by database programmers to understand the format of the data in the file. Therefore, it should be a well structured document with description of all tables and fields. Descriptions can be formal (based on programming language) or informal. Flat File Checker provides an easy way to build such schema in which each data element (table, field) can be defined by both data rule and free text comment. The benefit of using Flat File Checker is that this schema is both readable and executable.

Build your knowledge

With FFC you can easily extend schema without writing any code. It is important to add new rules to the schema each time it fails to catch errors.

Let them do it or automate it

As soon as your specification is ready you can delegate data validation to your data provider by just sending them the schema and the link to the Flat File Checker's download page.

If you need to validate data yourself you can use command line version of the application to automate the process.

Version 0.6.9 is released

bulat@flat-file.net (Bulat Yapparov) — Fri, 11 Dec 2009 06:59:00 -0800

Version 0.6.9 includes a feature that hopefully will make you scream with joy.

Finally it is possible to correct erroneous values directly from Flat File Checker with out going to any text editors or MS Excel.

Lets see how it works:

1) After the validation was complete we open the data error list () :

2) Change one or more values directly in the grid:

Now few things are new on this form

changed values are highlighted with blue background;
message in the left-bottom conner tells how many values were changed;
"Apply Changes" button allows to apply corrections done in the form to the original data files.

3) After we click on "Apply Changes" button few things will happen:

Corrected values will disappear from the errors list;
"Apply Changes" button itself will disappear or become disabled;
Message will show the status of file update:

Hope this new feature will streamline the work with data in flat files even more!

Windows installer can be downloaded here.

Version 0.6.8 is released

bulat@flat-file.net (Bulat Yapparov) — Sun, 6 Dec 2009 05:37:00 -0800

New features in v.0.6.8:

Global Variables
It is now possible to define global variables that are accessible from different parts of the schema.

To add new variables or edit existing ones click on Options->Global Variables in main form menu:

In the Global Constant Variables form fill in names of variables and their values.

Now you can use these values in different places of the schema through Constant function in VB expression, i.e.: [Constant('AgencyCode')] will be replaced with "RK".

This functionality can be useful if you want to use same schema for data files where some parameters change regularly.

Copy objects
Now it is possible to copy existing files or fields through GUI.
For example you can copy field with all rules from one file to another:
Copy field:

Paste field:

Freeware and Motivation

bulat@flat-file.net (Bulat Yapparov) — Thu, 26 Nov 2009 08:13:00 -0800

You have probably seen a good open source project getting abandoned (CamStudio, NDoc and many more) by developers due to poor motivation to continue. I have been thinking whether there is a way to sustain the motivation, and have explored the examples of both open source and proprietary software projects on the subject.

The findings of my research are easy to understand, but difficult to accept:

Developers need external motivation to keep the project running after the "magic" of the initial idea is gone.
Users only contribute when they have a clear incentive to do so.

Keeping this in mind, I have decided to create a brief outline of the main factors that can help any open source project to stay alive.

Developers' Motivation to Contribute
At the start of the project, I as a developer was motivated by the mere idea that Flat File Checker can optimise the way organisations exchange data, improve the quality of data and spare the data people from mundane and tedious manual work. As the project moved ahead, it brought about loads of little tasks that I was happy to solve as they came. I have extended my knowledge in various areas of application design and data quality, and this alone was quite exciting.

Today, as the development phase has essentially passed, my motivation has become much more prosaic. Now I have to face a different set of goals such as marketing and promotion, and recruiting new users is not something I like to do, so obviously just running the project for fun does not make sense to me anymore. What would be the alternative source of motivation? In theory, a developer expects two main benefits from his work: recognition (of his efforts, intelligence etc.) and revenues. In my case, these two would be: feedback from users (so I know that I don't waste my time) and earnings sufficient to cover project expenses including my precious time.

Users' Motivation to Contribute
The user's motivation to use the application is quite straightforward: it depends on the application's ability to efficiently solve the given task. However, when it comes to motivation to contribute, there is none even when the user is happy with the application.

Get Your User Involved
It seems that an open source project is unlikely to progress when the user community does not support it. Without users contributing to the project, developers feel frustrated about their apparently meaningless work. As a result, either the project is dropped, or developers start charging.

Since neither of these options appeals to me, I kept trying to figure out how to motivate users to provide feedback or to make donations to keep the project going. The next section of the post lists the conclusions I came up with; I also include a diagram that helps illustrate my thoughts.

Framework

H1: User is more likely to spent more money if he can save time by doing it.
Vendors request users to register every year and this is a good artificial way to make user to spend time.

H2: User is more likely to spent more money if he will get a better product for it.
This is a microeconomics rule for most of the products.

H3: User is more likely to spend time on the project if he will get a better product for it.
H3 is the same as H2 if we can find the price of user's time.

H4: User is more likely to spend time on the project if his effort is recognised.
This is a behaviour dogma that is used in many social web projects (digg, facebook, etc.).

H5(A): Developer's perceived recognition depends on how much time users spend on the project.
That is my personal opinion which can be tested.

H5(B): Developer's motivation depends on his perceived recognition.
H5 is similar to H4 but applied to developer.

H6: Developer is more likely to work on the project if he gets money for it.
Conclusion derived from motivation theories but can be tested.

A priory statement that defines user contribution to the project:
A1: User needs to spend time or donate to make a contribution. Therefore, user's contribution consists of time and money spent on the project.

Here is the diagram that represents set of hypothesises that were discussed above:

So what I want from a user boils down to contribution that will motivate me to continue working on the project. And to motivate users to spend their time or money (see A1) on the project I need to give them better version of the product and recognition for their contribution. Simple market exchange is healthy for both developers and users. So if user does not get involved then he does not get the best available version and his name will not be on an "I saved the world today" list.

Definition of Variables
User contribution - is measured in points that will be credited for any activity beneficial to the project, such as: donation, submission of a bug, feature request, link from blog, etc.
I think that the points based system is the most appropriate, though a bit complicated. The problem is to define how exactly points should be credited. As an old book wrongly says - "People only do for what they are paid for" (If no book says such things, at least some book says about money and motivation).

Let's see a theoretical example of how contribution can be calculated in points:

$1 donation - 1 point.
Submitted bug - 5-20 points (on SourceForge.net)
Feature request - 10 points (on SourceForge.net)
Links from relevant web page or blog - 10-100 points (to project's website page; rated by Website Grader)
Business case- 50-100 points (submitted on the Forum)
- Name of the organisation
- Description of how software is used and what problems it solves
Allow to use organisation name and logo on "Users" page - 50-150 points
Join the project - 10-200 points
- Programmer;
- Technical writer;
- Translator.

Note: All this points are subject to conditions. For example if feature request is absolutely irrelevant, then points for it will not be granted. Also where credited points are given as range, it is developers right to decide the final number of points that will be credited to the user for specific action. I will need to spend some time to finalise my estimates of each action before this "point based system" will appear on the main page of the website.

Donation - Money spent in $

Product's Quality - Release delay in months.
This is an open project and I don't want to produce cripple-ware versions with limited functions and make users to pay for "pro" versions. So the only option I see is delays in release times. Users who got involved or donated to the project will get new releases first without any delays, while other users will get releases with a delay. Though this model is not exactly what open source is about, I think it is fair.

So application release delay depends on how much points you have:

**Fairware* "price" structure**
Release delay (months)		User Contribution (points)
0		199
1		99
3		49
6		0

* Modified version of open source software, in which user's contribution towards the project gives him access to the most recent versions.

So if user did not contribute to the project his version of software will be maximum 6 months out of date. I think this is a fair play and would like to hear what other people think?

Developer's Motivation - Can be measured through developer's performance, which can be in its turn measured by the number of lines of code written within a period. Though this is a primitive measure (often I would rather "bonus" myself for reducing the number of code lines) it is easy to understand and to measure.

Conclusion
this was just a though and I am sure that I am not going to implement all that. Managing such system within one open source project will impossible and pointless as all this is based on hypothesis.

However, I have decided to make new releases available only to the smaller group of users who are ready to register to download the application. Moreover, license for these latest releases is slightly different from basic GNU Licence and requires commercial users of application to contribute.

This article was an attempt to define fair-ware instead of donation-ware, which is a concept that does not work to well in the real world (list of examples includes all communist countries). It would be nice if SourceForge and other software hosting providers had such functionality to support developers and award users who make contributions.

API Documentation

bulat@flat-file.net (Bulat Yapparov) — Tue, 24 Nov 2009 10:30:00 -0800

This week I was working hard to produce API documentation for application's core DLL - FlatFileLibrary.dll. It takes quite a while to feel in all the gaps in the comments but I am nearly there.

On-line API documentation is now available, which will help if you are using the code in your project.

This documentation is still not complete but it is evolving rapidly. As Flat File Checker is at its Beta stage of development, names of classes and their members can change. Everybody is invited to review the code and the documentation. You can leave your comments or questions on this post or send me an email.

This documentation was created with Sandcastle that is why it looks so good.

Version 0.6.7 is released

bulat@flat-file.net (Bulat Yapparov) — Sat, 14 Nov 2009 09:52:00 -0800

New in 0.6.7 release:

Data rule based on VB Expression:

Validation of fixed position files. Field position in the line can be defined in the Field Details form:

Version 0.6.6 is released

bulat@flat-file.net (Bulat Yapparov) — Sun, 8 Nov 2009 09:31:00 -0800

New in v.0.6.6 release:

Validation of files without headers;
Field index in Field details form;

Default folder for file set is shown in File Definition form:

Version 0.6.5 is released

bulat@flat-file.net (Bulat Yapparov) — Sat, 31 Oct 2009 04:04:00 -0700

New in 0.6.5 release:

Custom query form - validate values in the file against external database.

Compare fields form - compare fields within the file set.