Extract specific data from .txt file into Excel using VBA

[r_john]

Have a .txt file that contains specific data that needs to be extracted and placed into corresponding columns in Excel. New to VBA coding so having difficulty in making this work... below shows the code I have thus far but when run, it only extracts the first set of data but does not move onto the next block of text. In the Excel file I need: Description (company Name) | Speed (eg 1M) | Service Num. (7-digit number after the speed). The following is sample data present in the .txt file:

#
interface GigabitEthernet5/
vlan-type aser 7878
description ABC_COMPANY_1M_1254589_4444243
ip binding vpn-instance internet_vpn
ip address 158.214.125.215
#
interface GigabitEthernet5/0
vlan-type frin 2255
description XYZ_COMPANY_6M_1458963_444
ip binding vpn-instance internet_vpn
ip address 148.214.25.214
#

All data required comes after the "interface GigabitEthernet" line (eg. Description: ABC_COMPANY | Speed: 1M | Service Num: 1254589)... there is also loads of data that comes before and after these blocks that does not need extracting.

The code below extracts correctly but does not move onto the next block of data required:

Private Sub CommandButton1_Click()
    Dim myFile As String, find1 As String, i As Integer, und As String, speed2 s Integer, text As String, Desc As String, r As Long, dashpos As Long, m As Long, textline As String, posLat As Integer, posLong As Integer, strLeft As String, strFind As String, strRight As String, strMid As String, speed As String

    myFile = "C:\dump2.txt"

    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
    text = text & textline
    Loop

    Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 

    Desc = InStr(text, "interface GigabitEthernet")
    speed = InStr(text, "M_")

    Range("A1").Value = "Description"
    Range("B1").Value = "Speed"
    Range("c1").Value = "Service Num"

    Range("A2").Value = Mid(text, Desc + 68, 30)
    Range("b2").Value = Mid(text, speed + -3, 4)

    und = Mid(text, speed + -3, 4)

    speed2 = InStr(1, und, "_")

    Dim finalString As String
    finalString = Right(und, Len(und) - speed2 + 0)
    Range("b2").Value = finalString

    Desc = InStr(text, "interface GigabitEthernet")
    speed = InStr(text, "M_")
    Range("C2").Value = Mid(text, speed + 2, 6)
    End Sub

Appreciate any help with this... many thanks in advance.

 

[Holiday_Drew]

All .txt files in the "C:\Users\Andrew Sangalang\Downloads\Logs" folder are to be processed, correct? - Yes
If so, I am guessing you do not care in what order the files are processed (affects the order the input and output bytes appear), correct? - Yes
Do you want a blank row between each file's output so you can tell where one ends and the next begins (would protect against there being more inputs than outputs, or vice versa, per file)? - no need Sir

thanks a lot again!

 

[Rick Rothstein]

All .txt files in the "C:\Users\Andrew Sangalang\Downloads\Logs" folder are to be processed, correct? - Yes
If so, I am guessing you do not care in what order the files are processed (affects the order the input and output bytes appear), correct? - Yes
Do you want a blank row between each file's output so you can tell where one ends and the next begins (would protect against there being more inputs than outputs, or vice versa, per file)? - no need Sir

Okay, I took out the dialog box as we now know where all the files are located. I think this could will do what you are looking to do...

[table="width: 500"]
[tr]
	[td]Sub Holiday_Drew()
  Dim X As Long, B As Long, FileNum As Long
  Dim TotalFile As String, Path As String, Filename As String
  Dim Bytes() As String, Packets() As String, Txt() As String
  Path = "C:\Users\Andrew Sangalang\Downloads\Logs\"
  Filename = Dir$(Path & "*.txt")
  Do While Len(Filename)
    FileNum = FreeFile
    Open Path & Filename For Binary As #FileNum 
      TotalFile = Space(LOF(FileNum))
      Get #FileNum , , TotalFile
    Close #FileNum 
    Packets = Split(TotalFile, " packets ", , vbTextCompare)
    ReDim Bytes(1 To UBound(Packets) + 1, 1 To 2)
    B = 0
    For X = 1 To UBound(Packets)
      Txt = Split(Packets(X))
      If Txt(0) = "input," And Not Txt(1) Like "*[!0-9]*" Then
        B = B + 1
        Bytes(B, 1) = Txt(1)
      ElseIf Txt(0) = "output," And Not Txt(1) Like "*[!0-9]*" Then
        Bytes(B, 2) = Txt(1)
      End If
    Next
    Cells(Rows.Count, "A").End(xlUp).Offset(1).Resize(B, 2) = Bytes
    Columns("A:B").AutoFit
    Filename = Dir$
  Loop
End Sub[/td]
[/tr]
[/table]

 

[Holiday_Drew]

Hi Sir,

I got it thanks a lot! One last more question this is my final and last question if I want to get the information with red color what will be the code I will add another button also I want the same result with my last question I just want All .txt files in the "C:\Users\Andrew Sangalang\Downloads\Logs" folder are to be processed

BALAGTASBCISR2#opnet2h12
Translating "opnet2h12"...domain server (255.255.255.255)

% Bad IP address or host name
Translating "opnet2h12"...domain server (255.255.255.255)
(255.255.255.255)
Translating "opnet2h12"...domain server (255.255.255.255)

% Unknown command or computer name, or unable to find computer address
BALAGTASBCISR2#!
BALAGTASBCISR2#show proc cpu sorted | e 0.0
CPU utilization for five seconds: 0%/0%; one minute: 1%; five minutes: 1%

Thanks this will be my last question for today! Thanks a lot!

 

[Holiday_Drew]

Hi Sir,

I just want to follow up this I hope you could help me...

I got it thanks a lot! One last more question this is my final and last question if I want to get the information with red color what will be the code I will add another button also I want the same result with my last question I just want All .txt files in the "C:\Users\Andrew Sangalang\Downloads\Logs" folder are to be processed

BALAGTASBCISR2#opnet2h12
Translating "opnet2h12"...domain server (255.255.255.255)

% Bad IP address or host name
Translating "opnet2h12"...domain server (255.255.255.255)
(255.255.255.255)
Translating "opnet2h12"...domain server (255.255.255.255)

% Unknown command or computer name, or unable to find computer address
BALAGTASBCISR2#!
BALAGTASBCISR2#show proc cpu sorted | e 0.0
CPU utilization for five seconds: 0%/0%; one minute: 1%; five minutes: 1%

 

[Holiday_Drew]

Hi Sir,

I just want to follow up this I hope you could help me...

I got it thanks a lot! One last more question this is my final and last question if I want to get the information with red color what will be the code I will add another button also I want the same result with my last question I just want All .txt files in the "C:\Users\Andrew Sangalang\Downloads\Logs" folder are to be processed

BALAGTASBCISR2#opnet2h12
Translating "opnet2h12"...domain server (255.255.255.255)

% Bad IP address or host name
Translating "opnet2h12"...domain server (255.255.255.255)
(255.255.255.255)
Translating "opnet2h12"...domain server (255.255.255.255)

% Unknown command or computer name, or unable to find computer address
BALAGTASBCISR2#!
BALAGTASBCISR2#show proc cpu sorted | e 0.0
CPU utilization for five seconds: 0%/0%; one minute: 1%; five minutes: 1%

Hi Sir Rick Rothstein,

Can you please help me regarding this Sir please I do apologized if I have a lot of question because this is my first time to use the vba on excel.

Thanks a lot!

 

[Rick Rothstein]

CPU utilization for five seconds: 0%/0%; one minute: 1%; five minutes: 1%

Keep in mind that we here have no idea about your data or what you need from it, so you should explain in detail what you are looking to do.

Are you looking for the last number on a line that starts with the text "CPU utilization" or the number after the text "five minutes:" on a line that starts with the text "CPU utilization"?

 

[Holiday_Drew]

Hi Sir Rick,

I need to get the number on the five minutes: % result and I just want All .txt files in the "C:\Users\Andrew Sangalang\Downloads\Logs" folder are to be processed.

Thanks a lot Sir!

 

[Rick Rothstein]

Hi Sir Rick,

I need to get the number on the five minutes: % result and I just want All .txt files in the "C:\Users\Andrew Sangalang\Downloads\Logs" folder are to be processed.

Yes, but will that "five minutes:" percentage always be located on a line of text that starts with the text "CPU utilization? Or can that "five minutes:" percentage be located on any line of text in the file?

Also, is this a separate macro from the previous one I wrote or do you want the code to do this embedded within the other macro I wrote? if so, explain in detail where on the output it should go. If not, where should the output go and how should it be listed (just a long list of numbers down a column)?

 

[Holiday_Drew]

Hi Sir,

Yes, but will that "five minutes:" percentage always be located on a line of text that starts with the text "CPU utilization? Yes it is located on a line of text that starts with the text "CPU utilization"

Yes this is separate macro from the previous one I will add another button for this sir

 

[Rick Rothstein]

Yes this is separate macro from the previous one I will add another button for this sir

And how should it be listed (one long list, value after value with regard which file the number came from)?

Also, a follow-up question... will the text "5 minutes:" ever appear on a line in the file which does not start with the text "CPU utilization"