excel - 从日志文件中提取时间戳并保存到新的 Excel 文件

Question

我想从本地计算机上的文本格式日志文件中获取时间戳值，并使用 VB 脚本将其保存到 Excel 文件中。

我的日志文件格式是:-

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 452314.000.00.10 - - [07/Mar/2015:16:10:02 -0800] "GET /mailman/listinfo/hsdivision HTTP/1.1" 200 629114.000.00.10 - - [07/Mar/2015:16:11:58 -0800] "GET /twiki/bin/view/TWiki/WikiSyntax HTTP/1.1" 200 735214.000.00.10 - - [07/Mar/2015:16:20:55 -0800] "GET /twiki/bin/view/Main/DCCAndPostFix HTTP/1.1" 200 525314.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

在 cmd 提示符下使用 cscript name.vbs 运行时，它显示找到字符串的行号，之后 .csv 文件打开时出现错误“‘access_og.csv’的文件格式和扩展名不正确”匹配。该文件可能已损坏且不安全。”

问题仍然没有解决:(

Answer 1

你能试试这个吗:-

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)

在运行之前，请确保您已使用“Excel 文件路径”中指定的相同名称创建了 Excel 文件。