PDA

View Full Version : 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:
17197

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
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,

PFA17200

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