Import Specific text from a long text file

[Tukoah]

I am having an issue importing specific text from a lengthy log file. I can get it to find and return the correct results but it stops after the first match. I need it to go thru the entire file and return all relevant results in each column.

Private Sub CommandButton1_Click()
Dim myFile As String, text As String, textline As String, posIP As Integer, posUser As Integer
myFile = Application.GetOpenFilename()

Open myFile For Input As #1

Do Until EOF(1)
Line Input #1 , textline
text = text & textline
Loop

Close #1

posLat = InStr(text, "New client connection opened for")
posUser = InStr(text, "Trying to authenticate user-")


Range("A2").Value = Mid(text, posLat + 32, 15)

Range("B2").Value = Mid(text, posUser + 28, 5)

End Sub

 

[Rick Rothstein]

This is untested, but I think something like the following might do what you want (it examines and processes each line from the file as it is read in)...

[table="width: 500"]
[tr]
	[td]Private Sub CommandButton1_Click()

  Dim myFile As String, TextLine As String, posIP As Long, posUser As Long
  
  myFile = Application.GetOpenFilename()
  
  Open myFile For Input As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
  
  Do Until EOF(1)
    Line Input [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , TextLine
    posLat = InStr(TextLine, "New client connection opened for")
    posUser = InStr(TextLine, "Trying to authenticate user-")
    If posLat Then Cells(Rows.Count, "A").End(xlUp).Offset(1) = Mid(TextLine, posLat + 32, 15)
    If posUser Then Cells(Rows.Count, "B").End(xlUp).Offset(1) = Mid(text, posUser + 28, 5)
  Loop
  
  Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
  
End Sub[/td]
[/tr]
[/table]

 

[Tukoah]

That worked. Is there a way to add one more column that if a text string exists then insert the entire line into column 3?

 

[Rick Rothstein]

That worked. Is there a way to add one more column that if a text string exists then insert the entire line into column 3?

If I understand what you want correctly, just add another constant for that text string and do the same thing with it that was done for the other two text strings except that you would specify the entire text line. For example, something like this (although you will have to put the correct text string in place of my stand-in text string)...

Private Sub CommandButton1_Click()

  Dim myFile As String, TextLine As String, posIP As Long, posUser As Long[B][COLOR="#FF0000"], posCol3Text As Long[/COLOR][/B]
  
  myFile = Application.GetOpenFilename()
  
  Open myFile For Input As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL]  
  
  Do Until EOF(1)
    Line Input [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL]  , TextLine
    posLat = InStr(TextLine, "New client connection opened for")
    posUser = InStr(TextLine, "Trying to authenticate user-")
    [B][COLOR="#FF0000"]posCol3Text = InStr(TextLine, "New Column 3 Text To Test")[/COLOR][/B]
    If posLat Then Cells(Rows.Count, "A").End(xlUp).Offset(1) = Mid(TextLine, posLat + 32, 15)
    If posUser Then Cells(Rows.Count, "B").End(xlUp).Offset(1) = Mid(TextLine, posUser + 28, 5)
    [B][COLOR="#FF0000"]If posCol3Text Then Cells(Rows.Count, "C").End(xlUp).Offset(1) = TextLine[/COLOR][/B]
  Loop
  
  Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL]  
  
End Sub

 

[Tukoah]

Thank you. I worked on this for like 2 hours, what you fixed in a few mins.