parsing txt log to excel table

**enfapro7** · 09-28-2016, 05:17 AM

Dear All,

can you help me to copy and paste this text from txt file to excel table who i have already created the header

my txt file contains text like this:

DSP VSWR:;
3323780G_3G_KENDARI_AIRPORT
+++ 3323780G_3G_KENDARI_AIRPORT 2016-09-27 17:25:29
O&M #537941448
%%/*162983383*/DSP VSWR:;%%
RETCODE = 0 Operation succeeded.

Dsp VSWR Test Result
--------------------
Cabinet No. Subrack No. Slot No. TX Channel No. VSWR(0.01)

0 4 1 0 110
0 4 3 0 110
0 4 5 0 110
0 5 1 0 110
0 5 3 0 110
0 5 5 0 110
(Number of results = 6)

--- ENDDSP VSWR:;
3323860G_3G_UNAAHA
+++ 3323860G_3G_UNAAHA 2016-09-27 17:23:12
O&M #537970299
%%/*162983380*/DSP VSWR:;%%
RETCODE = 0 Operation succeeded.

Dsp VSWR Test Result
--------------------
Cabinet No. Subrack No. Slot No. TX Channel No. VSWR(0.01)

0 4 1 0 110
0 4 3 0 110
0 4 5 0 110
0 5 1 0 110
0 5 3 0 110
0 5 5 0 110
(Number of results = 6)

--- END

i want the result in excel like this:
VSWRs.JPG

**Paul_Hossler** · 09-28-2016, 07:52 AM

my txt file contains text like this:

It's a lot easier to help if you attach a sample file with enough data to test.

Bottom right, click [Go Advanced] and use the paper clip icon

**enfapro7** · 09-28-2016, 08:07 AM

Originally Posted by Paul_Hossler

It's a lot easier to help if you attach a sample file with enough data to test.

Bottom right, click [Go Advanced] and use the paper clip icon

Dear,

i have trouble uploading txt file, but i have paste it in my previous quote

**Paul_Hossler** · 09-28-2016, 08:30 AM

Sorry, but the txt file would be what is required in order to see the data format

If it won't let you upload a CSV or TXT file, then try renaming it to (for example) MyData.txt.xlsx

**enfapro7** · 09-28-2016, 08:42 AM

Dear,

PFAVSWR PALUS.txt.csv

**Paul_Hossler** · 09-28-2016, 12:10 PM

Try something like this

Option Explicit

Sub ParseLog()
    Dim iFile As Long, iOut As Long
    Dim sFile As String, sLine As String
    Dim wsSummary As Worksheet
    Dim vSplit As Variant
    Dim bNew As Boolean
    
    'get file name
    sFile = Application.GetOpenFilename("Log Files (*.txt;*.csv), *.txt;*.csv")
    If sFile = "False" Then Exit Sub
    
    
    'setup
    Application.ScreenUpdating = False
    iOut = 2
    Set wsSummary = Worksheets("Summary")
    Range(wsSummary.Cells(2, 1), wsSummary.Cells(2, 1).End(xlDown)).EntireRow.Delete
    
    
    
    'open file
    iFile = FreeFile
    Open sFile For Input As #iFile
    
    Do While Not EOF(iFile)
        Line Input #iFile, sLine

        If Left(sLine, 3) = "+++" Then
            Do While InStr(sLine, "  ") > 0
                sLine = Replace(sLine, "  ", " ")
            Loop
            vSplit = Split(sLine, " ")
            wsSummary.Cells(iOut, 1) = vSplit(1)
            wsSummary.Cells(iOut, 2) = vSplit(2)
            wsSummary.Cells(iOut, 3) = vSplit(3)
            bNew = True
            
            
        ElseIf Left(sLine, 7) = "Cabinet" Then
            'read blank line
            Line Input #iFile, sLine
            
            Line Input #iFile, sLine
            Do While Left(sLine, 7) <> "(Number"
                
                sLine = Trim(sLine)
                Do While InStr(sLine, "  ") > 0
                    sLine = Replace(sLine, "  ", " ")
                Loop
            
                vSplit = Split(sLine, " ")
                
                wsSummary.Cells(iOut, 4) = vSplit(0)
                wsSummary.Cells(iOut, 5) = vSplit(1)
                wsSummary.Cells(iOut, 6) = vSplit(2)
                wsSummary.Cells(iOut, 7) = vSplit(3)
                wsSummary.Cells(iOut, 8) = vSplit(4)
                
                iOut = iOut + 1
                Line Input #iFile, sLine
            Loop
        
        End If
    Loop
    Close #iFile

    Application.StatusBar = False
    Application.ScreenUpdating = True

End Sub

**enfapro7** · 09-28-2016, 12:50 PM

Originally Posted by Paul_Hossler

Try something like this

Option Explicit

Sub ParseLog()
    Dim iFile As Long, iOut As Long
    Dim sFile As String, sLine As String
    Dim wsSummary As Worksheet
    Dim vSplit As Variant
    Dim bNew As Boolean
    
    'get file name
    sFile = Application.GetOpenFilename("Log Files (*.txt;*.csv), *.txt;*.csv")
    If sFile = "False" Then Exit Sub
    
    
    'setup
    Application.ScreenUpdating = False
    iOut = 2
    Set wsSummary = Worksheets("Summary")
    Range(wsSummary.Cells(2, 1), wsSummary.Cells(2, 1).End(xlDown)).EntireRow.Delete
    
    
    
    'open file
    iFile = FreeFile
    Open sFile For Input As #iFile
    
    Do While Not EOF(iFile)
        Line Input #iFile, sLine

        If Left(sLine, 3) = "+++" Then
            Do While InStr(sLine, "  ") > 0
                sLine = Replace(sLine, "  ", " ")
            Loop
            vSplit = Split(sLine, " ")
            wsSummary.Cells(iOut, 1) = vSplit(1)
            wsSummary.Cells(iOut, 2) = vSplit(2)
            wsSummary.Cells(iOut, 3) = vSplit(3)
            bNew = True
            
            
        ElseIf Left(sLine, 7) = "Cabinet" Then
            'read blank line
            Line Input #iFile, sLine
            
            Line Input #iFile, sLine
            Do While Left(sLine, 7) <> "(Number"
                
                sLine = Trim(sLine)
                Do While InStr(sLine, "  ") > 0
                    sLine = Replace(sLine, "  ", " ")
                Loop
            
                vSplit = Split(sLine, " ")
                
                wsSummary.Cells(iOut, 4) = vSplit(0)
                wsSummary.Cells(iOut, 5) = vSplit(1)
                wsSummary.Cells(iOut, 6) = vSplit(2)
                wsSummary.Cells(iOut, 7) = vSplit(3)
                wsSummary.Cells(iOut, 8) = vSplit(4)
                
                iOut = iOut + 1
                Line Input #iFile, sLine
            Loop
        
        End If
    Loop
    Close #iFile

    Application.StatusBar = False
    Application.ScreenUpdating = True

End Sub

Dear,

SUPERB.. THANKS.. YOUR GREAT..

**Paul_Hossler** · 09-28-2016, 03:33 PM

This is a slightly improved (I hope) version

It's a tiny bit faster and a little more robust

Option Explicit

Sub ParseLog()
    Dim iFile As Long, iOut As Long
    Dim sFile As String, sLine As String
    Dim wsSummary As Worksheet
    Dim vSplit As Variant
    
    'get file name
    sFile = Application.GetOpenFilename("Log Files (*.txt;*.csv), *.txt;*.csv")
    If sFile = "False" Then Exit Sub
    
    
    'setup
    Application.ScreenUpdating = False
    iOut = 2
    Set wsSummary = Worksheets("Summary")
    With wsSummary
        Range(.Cells(2, 1), .Cells(.Rows.Count, 1).End(xlDown)).EntireRow.Delete
    End With
    
    
    'open file
    iFile = FreeFile
    Open sFile For Input As #iFile
    
    Do While Not EOF(iFile)
        Line Input #iFile, sLine
        If Left(sLine, 3) = "+++" Then
            sLine = Application.WorksheetFunction.Trim(sLine)
            
            vSplit = Split(sLine, " ")
            wsSummary.Cells(iOut, 1) = vSplit(1)
            wsSummary.Cells(iOut, 2) = vSplit(2)
            wsSummary.Cells(iOut, 3) = vSplit(3)
            
            
        ElseIf Left(sLine, 7) = "Cabinet" Then
            'read blank line
            Line Input #iFile, sLine
            
            Line Input #iFile, sLine
            Do While Left(sLine, 7) <> "(Number"
                
                sLine = Application.WorksheetFunction.Trim(sLine)
            
                vSplit = Split(sLine, " ")
                
                wsSummary.Cells(iOut, 4) = vSplit(0)
                wsSummary.Cells(iOut, 5) = vSplit(1)
                wsSummary.Cells(iOut, 6) = vSplit(2)
                wsSummary.Cells(iOut, 7) = vSplit(3)
                wsSummary.Cells(iOut, 8) = vSplit(4)
                
                iOut = iOut + 1
                Line Input #iFile, sLine
            Loop
        
        End If
    Loop
    Close #iFile
    Application.StatusBar = False
    Application.ScreenUpdating = True

End Sub

**enfapro7** · 10-11-2016, 08:34 PM

Originally Posted by Paul_Hossler

This is a slightly improved (I hope) version

It's a tiny bit faster and a little more robust

Option Explicit

Sub ParseLog()
    Dim iFile As Long, iOut As Long
    Dim sFile As String, sLine As String
    Dim wsSummary As Worksheet
    Dim vSplit As Variant
    
    'get file name
    sFile = Application.GetOpenFilename("Log Files (*.txt;*.csv), *.txt;*.csv")
    If sFile = "False" Then Exit Sub
    
    
    'setup
    Application.ScreenUpdating = False
    iOut = 2
    Set wsSummary = Worksheets("Summary")
    With wsSummary
        Range(.Cells(2, 1), .Cells(.Rows.Count, 1).End(xlDown)).EntireRow.Delete
    End With
    
    
    'open file
    iFile = FreeFile
    Open sFile For Input As #iFile
    
    Do While Not EOF(iFile)
        Line Input #iFile, sLine
        If Left(sLine, 3) = "+++" Then
            sLine = Application.WorksheetFunction.Trim(sLine)
            
            vSplit = Split(sLine, " ")
            wsSummary.Cells(iOut, 1) = vSplit(1)
            wsSummary.Cells(iOut, 2) = vSplit(2)
            wsSummary.Cells(iOut, 3) = vSplit(3)
            
            
        ElseIf Left(sLine, 7) = "Cabinet" Then
            'read blank line
            Line Input #iFile, sLine
            
            Line Input #iFile, sLine
            Do While Left(sLine, 7) <> "(Number"
                
                sLine = Application.WorksheetFunction.Trim(sLine)
            
                vSplit = Split(sLine, " ")
                
                wsSummary.Cells(iOut, 4) = vSplit(0)
                wsSummary.Cells(iOut, 5) = vSplit(1)
                wsSummary.Cells(iOut, 6) = vSplit(2)
                wsSummary.Cells(iOut, 7) = vSplit(3)
                wsSummary.Cells(iOut, 8) = vSplit(4)
                
                iOut = iOut + 1
                Line Input #iFile, sLine
            Loop
        
        End If
    Loop
    Close #iFile
    Application.StatusBar = False
    Application.ScreenUpdating = True

End Sub

Hi Bro,

i need little help, how about we have two or more txt file and we want to parse at once?

Thread: parsing txt log to excel table

Thread Tools

Display

parsing txt log to excel table

Posting Permissions