CODEWITHSUNDEEP
excelvbscriptscripting
Satish Shihani
Satish Shihani

Reputation: 471

Extract time-stamps from log file and save to new Excel file

I want to take time-stamp values from a log file which is in text format in my local machine and save these into an Excel file, using VB Scripting.

My log file format is:-

14.000.00.10 - - [07/Mar/2015:16:06:51 -0800] "GET /twiki/bin/rdiff/TWiki/NewUserTemplate?rev1=1.3&rev2=1.2 HTTP/1.1" 200 4523
14.000.00.10 - - [07/Mar/2015:16:10:02 -0800] "GET /mailman/listinfo/hsdivision HTTP/1.1" 200 6291
14.000.00.10 - - [07/Mar/2015:16:11:58 -0800] "GET /twiki/bin/view/TWiki/WikiSyntax HTTP/1.1" 200 7352
14.000.00.10 - - [07/Mar/2015:16:20:55 -0800] "GET /twiki/bin/view/Main/DCCAndPostFix HTTP/1.1" 200 5253
14.000.00.10 - - [07/Mar/2015:16:23:12 -0800] "GET /twiki/bin/oops/TWiki/AppendixFileSystem?template=oopsmore&param1=1.12&param2=1.12 HTTP/1.1"

By taking an ID value which is coming repeatedly in multiple lines, how can I save my timestamp e.g. [07/Mar/2015:16:23:12] from log .txt file to Excel file?

I tried to code this:

Set xl = CreateObject("Excel.Application")
xl.Visible = True

Set wb = xl.Workbooks.Add
Set ws = wb.Sheets(1)

Const ForReading = 1

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("E:\access_log.txt")

strContents = objFile.ReadAll

objFile.Close

Set objRegEx = CreateObject("VBScript.RegExp")
objRegEx.IgnoreCase = True
objRegEx.Global = True
objRegEx.Pattern = "mailman"

Set colMatches = objRegEx.Execute(strContents)  

For Each Match in colMatches
    strReturnStr = "Match found at position "
    strReturnStr = strReturnStr & match.FirstIndex & ". Match Value is '"
    StrReturnStr = strReturnStr & match.value & "'." & "<BR>" & VBCrLf
    WScript.Echo(strReturnStr)
Next
wb.SaveAs "E:\access_og.csv", -4143, , , , False
wb.Close
xl.Quit

While running with cscript name.vbs on cmd prompt it is showing line numbers where string found, and after that .csv file is opening with error "the file format and extension of 'access_og.csv' don't match. the file could be corrupted and unsafe."

Still problem not solved :(

Upvotes: 3

Views: 355

Answers (1)

saurabh dadhich
saurabh dadhich

Reputation: 59

Can you try this:-

Dim objFSO, strTextFile, strData, strLine, arrLines
CONST ForReading = 1
'name of the text file
strTextFile = "E:\access_log.txt"
'Create a File System Object
Set objFSO = CreateObject("Scripting.FileSystemObject")
'Open the text file - strData now contains the whole file 
strData = objFSO.OpenTextFile(strTextFile,ForReading).ReadAll
'Split the text file into lines
arrLines = Split(strData,vbCrLf)
Set objExcel = CreateObject("Excel.Application")
'Mentioning the path of the excel sheet.
Set objWorkbook = objExcel.Workbooks.Open("Your Excel File Path")
'Mentioning the worksheet which is going to be used.
Set objWorkSheet = objWorkbook.Worksheets("Sheet1")

'This control will define the  view of  the excel sheet. Set it to "True" if you want to see the excel sheet. Set it to "False" if you don't want to view the excel sheet.

objExcel.Application.Visible = True

'We will assign a varaible called rowcount to mention the number of used rows.

rowcount = objWorkSheet.usedrange.rows.count
rowcount = rowcount + 1
objExcel.Visible = True

'Step through the lines
For Each strLine in arrLines
 intLine = InStr(1,strLine,"mailman",1)

 if intLine > 0 Then
  objExcel.Cells(rowcount, 1).Value = strLine
  rowcount = rowcount + 1
 End IF
 objWorkbook.Save
Next
objWorkbook.Save
objWorkbook.quit
Set objFSO = Nothing
result=Msgbox("Completed",0)

Before running it make sure that you have excel file created with the same name given in "Excel file path".

Upvotes: 2

Related Questions