Need help to speed up my text import

[darag2358]

I have a big messy text file that I need to import and extract information from to go in to two tables. These are our mainframe security access rules and I need to compile the information from this file in to a database so that I can see who owns and has access to what screens. The file is about 85 thousand lines (I tried to post a screenshot showing an example of what is in the text file, but I couldn't get it to upload). I used to clean this file up in Excel before exporting to Access but the file size is now too large for Excel to handle. The code works but it takes 14 minutes to run through the file. Is there anything I can do to speed this up?

Here's the code:

Public Function ImportMainframeTable()

    Dim db As DAO.Database, rst1 As DAO.Recordset, rst2 As DAO.Recordset, stTsoFile As String
    Dim stTSOLine As String, stKey As String, stAuditOwner As String
    Dim stOwner As String, stArea As String, stAllowPrevent As String
    Dim TimeA As String, TimeB As String
    Dim check As Integer
    
    TimeA = Time ' marks beginning time
    
    stTsoFile = "c:\CICSPROD.txt" ' location of the text file
    Set db = CurrentDb
    Set rst1 = db.OpenRecordset("tblMainframeRules")
    Set rst2 = db.OpenRecordset("tblRuleOwners")

    '  Delete existing tables
    db.Execute "DELETE * FROM tblMainframeRules", dbFailOnError
    db.Execute "DELETE * FROM tblRuleOwners", dbFailOnError

    Close #1 'just in case code is restarted after being interrupted before completion
    
    Open stTsoFile For Input As #1
    
    check = 0  'used in the inner loop
    
    Do Until EOF(1)
        Line Input #1, stTSOLine
	'First check to see if line contains "$KEY"
        If InStr(1, stTSOLine, "$KEY(") <> 0 Then
            stKey = Left(stTSOLine, InStr(1, stTSOLine, "    ") - 1)
            Line Input #1, stTSOLine
	    'Next check to see if next line contains "$USERDATA"
            If InStr(1, stTSOLine, "$USERDATA") <> 0 Then
                stOwner = Mid(stTSOLine, InStr(1, stTSOLine, "%") + 1, (InStr(1, stTSOLine, "    ") - 2) - InStr(1, stTSOLine, "%"))
                Line Input #1, stTSOLine
		'If next line contains "$PREFIX" then skip it and go to next line because it is not needed information
                If InStr(1, stTSOLine, "$PREFIX") Then Line Input #1, stTSOLine
		'Check to make sure next line reads "%CHANGE"
                If InStr(1, stTSOLine, "%CHANGE") <> 0 Then
                    stAuditOwner = Mid(stTSOLine, 9, 6)
                    With rst2
                    .AddNew
                    !fldKey = stKey
                    !fldOwner = stOwner
                    !fldAuditOwner = stAuditOwner
                    .Update
                    End With
                    Do While check = 0
                    Line Input #1, stTSOLine
		    'Final check:  Next lines should have the UID string and should loop through 
		    ' and write to the two tables until the final UID line is read for that group
                    If InStr(1, stTSOLine, "UID") <> 0 Then
                        stArea = Mid(stTSOLine, 6, InStr(1, stTSOLine, ")") - 6)
                        If InStr(1, stTSOLine, "prevent") <> 0 Then
                            stAllowPrevent = Mid(stTSOLine, InStr(1, stTSOLine, ")") + 2, 7)
                        Else
                            stAllowPrevent = Mid(stTSOLine, InStr(1, stTSOLine, ")") + 2, 5)
                        End If
                        With rst1
                        .AddNew
                        !fldArea = stArea
                        !fldAllowPrevent = stAllowPrevent
                        !fldKey = stKey
                        '!fldOwner = stOwner
                        '!fldAuditOwner = stAuditOwner
                        .Update
                        End With
                    Else
			'Last UID line was read
                        check = 1
                    End If
                    Loop
                End If
            End If
        End If
    'Resets the check variable before restarting
    check = 0
    Loop
    
    Close #1
    rst1.Close
    rst2.Close
    TimeB = Time
    MsgBox (TimeA & TimeB)  ' Displays elapsed time to run the code
End Function

[WayneRyan]

Dan,

You're not doing anything unusual in your code. Read the
file and write to one of two recordsets. I don't see any way
to "streamline" it.

In the overall scheme of things, 14 minutes is not too bad,
since you know that ... it looks like break time.

How often is this done. If it is daily (or often) maybe you could
look into ways of reducing the size of the file so that you don't
re-insert the same data every time.

Maybe you should not delete the data first, run your process
in the "background" and spend the extra effort of searching for
additions, deletions and updates. Longer overall, but the
database is available during processing.

Wayne

[ChrisO]

G’day Dan.

Dim lngPosition As Long
    
    '  Since this is the inner loop it is probably to place to start.
    '
    '  Should be faster to do the InStr(1, stTSOLine, ")")
    '  only once and re-use the result in both places.
    '
    '  Also if you can be sure of the case, string compares
    '  should be faster done with vbBinaryCompare
    '
    '  The With statement might also slow things down a bit.
    '
    '  I have no idea how much faster it would be without the data and testing it.
    
    If InStr(1, stTSOLine, "UID", vbBinaryCompare) <> 0 Then
        lngPosition = InStr(1, stTSOLine, ")")
        stArea = Mid(stTSOLine, 6, lngPosition - 6)
        
        If InStr(1, stTSOLine, "prevent", vbBinaryCompare) <> 0 Then
            stAllowPrevent = Mid(stTSOLine, lngPosition + 2, 7)
        Else
            stAllowPrevent = Mid(stTSOLine, lngPosition + 2, 5)
        End If
        
        rst1.AddNew
        rst1!fldArea = stArea
        rst1!fldAllowPrevent = stAllowPrevent
        rst1!fldKey = stKey
        'rst1!fldOwner = stOwner
        'rst1!fldAuditOwner = stAuditOwner
        rst1.Update
    Else
        check = 1
    End If

Edit to add: -

A couple of other things that might help;

Search strings like

"$KEY" "$USERDATA" "$PREFIX" "%CHANGE" and "prevent"

may??? be able to be abbreviated.

Maybe;

“$K” “$U” “$P” “%C” and “prev” would do the same job if they are unique on the line. No point wasting time checking the extra characters once a match is found for a unique identifier.

Another thing to look at might be the starting position of the identifiers. If for instances "$USERDATA" always starts at the 20th character, if present, then use the

InStr(20, stTSOLine, "$U", vbBinaryCompare)

construct instead.

All this of course depends on the data.

Regards,
Chris.

[darag2358]

Appreciate both of your thoughts. I will put them in to place and see if it helps. At least it is good to know that my code is basically correct, since this is the first time I have tried to import a text file in this manner.

Thanks again,
Dan

[Mile-O]

                        !fldArea = stArea
                        !fldAllowPrevent = stAllowPrevent
                        !fldKey = stKey
                        '!fldOwner = stOwner
                        '!fldAuditOwner = stAuditOwner

Use .Fields("fldArea") = stArea etc. as, by using the ! notation (late binding) you are forcing the code at runtime to determine what type of object you are implying and so the interpreter goes through its libraries looking for any type of known object that might match - eventually it gets to the field object and it goes "A ha! and makes the label named after it into a field.

If you use the . notation (early binding) then you are explicitly telling the code to reference a field. When this method is used the code is compiled prior to runtime and therefore, when running, the interpreter knows that it is a field you are referenceing and doesn't have to waste time playing mix and match while it finds the correct, if any, match. The reason that I say "if any" is, because the ! determined at runtime may be incorrect (e.g. spelling mistake) and won't come to light until the application is tripped by this. By using early binding you can explicity set these references, and have the benefit of the Intellisense system that drops down all possible methods, properties, and functions possible with respect to any recognised object.

[darag2358]

Thank you, that is an excellent tip and explanation!