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

Posted on 2006-05-05
Last Modified: 2008-02-01
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.
Question by:michaeldphillips
    LVL 32

    Accepted Solution


    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( 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

    LVL 32

    Expert Comment

    by:Robberbaron (robr)
    '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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    I guess it is not common knowledge to most Wintel engineers/administrators: If you have an SNMP-based monitoring system in your environment (and it's common to have SNMP or Syslog) it's reasonably easy to enable monitoring of the Windows Event logs,…
    Setting up a Microsoft WSUS update system is free relatively speaking if you have hard disk space and processor capacity.   However, WSUS can be a blessing and a curse. For example, there is nothing worse than approving updates and they just have…
    This video discusses moving either the default database or any database to a new volume.
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now