Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import data from a log file
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!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import data from a log file
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!! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import data from a log file
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 "Yuri S" wrote in message ... 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!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Import data from one file to another | Excel Discussion (Misc queries) | |||
import data from txt file to an existing excel file | Excel Discussion (Misc queries) | |||
Import File Data | Excel Worksheet Functions | |||
How do I import text file, analyze data, export results, open next file | Excel Programming | |||
Get External Data, Import Text File, File name problem | Excel Programming |