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?
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.