extracting data from lines in a log file

hondahawkrider

New Member
Joined
Nov 12, 2015
Messages
8
Having a little difficulty...and was hoping to be pointed in the right direction
I have a huge amdas log file with lots of data - several thousand rows
I am looking to parse two lines so i could do a comparison between the timestamp
The beginning line is
INFO : 2020-11-06 13:58:54.814: (thread: 1333782272): ==> Received payload from Postfix
The ending line is
INFO : 2020-11-06 13:58:55.449: (thread: 1333782272): ==> TRANSACTION completed.

There are several lines in-between and several instances per file - the unique values are "Received payload from Postfix" and "TRANSACTION completed" …
Where I am attempting to get the Received time stamp into column A and completed time stamp to column B..
I can import the file, not problem but having issues with the values, and help is appreciated... My macro skill are rusty..
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
TimeStamp is calculated and retained as "Double" to maintain accuracy
Format the cells in your worksheet to show as date

To test ...
... Insert these CallFunc and GetTimeStamp in the same module and run CallFunc
VBA Code:
Sub CallFunc()
    Const Alpha = "INFO : 2020-11-06 13:58:54.814: (thread: 1333782272): ==> Received payload from Postfix"
    Const Omega = "INFO : 2020-11-06 13:58:55.449: (thread: 1333782272): ==> TRANSACTION completed."
    Range("A1") = GetTimeStamp(Alpha)
    Range("B1") = GetTimeStamp(Omega)
End Sub

Private Function GetTimeStamp(LineOfText As Variant) As Double
    Dim d, t
    d = Split(Split(LineOfText)(2), "-")
    d = DateSerial(d(0), d(1), d(2))
    t = Split(Split(LineOfText)(3), ":")
    t = (t(0) * 60 * 60 + t(1) * 60 + t(2)) / 86400
    GetTimeStamp = d + t
End Function

Importing first and last line
You said that you have already imported the 2 lines so you do not need code below
This is probably a different method to extract first and last line from a textfile
Rich (BB code):
Sub ReadTextFile()
    Dim a As Long, myFile As String, TextLine As String, Alpha, Omega
    myFile = "C:\test\Folder\subfolder\FileXXX.txt"
    Open myFile For Input As #1
    Do Until EOF(1)
        Line Input #1, TextLine
        If a = 0 Then Alpha = TextLine
        Omega = TextLine
        a = 1
    Loop
    Close #1
    MsgBox GetTimeStamp(Alpha) & vbCr & GetTimeStamp(Omega)
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,127,765
Messages
5,626,742
Members
416,201
Latest member
brianhf

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top