• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 414
  • Last Modified:

VBScript that searches several text files for specific data and then write that data to a spreadsheet

We have just finished our pilot of 400 workstations migrating to XP SP2. USMT was the tool used for data migration. We have several progress log files that we want to extract information from, then put that info into a spreadsheet with defined column headers. Here's what i thinking of:
1- browse to or define the folder where the text files reside
2-write the filename of each file in that folder to a spreadsheet (column A)
3-read each file for a line that contains specific data/value
4-write the contents of that entire line into the spreadsheet's row that contains the corresponding filename from (column A).

Ex: If the file contains "end,scanstate"; then write that entire line into the spreadsheet with the data/values (separated by a comma) in the appropriate columns. Same applies for the same file: if contains "end,loadstate."

2006/04/12 20:40:30,XXSOMENMWSJ020L,End,SCANSTATE,0,45868,00:04:31
2006/04/12 22:24:24,XXSOMENMWSJ020L,End,LOADSTATE,0,155006,00:00:48

Essentially, that's the information i want out of the several lines one (1) file will contain. It is possible that a file could contain more than one instance of the "End, Scanstate" or End Loadstate."

As mentioned i have approx 472 files from our pilot. Upon completion there will be several thousand log files that i would need to extrrapolate this info.
  • 2
1 Solution
Robberbaron (robr)Commented:

writing direct to a spreadsheet is a little difficult but appending to a CSV for easy import is good.

dim sLogFolder , oFSO, oFolder, oFile , cFilesLIst
dim sLogFiles , sTagCode , sCSVname , oCSVFile , sResultLine


'search the Logfolder for .log files

'open each log file
'  load each line.  (or load the entire file as string)
'    check for tagcode
'    if tagcode exists, write data to CSV
'  next line
'next file

    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set oFolder = oFSO.GetFolder(sLogFolder)
    if oFSO.FileExists(sLogFolder & "\" & sCSVname) Then
      set oCSVFile = oFSO.OpenTextFile (sLogFolder & "\" & sCSVname,8) 'append
      Set oCSVFile = oFSO.CreateTextFile(sLogFolder & "\" & sCSVname,true,false)
    end if


    oCSVFile.WriteLine ("Filename,Date,Code1,Endx,State,Count1,Count2,tElapsed"  )
                'a relevant header line. Dont use spaces in header tags
    Set cFilesList = oFolder.Files
    For Each oFile in cFilesList
      If oFSO.GetExtensionName(oFile.name)=sLogFiles then
      Set oTextFile = oFile.OpenAsTextStream (1, 0)  'for read

      Do while not oTextFile.AtEndOfStream
                sResultLine = oTextFile.Readline
            if instr(sResultLine,sTagCode1) or instr(sResultLine, sTagCode2) then
                  'append the line to csv
                  oCSVFile.WriteLine(oFile.Name & "," & sResultLine)
            end if
      End if

    set FSO=nothing

Robberbaron (robr)Commented:
'test files
2006/04/12 20:40:30,XXSOMENMWSJ020L,End,SCANSTATE,0,45868,00:04:31
2006/04/12 22:24:24,XXSOMENMWSJ020L,End,LOADSTATE,0,155006,00:00:48
2006/04/12 20:40:30,XXSOMENMWSJ020L,End,SCANSTATE,0,25868,00:04:31
2006/04/12 22:24:24,XXSOMENMWSJ020L,Start,LOADSTATE,0,135006,00:00:48
2006/04/12 22:24:24,XXSOMENMWSJ020L,End,LOADSTATE,0,135006,00:00:48

file1.log,2006/04/12 20:40:30,XXSOMENMWSJ020L,End,SCANSTATE,0,45868,00:04:31
file1.log,2006/04/12 22:24:24,XXSOMENMWSJ020L,End,LOADSTATE,0,155006,00:00:48
file2.log,2006/04/12 20:40:30,XXSOMENMWSJ020L,End,SCANSTATE,0,25868,00:04:31
file2.log,2006/04/12 22:24:24,XXSOMENMWSJ020L,End,LOADSTATE,0,135006,00:00:48

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now