Import data from a log file

[Yuri S]

Hi,

I am trying to import data from a firewall log into excel sheet but the
logfile (txt) has rows which contains fieldnames seperated with the = signs.
The values and fieldnames also contains spaces see example below. Can anybody
help me to write a macro so I can import the data into Excel?

Example:

start_time="2007-09-20 15:29:33" duration=59 policy_id=2
service=udp/port:1900 proto=17 src zone=Trust dst zone=Untrust action=Permit
sent=343 rcvd=0 src=1.2.3.4 dst=4.3.2.1 src_port=1487 dst_port=1900
src-xlated ip=1.0.1.0 port=12570<000>
start_time="2007-09-20 15:29:33" duration=59 policy_id=2
service=udp/port:1900 proto=17 src zone=Trust dst zone=Untrust action=Permit
sent=402 rcvd=0 src=1.2.3.4 dst=4.3.2.1 src_port=1488 dst_port=1900
src-xlated ip=1.0.1.0 port=11673<000>
start_time="2007-09-20 15:29:33" duration=59 policy_id=2
service=udp/port:1900 proto=17 src zone=Trust dst zone=Untrust action=Permit
sent=408 rcvd=0 src=1.2.3.4 dst=4.3.2.1 src_port=1489 dst_port=1900
src-xlated ip=1.0.1.0 port=12423<000>
start_time="2007-09-20 15:29:33" duration=59 policy_id=2
service=udp/port:1900 proto=17 src zone=Trust dst zone=Untrust action=Permit
sent=396 rcvd=0 src=1.2.3.4 dst=4.3.2.1 src_port=1490 dst_port=1900
src-xlated ip=1.0.1.0 port=12116<000>

Thanks!!

 

[Joel]

Below is a macro that will turn the log file into a CSV file. Then you can
import the CSV file into excel. I assume you will want the data placed the
Field name as the header column and place only field data below the
corresponding header. You have a choice of importing the data into excel and
then move the data to correct columns, or edit the data in the log file so it
will automatically import to correct columns.

This macro can be modified to include addtional features.
1) Right now it removes the double quotes from the file so the data and time
will get put into excel as real data and time instead of a text stgtring
2) The macro replaces equal sign with a comma so tthe field name and field
data get put into seperate cells
3) Program can be moidified to put field name and field data one one line by
adding a CR at the end of each pair of data. This will be the two fields in
column A and B.
4) Program can be moidified so that wh start time is seen it starts to put
data in the next row of excel. all data from Start time to port number will
be on one row.
5) Program can be modified to create a header row of each field. Then put
each field data under the correct column.

Sub Getfixedtext()

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const MyPath = "C:\temp\"
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0


Set fsread = CreateObject("Scripting.FileSystemObject")
Set fswrite = CreateObject("Scripting.FileSystemObject")

ReadFileName = "firewall.log"
WriteFileName = "firewall.csv"


'open files
ReadPathName = MyPath + ReadFileName
Set fread = fsread.GetFile(ReadPathName)
Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault)

WritePathName = MyPath + WriteFileName
fswrite.CreateTextFile WritePathName
Set fwrite = fswrite.GetFile(WritePathName)
Set tswrite = fwrite.OpenAsTextStream(ForWriting, TristateUseDefault)


Do While tsread.atendofstream = False
InputLine = tsread.Readline
OutputLine = ""
FoundSpace = False
For CharCount = 1 To Len(InputLine)
Newchar = Mid(InputLine, CharCount, 1)

Select Case Newchar
Case "="
OutputLine = OutputLine & ","
Case " "
If FoundSpace = False Then
OutputLine = OutputLine & ","
FoundSpace = True
End If
Case """"
'remove double quotes
Case Else
FoundSpace = False
OutputLine = OutputLine & Newchar
End Select

Next CharCount

tswrite.WriteLine OutputLine

Loop


tswrite.Close
tsread.Close

End Sub

 

[kounoike]

I'm not sure this will work in your case, but try this one.

Sub importlog()
Dim QuoteIN As Long
Dim HeadIn As Long
Dim i As Long, j As Long, pos As Long
Dim strdata As String
Dim HBuf As String, dBuf As String, s As String
Dim Filename
Dim Filenum

Filename = Application.GetOpenFilename _
(FileFilter:= _
"Text file (*.prn;*.txt;*.csv;*.log),*.prn;*.txt;*.csv;*.log")
If Filename = "False" Then
Exit Sub
End If
Application.ScreenUpdating = False

Filenum = FreeFile()
Open Filename For Input As #Filenum
i = 1

Do While Not EOF(Filenum)
Line Input #Filenum, strdata
j = 1
pos = 1
HeadIn = 1
QuoteIN = 0
Do While (pos <= Len(strdata))
s = Mid(strdata, pos, 1)
If HeadIn = 1 Then
If s = "=" Then
If i = 1 Then
Cells(i, j) = HBuf
End If
HBuf = ""
HeadIn = 0
Else
HBuf = HBuf + s
End If
pos = pos + 1
Else
If s = " " Then
If QuoteIN = 1 Then
dBuf = dBuf + s
Else
Cells(i + 1, j) = dBuf
j = j + 1
dBuf = ""
HeadIn = 1
End If
pos = pos + 1
Else
If s = """" Then
If QuoteIN = 0 Then
QuoteIN = 1
pos = pos + 1
Else
If Mid(strdata, pos + 1, 1) = """" Then
dBuf = dBuf + s
pos = pos + 2
Else
QuoteIN = 0
pos = pos + 1
End If
End If
Else
dBuf = dBuf + s
pos = pos + 1
End If
End If
End If
Loop
Cells(i + 1, j) = dBuf
dBuf = ""
i = i + 1
If i + 1 > Cells.Rows.Count And Not EOF(Filenum) Then
Worksheets.Add after:=ActiveSheet
i = 1
End If
Loop
End Sub

keizi