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:
Here's the code:
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
Last edited:
