Writing vb script to extract data from log file 1

[rlee80]

Hi

I have been given the task of extracting information from an email server log file, I need to extract the info from the file containing users email usage (not content just number of mails in and out) and put this info into an excel file.

I am looking at doing this with vbs and just some helpfull pointers would be great

Thanks in advance

Rob

 

[PHV]

Do a keyword search in this forum for fso excel

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[rlee80]

The format of the file is *.ini do i need to convert this file to a csv file before extracting the data to an excel file, I also think I need to get rid of the square brackets and then read the file line by line an example of the data in the file is here

[name.name@somewhere.mail]
attchin2=119
attchout=0
bytesin2=744446
bytesout=0
filesin2=119
filesout=0

 

[PHV]

You may consider the Left, Mid, InStr and Split functions.
If you're confident with the Excel object model you may even populate directly a sheet without the csv step.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[rlee80]

Hi,

I have the file that i want now test.txt which is a csv file, there are 335 lines in this file with email users names and ins and outs etc. This code creates and formats the excel worksheet ok but when parsing the data directly into the worksheet there are only about 175 lines it seems to miss out a line when it loops through the file.

The format of the file is 335 lines like the one below

2004/06/03,00:02:35,in2,someone@internal.mail,postman@internal.mail,696,C:\MDAEMON\LOCALQ\md50000465133.msg,PostMan

Also I get input past end of file error!!

Thanks in advance

Rob



Option Explicit

Dim objFSO, objTextfile, objNewTextFile, objXL, arrEmailLog, i, count

Const ForReading = 1
'Const ForWriting = 2

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTextfile = objFSO.OpenTextFile("C:\test.txt", ForReading)
'Set objNewTextFile = objFSO.OpenTextFile("C:\results.txt", ForWriting)

Set objXL = WScript.CreateObject("Excel.Application")

objXL.Visible = True

objXL.Workbooks.Add
objXL.Cells(1,1).Value = "Users E-Mail:" 'format excel spreadsheet
objXL.Cells(1,2).Value = "Date"
objXL.Cells(1,3).Value = "Time"
objXL.Cells(1,4).Value = "Direction"
objXL.Cells(1,5).Value = "Size in Bytes"

objXL.Columns(1).ColumnWidth = 30
objXL.Columns(2).ColumnWidth = 10
objXL.Columns(3).ColumnWidth = 10
objXL.Columns(4).ColumnWidth = 10
objXL.Columns(5).ColumnWidth = 12


count=2

Do While objTextfile.AtEndOfStream <> True 'loops through test.txt till end

If inStr(objTextfile.Readline, ",") Then

arrEmailLog = split(objTextFile.Readline, ",") 'create array from textfile

Wscript.Echo "User Name: " & arrEmailLog(3) 'use array to display data needed
Wscript.Echo "Date: " & arrEmailLog(0)
Wscript.Echo "Time: " & arrEmailLog(1)
Wscript.Echo "Direction: " & arrEmailLog(2)
Wscript.Echo "Size Bytes: " & arrEmailLog(5)


objXL.Cells(count,1).Value = arrEmailLog(3) 'use array to populate cells with data needed
objXL.Cells(count,2).Value = arrEmailLog(0)
objXL.Cells(count,3).Value = arrEmailLog(1)
objXL.Cells(count,4).Value = arrEmailLog(2)
objXL.Cells(count,5).Value = arrEmailLog(5)


Else

WScript.Echo "wrong file"

End If

count=count+1
'i=i+1

Loop

objTextFile.Close