Smart Parsing and Validation Schema
Posted on 2006-05-03
I am working on an application where I receive text files from different sources:
Here is one record of one of the files:
I also have tables in SQL 2000 containing the parsing schema and validation rules. For this type of file (above) I have the following records in a table (fields are: FieldOrder, FieldName, StartPosition, Length, Rules):
1 OrderNumber 1 6 ANNNN
2 OrderDate 7 10 MM/DD/YYYY
2 OrderDate 7 10 <=today
3 ItemNumber 17 4 NNNN
4 Quant 21 3 NNN
4 Quant 21 3 >0
5 UnitPrice 24 10 NNNNNNNNNN
5 UnitPrice 24 10 >0
6 TotalInvoice 34 10 NNNNNNNNNN
6 TotalInvoice 34 10 =Field4 * Field5
Where: A = alpha, N = number, etc
As you can notice a field can have more than 1 validation and some of them like Field 6 require access to other fields in the record (TotalInvoice = Quant * UnitPrice).
I am trying to create a routine in VB 6 to handle the parsing and validation based on the schema from the database so when the schema is changed (new fields, delete fields, new validation rules, etc), or a new schema is added to the database no need to change the program.
The VB 6 program would basically do this:
- open text file – I know how to do this
- read DB to identify schema (the one described above as an example) – I have several schemas – I know how to do this
- read each line of the text file and be smart enough to identify each field (X47222 is the ordernumber and so on) and do the validation based on the schema retrieved from the database. – I don’t know how to do this one…
If you think there is a better way to do it I am completely open, as long as the schema is on the database and the code is written in VB6.
Thanks in advance..