VBA to extract data from notepad to excel

[SpeedyKevin]

Hello all!

Have been a lurker for sometime and have found an incredible amount of useful threads here! However I can't seem to find anything that may help me with a notepad to excel macro ( I know theres a few but can't seem to get them to work )

So what I am trying to do is pull out Time stamps from a log (txt file). The log looks like this:


START STREAM @ 2019-01-09 10:35:04
STOP STREAM @ 2019-01-09 10:35:06

START STREAM @ 2019-01-09 10:35:13
0:00:02 - GREEN
0:00:04 - RED
0:00:05 - YELLOW
STOP STREAM @ 2019-01-09 10:35:21


The bit of info I am trying pull out are the timestamps related to either GREEN, RED, or YELLOW (see underlined lines).
I would also like to assign a button that uses Application.GetOpenFilename() so that the user can select the log file to import.

Also, if possible, I'd prefer the Timestamps and the Color be in different Columns/Rows like this. (I don't really need the hypen but if its too complex, we can keep it there)

0:00:02	-	GREEN
0:00:04	-	RED
0:00:05	-	YELLOW

<tbody>
</tbody>

Thanks in advance!
Kevin

 

[Eric W]

Welcome to the posting part of the Board!

Open a new workbook. Press Alt-F11 to open the VBA editor. Press Alt-I-M. Paste the following code into the window that opens:

' This routine requests a file name, opens it as data (a standard textfile
' open will truncate leading spaces), then reads each line and saves
' selected records.
Sub ReadFile()
    Dim MyLine As String, FileNum As Integer, file1, FileLen As Integer, SheetNum As Integer
    
    Cells.ClearContents
    file1 = Application.GetOpenFilename("Any File,*.*", _
            1, "Select log file ", , False)
    If file1 = False Then
        MsgBox "No file selected - exiting"
        Exit Sub
    End If
    
    'Get an unused file number
    FileNum = FreeFile
    
    Open file1 For Input As #FileNum 
    If Err.Number <> 0 Then
        MsgBox "File open error #" & Err.Number & "!", vbCritical, "Error!"
        Exit Sub
    End If
    
    FileLen = 1
    
    Line Input #FileNum , MyLine
    While Not EOF(FileNum)
        If InStr(LCase(MyLine), "green") > 0 Or _
           InStr(LCase(MyLine), "yellow") > 0 Or _
           InStr(LCase(MyLine), "red") > 0 Then
            Cells(FileLen, "A") = MyLine
            FileLen = FileLen + 1
        End If
        Line Input #FileNum , MyLine
    Wend
    If InStr(LCase(MyLine), "green") > 0 Or _
       InStr(LCase(MyLine), "yellow") > 0 Or _
       InStr(LCase(MyLine), "red") > 0 Then
        Cells(FileLen, "A") = MyLine
        FileLen = FileLen + 1
    End If
    
    Close #FileNum 
    
    Range("A:A").TextToColumns DataType:=xlDelimited, OtherChar:="-"


End Sub

Close the editor (Alt-Q). Run the macro by pressing Alt-F8, or you can add it to the Ribbon, or to a button or other object. Let me know how it works.

 

[SpeedyKevin]

Welcome to the posting part of the Board!

Open a new workbook. Press Alt-F11 to open the VBA editor. Press Alt-I-M. Paste the following code into the window that opens:

' This routine requests a file name, opens it as data (a standard textfile
' open will truncate leading spaces), then reads each line and saves
' selected records.
Sub ReadFile()
    Dim MyLine As String, FileNum As Integer, file1, FileLen As Integer, SheetNum As Integer
    
    Cells.ClearContents
    file1 = Application.GetOpenFilename("Any File,*.*", _
            1, "Select log file ", , False)
    If file1 = False Then
        MsgBox "No file selected - exiting"
        Exit Sub
    End If
    
    'Get an unused file number
    FileNum = FreeFile
    
    Open file1 For Input As #FileNum 
    If Err.Number <> 0 Then
        MsgBox "File open error #" & Err.Number & "!", vbCritical, "Error!"
        Exit Sub
    End If
    
    FileLen = 1
    
    Line Input #FileNum , MyLine
    While Not EOF(FileNum)
        If InStr(LCase(MyLine), "green") > 0 Or _
           InStr(LCase(MyLine), "yellow") > 0 Or _
           InStr(LCase(MyLine), "red") > 0 Then
            Cells(FileLen, "A") = MyLine
            FileLen = FileLen + 1
        End If
        Line Input #FileNum , MyLine
    Wend
    If InStr(LCase(MyLine), "green") > 0 Or _
       InStr(LCase(MyLine), "yellow") > 0 Or _
       InStr(LCase(MyLine), "red") > 0 Then
        Cells(FileLen, "A") = MyLine
        FileLen = FileLen + 1
    End If
    
    Close #FileNum 
    
    Range("A:A").TextToColumns DataType:=xlDelimited, OtherChar:="-"


End Sub

Close the editor (Alt-Q). Run the macro by pressing Alt-F8, or you can add it to the Ribbon, or to a button or other object. Let me know how it works.

Hello Eric!

Thank you very much! It was able to import the data I needed but is it possible to also separate the time stamp and Color into seperate columns?

Thanks!
Kevin

 

[SpeedyKevin]

Oh wait nevermind it worked. Much appreciated Eric!

Welcome to the posting part of the Board!

Open a new workbook. Press Alt-F11 to open the VBA editor. Press Alt-I-M. Paste the following code into the window that opens:

' This routine requests a file name, opens it as data (a standard textfile
' open will truncate leading spaces), then reads each line and saves
' selected records.
Sub ReadFile()
    Dim MyLine As String, FileNum As Integer, file1, FileLen As Integer, SheetNum As Integer
    
    Cells.ClearContents
    file1 = Application.GetOpenFilename("Any File,*.*", _
            1, "Select log file ", , False)
    If file1 = False Then
        MsgBox "No file selected - exiting"
        Exit Sub
    End If
    
    'Get an unused file number
    FileNum = FreeFile
    
    Open file1 For Input As #FileNum 
    If Err.Number <> 0 Then
        MsgBox "File open error #" & Err.Number & "!", vbCritical, "Error!"
        Exit Sub
    End If
    
    FileLen = 1
    
    Line Input #FileNum , MyLine
    While Not EOF(FileNum)
        If InStr(LCase(MyLine), "green") > 0 Or _
           InStr(LCase(MyLine), "yellow") > 0 Or _
           InStr(LCase(MyLine), "red") > 0 Then
            Cells(FileLen, "A") = MyLine
            FileLen = FileLen + 1
        End If
        Line Input #FileNum , MyLine
    Wend
    If InStr(LCase(MyLine), "green") > 0 Or _
       InStr(LCase(MyLine), "yellow") > 0 Or _
       InStr(LCase(MyLine), "red") > 0 Then
        Cells(FileLen, "A") = MyLine
        FileLen = FileLen + 1
    End If
    
    Close #FileNum 
    
    Range("A:A").TextToColumns DataType:=xlDelimited, OtherChar:="-"


End Sub

Close the editor (Alt-Q). Run the macro by pressing Alt-F8, or you can add it to the Ribbon, or to a button or other object. Let me know how it works.

 

[Eric W]

The last line is designed to split the time and colors into separate columns by looking for the dash. As long as it's there, it should work.

Glad I could help!

 

[SpeedyKevin]

The last line is designed to split the time and colors into separate columns by looking for the dash. As long as it's there, it should work.

Glad I could help!

So I'm not sure what happened but the macro no longer works? I even copied and pasted it again. It does everything but separate the data now.

 

[Eric W]

It may be that what looks like a dash is actually something else. There are a few characters that look like that. You can try to figure it out by using the CODE function. Or select a row with one of them, select the cell, and from the formula bar, select the dash, copy it (Control-C), then paste that into the last line of the code over the dash. Or just change that last line so that OtherChar:=" " (a space), which will give you 3 columns, with whatever the dash is in the middle one.