excel read from text file if specific word exist in the line HELP

[nefkho]

hi,

can anyone help me with the following, i need to get a data from a text file and write specific contents to excel if the condition exist on the line.

below is the content of my text
----
2019-01-10 13:59:45 Local2.Info 172.16.66.1 Jan 10 13:59:35 stamgr: tac_del_arp:dev=br0 SIOCDARP failed, errno=6 2019-01-10 13:59:45 Local0.Info 172.16.66.1 Jan 10 13:59:35 syslog: eventd_to_syslog():User[08:ae:d6:95:51:c7] leave WLAN[House] at AP[HSE-2F-AP02@1c:b9:c4:20:91:B0] with Session Time[8.30 sec] RX Bytes[4614] TX Bytes[26831]
2019-01-10 13:59:46 Local0.Info 172.16.66.1 Jan 10 13:59:36 syslog: eventd_to_syslog():User[f4:09:d8:c7:e7:cf] joins WLAN[Guest] from AP[Admin@1c:b9:c4:23:cc:6A]
----

and in excel i need to have like this only if "WLAN[Guest]" exist in the line.

Column A Column B
2019-01-10 13:59:45 User[f4:09:d8:c7:e7:cf]

thanks,

 

[DanteAmor]

Use the following macro, change "sheetx" to the name of your excel sheet.
Change "text file.txt" to the name of your txt file.
Put the book with the macro in the same folder where you have your txt file.

Sub Read_From_Text_File()
    'read from text file if specific word "WLAN[Guest]" exist in the line
    '
    Application.ScreenUpdating = False
    ruta = ThisWorkbook.Path & "\"
    arch = "text file.txt"
    texto = "WLAN[Guest]"
    '
    Set l1 = ThisWorkbook
    Set h1 = l1.Sheets("SheetX")    'name of your sheet
    '
    Workbooks.OpenText Filename:=ruta & arch, _
        Origin:=xlMSDOS, StartRow:=1, DataType:=xlFixedWidth, _
        FieldInfo:=Array(0, 2), TrailingMinusNumbers:=True
    Set l2 = ActiveWorkbook
    Set h2 = l2.Sheets(1)
    Set b = h2.Columns("A").Find(texto, lookat:=xlPart)
    If Not b Is Nothing Then
        fec = Left(h2.Cells(b.Row, "A"), 19)
        i = InStr(1, h2.Cells(b.Row, "A"), "User")
        f = InStr(i, h2.Cells(b.Row, "A"), "]") - i + 1
        user = Mid(h2.Cells(b.Row, "A"), i, f)
        h1.Range("A2").Value = fec
        h1.Range("B2").Value = user
    Else
        MsgBox "The word does not exist"
    End If
    l2.Close False
    Application.ScreenUpdating = True
End Sub

 

[nefkho]

thanks, got it working but it only read the first line with matching filters, also i need to add the row down and read the whole file, i may have many WLAN[Guest],

thanks.


here is a full sample data:

sample output:

2019-01-10 13:59:45 User[Guest-2227@08:ae:d6:95:51:c7]
2019-01-10 13:59:46 User[Guest-2092@88:bd:45:1e:58:19]
2019-01-10 13:59:46 User[Guest-2763@f0:98:9d:60:4e:bf]
2019-01-10 13:59:46 User[Guest-2763@f0:98:9d:60:4e:bf]
2019-01-10 13:59:48 User[Guest-584@6c:ab:31:79:56:f5]
2019-01-10 13:59:48 User[Guest-1445@1c:23:2c:d7:30:47]
2019-01-10 13:59:48 User[Guest-1445@1c:23:2c:d7:30:47]

sample source:
2019-01-10 13:59:45 Local2.Info 172.16.66.1 Jan 10 13:59:35 stamgr: tac_del_arp:dev=br0 SIOCDARP failed, errno=6
2019-01-10 13:59:45 Local0.Info 172.16.66.1 Jan 10 13:59:35 syslog: eventd_to_syslog():User[Guest-2227@08:ae:d6:95:51:c7] leave WLAN[Guest] at AP[SBR-6F-AP02@1c:b9:c4:20:91:a0] with Session Time[8.30 sec] RX Bytes[4614] TX Bytes[26831]
2019-01-10 13:59:46 Local0.Info 172.16.66.1 Jan 10 13:59:36 syslog: eventd_to_syslog():User[f4:09:d8:c7:e7:cf] joins WLAN[Admin] from AP[Admin@1c:b9:c4:23:cc:60]
2019-01-10 13:59:46 Local2.Info 172.16.66.1 Jan 10 13:59:36 stamgr: tac_del_arp:dev=br0 SIOCDARP failed, errno=6
2019-01-10 13:59:46 Local0.Info 172.16.66.1 Jan 10 13:59:36 syslog: eventd_to_syslog():User[c4:ab:b2:71:fe:c2] disconnects from WLAN[Admin] at AP[SBR-4F-AP04@1c:b9:c4:20:ab:c0]
2019-01-10 13:59:46 Local0.Info 172.16.66.1 Jan 10 13:59:36 syslog: eventd_to_syslog():User[c4:ab:b2:71:fe:c2] joins WLAN[Admin] from AP[SBR-5F-AP05@1c:b9:c4:23:ca:00]
2019-01-10 13:59:46 Local2.Info 172.16.66.1 Jan 10 13:59:36 stamgr: tac_del_arp:dev=br0 SIOCDARP failed, errno=6
2019-01-10 13:59:46 Local0.Info 172.16.66.1 Jan 10 13:59:36 syslog: eventd_to_syslog():AP[SR-6F-AP02@f8:e7:1e:06:49:10] radio [11g/n] detects User[3c:f7:a4:eb:53:0b] in WLAN[Admin] roams out to AP[SR-7F-AP02@38:ff:36:01:84:20]
2019-01-10 13:59:46 Local0.Info 172.16.66.1 Jan 10 13:59:36 syslog: eventd_to_syslog():AP[SR-7F-AP02@38:ff:36:01:84:20] radio [11g/n] detects User[3c:f7:a4:eb:53:0b] in WLAN[Admin] roams from AP[SR-6F-AP02@f8:e7:1e:06:49:10]
2019-01-10 13:59:46 Local0.Info 172.16.66.1 Jan 10 13:59:36 syslog: eventd_to_syslog():User[Guest-2092@88:bd:45:1e:58:19] leave WLAN[Guest] at AP[SBR-GF-AP01@1c:b9:c4:21:fa:50] with Session Time[499.47 sec] RX Bytes[17240] TX Bytes[698291]
2019-01-10 13:59:46 Local0.Info 172.16.66.1 Jan 10 13:59:36 syslog: eventd_to_syslog():User[Guest-2763@f0:98:9d:60:4e:bf] leave WLAN[Guest] at AP[SBR-GF-AP01@1c:b9:c4:21:fa:50] with Session Time[109.50 sec] RX Bytes[0] TX Bytes[362744]
2019-01-10 13:59:48 Local0.Info 172.16.66.1 Jan 10 13:59:38 syslog: eventd_to_syslog():User[Guest-584@6c:ab:31:79:56:f5] reconnects to AP[SBR-4F-AP04@1c:b9:c4:20:ab:c0] within grace period. No additional authentication is required.
2019-01-10 13:59:48 Local0.Info 172.16.66.1 Jan 10 13:59:38 syslog: eventd_to_syslog():User[Guest-584@6c:ab:31:79:56:f5] joins WLAN[Guest] from AP[SBR-4F-AP04@1c:b9:c4:20:ab:c0]
2019-01-10 13:59:48 Local2.Info 172.16.66.1 Jan 10 13:59:38 stamgr: tac_del_arp:dev=br0 SIOCDARP failed, errno=6
2019-01-10 13:59:48 Local0.Info 172.16.66.1 Jan 10 13:59:38 syslog: eventd_to_syslog():AP[SR-UGF-AP03@f0:b0:52:16:07:b0] radio [11g/n] detects User[Guest-1445@1c:23:2c:d7:30:47] in WLAN[Guest] roams out to AP[SR-4F-AP09@f8:e7:1e:06:49:30]
2019-01-10 13:59:48 Local0.Info 172.16.66.1 Jan 10 13:59:38 syslog: eventd_to_syslog():AP[SR-4F-AP09@f8:e7:1e:06:49:30] radio [11a/n/ac] detects User[Guest-1445@1c:23:2c:d7:30:47] in WLAN[Guest] roams from AP[SR-UGF-AP03@f0:b0:52:16:07:b0]
2019-01-10 13:59:48 Daemon.Error 172.16.66.2 Jan 10 05:59:37 avpd[429]: avpd_process_roam_from: 740: the rflow count need to be synchronized is 0

 

[DanteAmor]

Okay, but you did not mention that you had several lines.

Updated macro:

Sub Read_From_Text_File()
    'read from text file if specific word "WLAN[Guest]" exist in the line
    '
    Application.ScreenUpdating = False
    ruta = ThisWorkbook.Path & "\"
    arch = "text file.txt"
    texto = "WLAN[Guest]"
    '
    Set l1 = ThisWorkbook
    Set h1 = l1.Sheets("SheetX")    'name of your sheet
    h1.Rows("2:" & Rows.Count).ClearContents
    j = 2
    '
    Workbooks.OpenText Filename:=ruta & arch, _
        Origin:=xlMSDOS, StartRow:=1, DataType:=xlFixedWidth, _
        FieldInfo:=Array(0, 2), TrailingMinusNumbers:=True
    Set l2 = ActiveWorkbook
    Set h2 = l2.Sheets(1)
    Set r = h2.Columns("A")
    Set b = r.Find(texto, LookAt:=xlPart)
    If Not b Is Nothing Then
        celda = b.Address
        Do
            'detalle
            fec = Left(h2.Cells(b.Row, "A"), 19)
            i = InStr(1, h2.Cells(b.Row, "A"), "User")
            f = InStr(i, h2.Cells(b.Row, "A"), "]") - i + 1
            user = Mid(h2.Cells(b.Row, "A"), i, f)
            h1.Range("A" & j).Value = fec
            h1.Range("B" & j).Value = user
            j = j + 1
            
            Set b = r.FindNext(b)
        Loop While Not b Is Nothing And b.Address <> celda
    End If
    l2.Close False
    Application.ScreenUpdating = True
End Sub

 

[nefkho]

thank you so much.... working pretty well..

 

[DanteAmor]

I'm glad to help you