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


"Yuri S" wrote:

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