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]

hi sir,

it will be on long list and yes for your follow up question thanks!

Here is the example of the result on excel should be:

Caloocan 1%
Batangas 4%
Cavite 5%

Yes it will ever appear on a line in the file which does not start with the text "CPU utilization"

 

[Rick Rothstein]

Here is the example of the result on excel should be:

Caloocan 1%
Batangas 4%
Cavite 5%

All you highlighted as wanting in your last question was the percentage number... where did the "Caloocan", "Batangas", "Cavite" text come from?

Yes it will ever appear on a line in the file which does not start with the text "CPU utilization"

Too bad... that will complicate the code somewhat.

 

[Holiday_Drew]

Hi Sir,

Sorry for the confusion I just need the percentage only but the position of the result will be like that no sorry for my wrong information Yes it will ever appear on a line in the file which does start with the text "CPU Utilization" Im so sorry

Thanks a lot

 

[Holiday_Drew]

Hi Sir,

I hope you could help me regarding this thank you so so much! Ill wait for the code thanks

 

[Rick Rothstein]

Hi Sir,

Sorry for the confusion I just need the percentage only but the position of the result will be like that no sorry for my wrong information Yes it will ever appear on a line in the file which does start with the text "CPU Utilization" Im so sorry

See if this code does what you want...

Sub Holiday_Drew_CPU()
  Dim X As Long, B As Long, FileNum As Long
  Dim TotalFile As String, Path As String, Filename As String
  Dim Bytes As Variant, Percents() 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, "CPU utilization", , vbTextCompare)
    ReDim Percents(1 To UBound(Packets), 1 To 1)
    B = 0
    For X = 1 To UBound(Packets)
      B = B + 1
      Percents(B, 1) = Format(Val(Split(Packets(X), "five minutes:")(1)) / 100, "0%")
    Next
    With Cells(Rows.Count, "A").End(xlUp).Offset(1).Resize(B, 1)
      .Value = Percents
      .Value = .Value
    End With
    Filename = Dir$
  Loop
End Sub

 

[Holiday_Drew]

Hi Sir,

Thank you so much for your help! I really appreciate! It is now working!

One more thing if I need to get multiple CPU utilization on 1 .txt file and I want to add it on that code you give to me can you please add it sir?

Thanks so much!

 

[Rick Rothstein]

One more thing if I need to get multiple CPU utilization on 1 .txt file

First, let me direct you back to what I wrote at the bottom of Message #14 . Now, with that said... I have no idea what you are asking me for with the above... please clarify it for us.

 

[Holiday_Drew]

Hi Sir,

Let me explain my concern in 1 .txt file I need to get 3 "Five Minutes: " CPU utilization I want to add the code on the previous code that you give to me.

Thanks a lot Sir!

 

[Holiday_Drew]

Hi Sir Rick,

I will wait for your help regarding my last concen:

Let me explain my concern in 1 .txt file I need to get 3 "Five Minutes: " CPU utilization I want to add the code on the previous code that you give to me.

Thanks a lot have a good day!

 

[nassardewa]

Cross Post

VBA Code to Extract Specific Data from .txt File into Excel | Chandoo.org Excel Forums - Become Awesome in Excel

Hi,
The VBA doesnt work for .txt file more than 400kB,
VBA code, we are declaring variable as string, it should be capable for 2billion character.
Anyother solution.