Avatar of cetafudd

asked on 

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:

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

Visual Basic Classic

Avatar of undefined
Last Comment
Avatar of GrahamSkan
Flag of United Kingdom of Great Britain and Northern Ireland image

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
    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
                    If InStr(strRule, "Field") > 0 Then
                        ParseLine = OtherFieldValue(strRule, strFieldData) 'needs a function written
                        ParseLine = False
                        MsgBox "Unrecognised rule; " & strRule
                    End If
                End If
        End Select
        If ParseLine = False Then
            Exit Function
        End If
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
Avatar of cetafudd


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?

Avatar of GrahamSkan
Flag of United Kingdom of Great Britain and Northern Ireland image

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.
Avatar of cetafudd


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???
Avatar of GrahamSkan
Flag of United Kingdom of Great Britain and Northern Ireland image

Blurred text
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
Visual Basic Classic
Visual Basic Classic

Visual Basic is Microsoft’s event-driven programming language and integrated development environment (IDE) for its Component Object Model (COM) programming model. It is relatively easy to learn and use because of its graphical development features and BASIC heritage. It has been replaced with VB.NET, and is very similar to VBA (Visual Basic for Applications), the programming language for the Microsoft Office product line.

Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews


IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo