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]

I got the button working now thanks last question,

How can I get another specific text file but in a different text file: sample I use earlier Balagtas now I will use file Caloocan same information I need to get the input and output


-----------------------------------------------------------------------
Cisco Configuration Professional (Cisco CP) is installed on this device.
This feature requires the one-time use of the username "cisco" with the
password "cisco". These default credentials have a privilege level of 15.

YOU MUST USE CISCO CP or the CISCO IOS CLI TO CHANGE THESE PUBLICLY-KNOWN
CREDENTIALS

Here are the Cisco IOS commands.

username <myuser> privilege 15 secret 0 <mypassword>
no username cisco

Replace <myuser> and <mypassword> with the username and password you want
to use.

IF YOU DO NOT CHANGE THE PUBLICLY-KNOWN CREDENTIALS, YOU WILL NOT BE ABLE
TO LOG INTO THE DEVICE AGAIN AFTER YOU HAVE LOGGED OFF.

For more information about Cisco CP please follow the instructions in the
QUICK START GUIDE for your router or go to http://www.cisco.com/go/ciscocp
-----------------------------------------------------------------------

% Password expiration warning.
-----------------------------------------------------------------------

Cisco Configuration Professional (Cisco CP) is installed on this device
and it provides the default username "cisco" for one-time use. If you have
already used the username "cisco" to login to the router and your IOS image
supports the "one-time" user option, then this username has already expired.
You will not be able to login to the router with this username after you exit
this session.

It is strongly suggested that you create a new username with a privilege level
of 15 using the following command.

username <myuser> privilege 15 secret 0 <mypassword>

Replace <myuser> and <mypassword> with the username and password you want to
use.

-----------------------------------------------------------------------

CALOOCANABCISR2#
CALOOCANABCISR2#!
CALOOCANABCISR2#en
CALOOCANABCISR2#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
CALOOCANABCISR2#!
CALOOCANABCISR2#show proc cpu sorted | e 0.0
CPU utilization for five seconds: 1%/0%; one minute: 1%; five minutes: 1%
PID Runtime(ms) Invoked uSecs 5Sec 1Min 5Min TTY Process
229 1084 3581282462 0 0.23% 0.23% 0.23% 0 HQF Output Shape
100 4696 57427252 0 0.15% 0.15% 0.15% 0 Netclock Backgro
117 584 1829291098 0 0.15% 0.13% 0.15% 0 Ethernet Msec Ti

CALOOCANABCISR2#!
CALOOCANABCISR2#sh int gig0/1
GigabitEthernet0/1 is up, line protocol is up
Hardware is CN Gigabit Ethernet, address is 74a2.e621.dbb1 (bia 74a2.e621.dbb1)
Description: { Link to DHTASR01 G0/1/0/6 }
Internet address is 10.221.2.214/28
MTU 1500 bytes, BW 4000 Kbit/sec, DLY 100 usec,
reliability 255/255, txload 1/255, rxload 1/255
Encapsulation ARPA, loopback not set
Keepalive set (10 sec)
Full Duplex, 100Mbps, media type is RJ45
output flow-control is XON, input flow-control is XON
ARP type: ARPA, ARP Timeout 04:00:00
Last input 00:00:05, output 00:00:04, output hang never
Last clearing of "show interface" counters never
Input queue: 0/75/0/0 (size/max/drops/flushes); Total output drops: 0
Queueing strategy: Class-based queueing
Output queue: 0/1000/0 (size/max total/drops)
5 minute input rate 0 bits/sec, 0 packets/sec
5 minute output rate 0 bits/sec, 0 packets/sec
794431 packets input, 70775345 bytes, 0 no buffer
Received 46836 broadcasts (0 IP multicasts)
0 runts, 0 giants, 0 throttles
0 input errors, 0 CRC, 0 frame, 0 overrun, 0 ignored
0 watchdog, 0 multicast, 0 pause input
2288773 packets output, 245197082 bytes, 0 underruns
0 output errors, 0 collisions, 0 interface resets
0 unknown protocol drops
0 babbles, 0 late collision, 0 deferred
1 lost carrier, 0 no carrier, 0 pause output
0 output buffer failures, 0 output buffers swapped out
CALOOCANABCISR2#!
CALOOCANABCISR2#sh int gig0/2
GigabitEthernet0/2 is up, line protocol is up
Hardware is CN Gigabit Ethernet, address is 74a2.e621.dbb2 (bia 74a2.e621.dbb2)
Description: { Link to CALOOCANABCISR1 G0/2 }
MTU 1500 bytes, BW 38000 Kbit/sec, DLY 10 usec,
reliability 255/255, txload 1/255, rxload 1/255
Encapsulation 802.1Q Virtual LAN, Vlan ID 1., loopback not set
Keepalive set (10 sec)
Full Duplex, 1Gbps, media type is RJ45
output flow-control is XON, input flow-control is XON
ARP type: ARPA, ARP Timeout 04:00:00
Last input 00:00:18, output 00:00:00, output hang never
Last clearing of "show interface" counters never
Input queue: 0/75/0/0 (size/max/drops/flushes); Total output drops: 49
Queueing strategy: Class-based queueing
Output queue: 0/1000/49 (size/max total/drops)
5 minute input rate 0 bits/sec, 0 packets/sec
5 minute output rate 77000 bits/sec, 42 packets/sec
815721 packets input, 147594050 bytes, 0 no buffer
Received 265858 broadcasts (0 IP multicasts)
0 runts, 0 giants, 0 throttles
0 input errors, 0 CRC, 0 frame, 0 overrun, 0 ignored
0 watchdog, 265858 multicast, 0 pause input
705768946 packets output, 163018068 bytes, 0 underruns
0 output errors, 0 collisions, 0 interface resets
0 unknown protocol drops
0 babbles, 0 late collision, 0 deferred
0 lost carrier, 0 no carrier, 0 pause output
0 output buffer failures, 0 output buffers swapped out
CALOOCANABCISR2#!
CALOOCANABCISR2#sh int gig1/1
GigabitEthernet1/1 is up, line protocol is up
Hardware is EE Internal Service Module, address is 0000.0000.0009 (bia 0000.0000.0009)
Description: {Internal switch interface connected to Service Module}
MTU 1500 bytes, BW 1000000 Kbit/sec, DLY 10 usec,
reliability 255/255, txload 1/255, rxload 1/255
Encapsulation ARPA, loopback not set
Keepalive set (10 sec)
Auto-duplex, Auto-speed
ARP type: ARPA, ARP Timeout 04:00:00
Last input 00:00:14, output never, output hang never
Last clearing of "show interface" counters never
Input queue: 0/75/0/0 (size/max/drops/flushes); Total output drops: 0
Queueing strategy: fifo
Output queue: 0/40 (size/max)
5 minute input rate 82000 bits/sec, 56 packets/sec
5 minute output rate 2000 bits/sec, 3 packets/sec
802659649 packets input, 1265363467 bytes, 0 no buffer
Received 33558257 broadcasts (29456120 multicasts)
0 runts, 0 giants, 0 throttles
0 input errors, 0 CRC, 0 frame, 0 overrun, 0 ignored
0 watchdog, 0 multicast, 0 pause input
0 input packets with dribble condition detected
48961341 packets output, 3955057802 bytes, 0 underruns
0 output errors, 0 collisions, 2 interface resets
478484 unknown protocol drops
0 babbles, 0 late collision, 0 deferred
2 lost carrier, 0 no carrier, 0 pause output
0 output buffer failures, 0 output buffers swapped out
CALOOCANABCISR2#!
CALOOCANABCISR2#sh interfaces counters errors

Port Align-Err FCS-Err Xmit-Err Rcv-Err UnderSize
Gi1/1 0 0 0 0 0

Port Single-Col Multi-Col Late-Col Excess-Col Carri-Sen Runts Giants
Gi1/1 0 0 0 0 0 0 0
CALOOCANABCISR2#


Thanks a lot!

 

[Rick Rothstein]

I got the button working now thanks last question,

How can I get another specific text file but in a different text file: sample I use earlier Balagtas now I will use file Caloocan same information I need to get the input and output

Give this modified version of my macro a try (it will put up a file selector from which you can select the text file you want to process)...

Sub Holiday_Drew()
  Dim X As Long, B As Long, FileNum As Long, Filename As String, TotalFile As String
  Dim Bytes() As String, Packets() As String, Txt() As String
  Filename = Application.GetOpenFilename("Text Files (*.txt),*.txt")
  If Filename = "False" Then Exit Sub
  FileNum = FreeFile
  Open 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)
  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
  Columns("A:B").Clear
  Range("A1").Resize(B, 2) = Bytes
  Columns("A:B").AutoFit
End Sub

Note: All output still starts at cell A1 of the ActiveSheet.

 

[Holiday_Drew]

This work but I need like this one:the first 3 lines data is in Caloocan file and the other 3 lines data is in Balagtas this is my last question sorry for some confusion but you are really big help for first timer on vba thanks a lot

1870415651 3065901950
3680178082 300869916
1028972520 3529467950
59034 253637
158288 62633453
70924975 4015719

 

[Rick Rothstein]

This work but I need like this one:the first 3 lines data is in Caloocan file and the other 3 lines data is in Balagtas this is my last question sorry for some confusion but you are really big help for first timer on vba thanks a lot

1870415651 3065901950
3680178082 300869916
1028972520 3529467950
59034 253637
158288 62633453
70924975 4015719

This should do what you are asking for...

Sub Holiday_Drew()
  Dim X As Long, B As Long, FileNum As Long, Filename As String, TotalFile As String
  Dim Bytes() As String, Packets() As String, Txt() As String
  Filename = Application.GetOpenFilename("Text Files (*.txt),*.txt")
  If Filename = "False" Then Exit Sub
  FileNum = FreeFile
  Open 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)
  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
End Sub

For future questions you may ask
------------------------------------------------------------
One thing you must keep in mind when you ask a question in a forum... the people you are asking to help you know absolutely nothing about your data, absolutely nothing about how it is laid out in the workbook, absolutely nothing about what you want done with it and absolutely nothing about how whatever it is you want done is to be presented back to you as a result... you must be very specific about describing each of these areas, in detail, and you should not assume that we will be able to "figure it out" on our own. Remember, you are asking us for help... so help us to be able to help you by providing the information we need to do so, even if that information seems "obvious" to you (remember, it is only obvious to you because of your familiarity with your data, its layout and the overall objective for it).

 

[Holiday_Drew]

Hi Sir,

Thanks for the big help this is working is it also possible to select the folder where the text files location then it will automatically search the data like just 1 click?

Thanks for the big help!

 

[Rick Rothstein]

Hi Sir,

Thanks for the big help this is working is it also possible to select the folder where the text files location then it will automatically search the data like just 1 click?

Do you mean you keep all of your files in one folder and you wan the code to open the "Get File" dialog box pointing to the folder? If so, what is the path to that folder. If not, then you will need to expand your description of what you are asking for here.

 

[Holiday_Drew]

Sir,

You are correct I want to keep all files in one folder and I want the code to open the "Get File" dialog box pointing to the folder. Here is the path "C:\Users\Andrew Sangalang\Downloads\Logs" of the folder

 

[Rick Rothstein]

You are correct I want to keep all files in one folder and I want the code to open the "Get File" dialog box pointing to the folder. Here is the path "C:\Users\Andrew Sangalang\Downloads\Logs" of the folder

Give this code a try...

[table="width: 500"]
[tr]
	[td]Sub Holiday_Drew()
  Dim X As Long, B As Long, FileNum As Long
  Dim Drive As String, Path As String, Filename As String, TotalFile As String
  Dim Bytes() As String, Packets() As String, Txt() As String
  Drive = CurDir
  ChDrive "C"
  ChDir "C:\Users\Andrew Sangalang\Downloads\Logs"
  Filename = Application.GetOpenFilename("Text Files (*.txt),*.txt")
  ChDrive Left(Drive, 1)
  ChDir Drive
  If Filename = "False" Then Exit Sub
  FileNum = FreeFile
  Open 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)
  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
End Sub[/td]
[/tr]
[/table]

 

[Holiday_Drew]

Hi Sir,

Still need to select one by one sir... still need to click the button many times and select the text file even if all text file is in one folder I just want to work just one click and all of information I need will automatically transfer to worksheet thanks a lot sir!

 

[Rick Rothstein]

Still need to select one by one sir... still need to click the button many times and select the text file even if all text file is in one folder I just want to work just one click and all of information I need will automatically transfer to worksheet thanks a lot sir!

Sorry, I had misunderstood what you wanted. Just to be clear...

All .txt files in the "C:\Users\Andrew Sangalang\Downloads\Logs" folder are to be processed, correct?

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?

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)?