Parsing On The Fly

[MoonMullen]

In MS-Access97, can you parse a text file with VBA code one line at a time? I mean read part of a line, say first 15 characters, then parse that into five fields and create the first record in a table. Then repeat the whole process for as many times as is needed. I currently read the text file in and then create a new text file by writting out each line to disk. I then import and parse the text file I just created to make a table. Can this proscess be streamlined by parsing on the fly? If so, how would you code it? All help would be greatly appreciated. I

 

[gwinn7]

Ouch! Yes, there is a much better way. My example is obviously not the only way or even necessarily preferred...

The following example code (From Access97 help), uses the Line Input function to read a line into the variable.

Then, using a separate function, called within the loop, use string manipulation functions (ie. Mid, Left, Right) to pull the information from each imported string.

Dim TextLine
Open "TESTFILE" For Input As #1 ' Open file.

Do While Not EOF(1) ' Loop until end of file.
Line Input #1, TextLine ' Read line into variable.
ProcessStringToDatabase(TextLine)
Loop

Close #1 ' Close file.

Public Function ProcessStringToDatabase(strIn as String)
' Parse your string and update database.
End Function


Gary
gwinn7

 

[MichaelRed]

If you know the 'structure' of the original input, or can use some part of the input line to determine the 'structure', you can easily just input the whole line and parse it in a single operation. The routine(s) for this tend to be specific to the file and line structure of their input but are generally quite simple. A brief sample which I posted for someone else recently:

The Input File:
HEAD19072001BATCH4
01John Smith
0212 Street, Manchester
03100720011420Water Burst Paul Jackson
TRAILREC003

Public Function basParseRec(FilName As String)


    Dim MyFil As Integer
    Dim MyLine As String

    MyFil = FreeFile
    Open FilName For Input As MyFil

    While Not EOF(MyFil)
        Line Input #MyFil, MyLine
        Select Case UCase(left(MyLine, 2))
            Case Is = "HE"      'Header Line
                'Process as ...
                Debug.Print right(MyLine, Len(MyLine) - 4)
            Case Is = "01"      'Name
                'Process as ...
                Debug.Print right(MyLine, Len(MyLine) - 2)
            Case Is = "02"      'Address
                'Process as ...
                Debug.Print right(MyLine, Len(MyLine) - 2)
            Case Is = "03"      'Code / Problem
                'Process as ...
                Debug.Print right(MyLine, Len(MyLine) - 2)
            Case Is = "TR"      'Trailer Line
                'Process as ...
                Debug.Print right(MyLine, Len(MyLine) - 5)
        End Select
    Wend

End Function

This may be sufficient to get you started ...


MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[MoonMullen]

I understand eveything that was said so far, however how do you create a routine that will link a variable to a record within a table? It is the guts of what gwinn7 said as,

"Public Function ProcessStringToDatabase(strIn as String)
' Parse your string and update database.
End Function"

that I do not understand how to write. Please help with code or reference material!!I

 

[MichaelRed]

Jst add the dim and set statements for the database and recordset(s). When you have 'decoded" what part of the input goes where, do a recordset operation (edit / add) and place the new info into the recordset.field with the standatd assignment operations. update the recordset and move on to the next operation.

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[MichaelRed]

A "BARE BONES" example below shows all of the operations. The record retrieval and parseing is a bit off the beaten track, and the recordset is the older DAO type, but the whole process works and shows the assignment operations in complete detail.

Public Function basImportQuo()

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset

    Set dbs = CurrentDb
    Set rst = dbs.openrecordset("tblDailyQuo", dbopendynaset)

    Dim MyFil As Integer
    Dim Idx As Integer
    Dim MyFile() As String
    Dim MyStock As String
    Dim MyLine As Variant
    Dim FileName As String
    Dim MyPath As String

    MyPath = "C:\MsAccess\"
    ReDim MyFile(0)

    FileName = Dir(MyPath & "*.CSV")

    While FileName <> &quot;&quot;
        MyFile(Idx) = FileName
        FileName = Dir
        Idx = Idx + 1
        ReDim Preserve MyFile(Idx)
    Wend

    ReDim Preserve MyFile(Idx - 1)

    For Idx = 0 To UBound(MyFile)
        MyFil = FreeFile
        Open MyPath & MyFile(Idx) For Input As #MyFil
        MyTicker = Split(MyFile(Idx), &quot;.&quot;)
        MyTick = MyTicker(0)

        Line Input #MyFil, MyStock
        MyLine = Split(MyStock, vbLf)
        For Jdx = 0 To UBound(MyLine) - 1
            MyData = Split(MyLine(Jdx), &quot;,&quot;)
            If (IsDate(MyData(0))) Then
                With rst
                    .AddNew
                        !Tick = MyTick
                        !dtQuo = MyData(0)
                        !OpnQuo = MyData(1)
                        !HiQuo = MyData(2)
                        !LoQuo = MyData(3)
                        !ClsQuo = MyData(4)
                        !Vol = MyData(5)
                        '!AdjClose = MyData(6)
                    .Update
                End With
            End If
        Next Jdx

        Close #MyFil
    Next Idx

End Function

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[MoonMullen]

Does it matter to code variables as,
Dim Variable1 As String
Dim Variable2 As String
Dim Variable3 As String
or
Dim Variable1, Variable2, Variable3 As String?I

 

[MichaelRed]

Well, YES. But if yourr'e asking the question it may not matter. Ms. Access Help has a fairly clear explination. In your alternative statement, Variable1 and Variable2 are of type Variant while Variable3 is of type String.

Both Ms. Access and VB appear to moving (at approxinmatly the relative speed of a glacier) toward "Strong Typing", so at SOME point far away in La-La land, the alternative may not be allowed AT ALL.

For the present, a variable of type Variant will (ALMOST?) always be able to have a string be assigned to it and VBA will (again ALMOST?) always be able to negoitate a conversion to / form other appropiate data type. It WILL be somewhat slower than an explicit typing of string for all three, but the average user will not notice a sluggishness in a program - unless you are processing a really LARGE ammount of information through the variables.

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[MoonMullen]

MichealRed, thank you for your explanation. However I meant to ask, why is there a difference between how one codes varaiables in MS-Excel VBA and MS-Access VBA. My second example is the same as the first when coded in MS-Excel. All varaibles are "known" as string. I did not realise that MS-Access would "see" in the second example that variables 1 & 2 as variant. I thought that MS-Access and MS-Excel behaved in the same fashion, in this regard. Thank you for setting me straight. I