asked on 5/3/2006

Smart Parsing and Validation Schema

I am working on an application where I receive text files from different sources:
Here is one record of one of the files:
X4722204/29/2006900100200000019990000003998

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..

ceta

Visual Basic Classic

Last Comment

GrahamSkan

5/5/2006

GrahamSkan

5/4/2006

I think that the core of your problem is how to organise the parsing of each field.
I suggest having a function for each rule, or group of rules, if some can be grouped. You would step through each field specification get the relevant part of the data string, and call the applicable function, something like this:

Function ParseLine(strLine As String, rsParseTable As Recordset) As Boolean
Dim strFieldData As String
Dim strRule As String
ParseLine = True
rs.MoveFirst
Do Until rs.EOF
strFieldData = Mid$(rs.Fields("StartPosition"), rs.Fields("Length"))
strRule = rs.Fields("Rules")
Select Case strRule
Case "<=today"
ParseLine = IsBeforeToday(strFieldData) 'Suggested function provided below
Case "MM/DD/YYYY"
ParseLine = IsDate(strFieldData)
Case ">0"
ParseLine = IsGreaterThanZero(strFieldData) 'needs a function written
Case Else
If Len(Trim$(Replace(Replace(strRule, "A", ""), "N", ""))) = 0 = 0 Then 'Only "A"s and "N"s
ParseLine = IsAlphaNumericPattern(strRule, strFieldData) 'needs a function written
Else
If InStr(strRule, "Field") > 0 Then
ParseLine = OtherFieldValue(strRule, strFieldData) 'needs a function written
Else
ParseLine = False
MsgBox "Unrecognised rule; " & strRule
End If
End If
End Select
If ParseLine = False Then
Exit Function
End If
rs.MoveNext
Loop
End Function

Function IsBeforeToday(strDate As String) As Boolean
If IsDate(strDate) Then
If DateDiff("d", CDate(strDate), Now) > 0 Then
IsBeforeToday = True
End If
End If
End Function

cetafudd

5/4/2006

ASKER

Thank you for replying Graham.. very nice code, I am sure I could use it.
I still have a problem here, how do I validate field number 6 since it depends on 2 other fields? I can't have something hard coded because today the rule is that it has to be equal to Field4 * Field5, tomorrow could be Field4 * Field5 + 10% and so on....It has to be more dynamic so I dont have to change my VB code every time a rule changes or new rules are created.
We would have basically 2 sets of rules:
1) field formatting and datatype (those are field size, numeric, alpha, combination, dates, etc)
2) field value - this can also involve some calculation like:
a) a math method against this or any other record in the current record (like Field 6)
b) condition...yikes....I will explain what I have in mind:
example of rule for field10: #field5 > 100,field5 * 0.10,field5 * 0.07#
this (or any other convention) would tell me that the correct value of field 10 will depend on the value of field 5, if field5 greater than 100 field 10 is 10% of it, otherwise it is 7 %....and so on

It is more like having a generic routine in VB that would interpret the rules and do the right validation..
If I am able to accomplish such code, I could change the rules for field10
from #field5 > 100,field5 * 0.10,field5 * 0.07#
to #(field5 / 2) + field57 #
or have my rule for another field like #at(1, 4, 6, 15)# (the only valid value are 1, 4, 6, or 15)....and so on...

Again, this design is still open and it can be changed as long as I keep the templates and rules in the DB and code is VB 6.

What do you think?

GrahamSkan

5/4/2006

If the maths is limited to a fairly strict pattern, you could write your own evaluator, otherwise you could consider using the Eval function that is available with the Microsoft Script control.

cetafudd

5/4/2006

ASKER

It will follow a fairly strict pattern - could you provide some code based the examples I mentioned?
Besides the maths would I be able to do the conditions I mentioned on item 2???

ASKER CERTIFIED SOLUTION

GrahamSkan

5/5/2006

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial