Log File to Excel

Clareman

Hi All,

I've a LOAD of log files I need to go through but the problem is they are all in .log so they open in Notepad, is there anyway to convert the data into something that can be opened in excel for easy reporting?

All the lines have the same "Subject" and they are all in 12 line chunk, for example

<!-- Line1:	0a429fae-d2dd-4bd6-a159-0e826b01641e -->
<!-- Line2:	213 -->
<!-- Line3:	30102 -->
<!-- Line4:	0 -->
<!-- Line 5:	Validate-->
<!-- Line 6:	1.8 -->
<!-- Line 7:AAA = 213,BBB = UB001,CCC = 30102,DDD = sdfsa@sdag.gfs,EEE = LIJhNeCeZZ101plus101RRWb2ic4mug10lUqE0110lUqE01 -->
<!-- Line 8: Access failure-->
<!-- Time Stamp:	7/14/2017 9:50:28 AM -->

 
 
 <!-- Line1:	8a3763fd-ab41-45ed-8cf9-84d33cfaa0e7 -->
<!-- Line2:	213 -->
<!-- Line3:	30102 -->
<!-- Line4:	0 -->
<!-- Line 5:	Validate-->
<!-- Line 6:	1.8 -->
<!-- Line 7:AAA = 213,BBB = UB001,CCC = 30102,DDD = sdfsa@sdag.gfs,EEE = LIJhNeCeZZ101plus101RRWb2ic4mug10lUqE0110lUqE01 -->
<!-- Line 8: Access failure-->
<!-- Time Stamp:	7/14/2017 10:11:41 AM -->

 
 
 <!-- Line1:	719c8aba-4b3f-4080-b9e5-b6fdc6fcc192 -->
<!-- Line2:	213 -->
<!-- Line3:	30102 -->
<!-- Line4:	0 -->
<!-- Line 5:	Validate-->
<!-- Line 6:	1.8 -->
<!-- Line 7:AAA = 213,BBB = UB001,CCC = 30102,DDD = sdfsa@sdag.gfs,EEE = LIJhNeCeZZ101plus101RRWb2ic4mug10lUqE0110lUqE01 -->
<!-- Line 8: Access failure-->
<!-- Time Stamp:	7/14/2017 10:11:45 AM -->

What I would like to do is have 9 columns, Line 1, Line 2, Line 3, etc. etc. which in turn as the data to the right, then in turn I'll be able to filter in Excel.

srsly78

Looks like fixed width format? Just use the excel txt import feature -> https://support.office.com/en-ie/article/Text-Import-Wizard-c5b02af6-fda1-4440-899f-f78bafe41857

Or write a python script to convert it to whatever you like.

Clareman

srsly78 wrote: »

Looks like fixed width format? Just use the excel txt import feature -> https://support.office.com/en-ie/article/Text-Import-Wizard-c5b02af6-fda1-4440-899f-f78bafe41857

Or write a python script to convert it to whatever you like.

A couple of the lines can vary in length depending on the content.

I could do with moving line 1 to column a, line 2 to column b... Up to line 12 then move line 13 to column a and so on

srsly78

Yeah if the auto-wizard thingy won't do it then just write some python tbh. Or VB or <scripting language of choice>.

Read it in as list of tokens, then write it all back out as csv. Excel can easily load it then.

Clareman

srsly78 wrote: »

Yeah if the auto-wizard thingy won't do it then just write some python tbh. Or VB or <scripting language of choice>.

Read it in as list of tokens, then write it all back out as csv. Excel can easily load it then.

Any help with doing this would be greatly appreciated.

pillphil

This is just a glorified ctrl + r...

Done with Excel 2016

If you need to enable regex in excel:
https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops

Step 1: Add VBA reference to "Microsoft VBScript Regular Expressions 5.5"

• Select "Developer" tab (I don't have this tab what do I do?)
• Select "Visual Basic" icon from 'Code' ribbon section
• In "Microsoft Visual Basic for Applications" window select "Tools" from the top menu.
• Select "References"
• Check the box next to "Microsoft VBScript Regular Expressions 5.5" to include in your workbook.
• Click "OK"

Return to excel, click macros beside the visual basic button
Put a name in macro name and click create
Paste in this code

Sub test()
    Dim myFile As String, text As String, textline As String, posLat As Integer, posLong As Integer
    myFile = Application.GetOpenFilename()
    
    Open myFile For Input As #1
    
    Do Until EOF(1)
        Line Input #1, textline
        text = text & textline
    Loop
    
    Close #1
    
    Dim strPattern As String: strPattern = "(-->)*([ ?\n]{1,})*( <!--)"
    Dim StrReplace As String: StrReplace = Chr(10)
    Dim regEx As New RegExp
    
    text = RegThing(text, "(-->)*([ ?\n]{1,})*( <!--)", Chr(10))
    text = RegThing(text, "( *-->)[\n]*( *<!-- *)*", ",")
    text = RegThing(text, " *Line *\d:\t*", "")
    text = RegThing(text, " *Time Stamp:\t*", "")
    text = RegThing(text, "\D{3} =", "")

    
    Export (text)
End Sub

Sub Export(text As String)
    Dim Filename As String, line As String
    Dim i As Integer
    Filename = "C:\Users\Phil" & "\textfile.csv"
    Open Filename For Output As #1
    Print #1, text
    Close #1
End Sub

Public Function RegThing(text, strPattern, StrReplace) As String
    Dim regEx As New RegExp
    
    With regEx
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
            .Pattern = strPattern
    End With
    
    If regEx.test(text) Then
        text = regEx.Replace(text, StrReplace)
    Else
        MsgBox ("Not matched")
    End If
    
    RegThing = text
End Function

You'll need to change this line to a folder you have access to

"C:\Users\Phil" & "\textfile.csv"

This line assumes the format of the lines "AAA = " is representitive (i.e. 3 letters, a space and an equals) and undesirable. delete if unnecessary.

text = RegThing(text, "\D{3} =", "")

Hit the green arrow and it'll ask you to select the log file. It should just run then.

I don't know how representative of the rest of the doc this section is, so it might not work.

Clareman

Looks like that works perfectly, thanks a million