Results 1 to 2 of 2

Thread: VBScript for parsing multiple text files

  1. #1
    Join Date
    Apr 2010
    Posts
    1

    question VBScript for parsing multiple text files

    Hi,

    I have around 175 text files that contain inventory information that I am trying to parse into an Excel file. We are upgrading our Office platform from 2003 to 2010 and my boss wants to know which machines will have trouble supporting it. I found a script that will parse a single text file based upon ":" as the delimiter and I'm having trouble figuring out how to change it to open an entire folder of text files and write all of the data to a single Excel spreadsheet. Here is an example of the text file I'll be parsing. I'm interested in the "Memory and Processor Information" and "Disk Drive Information" sections mainly.

    SOMECOMPUTER-XP Computer Inventory
    ******************************************

    ******************************************
    OS Information
    ******************************************

    OS Details
    Caption: Microsoft Windows XP Professional
    Description:
    InstallDate: 20070404123855.000000-240
    Name: Microsoft Windows XP Professional|C:\WINDOWS|\Device\Harddisk0\Partition1
    Organization: Your Mom
    OSProductSuite:
    RegisteredUser: Bob
    SerialNumber: 55274-640-3763826-23029
    ServicePackMajorVersion: 3
    ServicePackMinorVersion: 0
    Version: 5.1.2600
    WindowsDirectory: C:\WINDOWS

    ******************************************
    Memory and Processor Information
    ******************************************

    504MB Total memory HOW CAN I PULL THIS WITHOUT ":" ALSO
    Computer Model: HP d330 uT(DG291A)
    Processor: Intel(R) Pentium(R) 4 CPU 2.66GHz

    ******************************************
    Disk Drive Information
    ******************************************

    27712MB Free Disk Space ANY WAY TO PULL THIS WITHOUT ":"
    38162MB Total Disk Space

    ******************************************

    Installed Software
    ******************************************

    Here is the start of the script I have so far. . .


    Const ForReading = 1

    Set objDict = CreateObject("Scripting.Dictionary")

    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objTextFile = objFSO.OpenTextFile("C:\Test\test.txt",ForReading) WANT THIS TO BE C:\Test

    Do Until objTextFile.AtEndOfStream
    strLine = objTextFile.ReadLine
    If Instr(strLine,":") Then
    arrSplit = Split(strLine,":") IS ":" THE BEST DELIMITER TO USE?
    strField = arrSplit(0)
    strValue = arrSplit(1)
    If Not objDict.Exists(strField) Then
    objDict.Add strField,strValue
    Else
    objDict.Item(strField) = objDict.Item(strField) & "||" & strValue
    End If
    End If
    Loop

    objTextFile.Close

    Set objExcel = CreateObject("Excel.Application")
    objExcel.Visible = True
    objExcel.Workbooks.Add

    intColumn = 1

    For Each strItem In objDict.Keys
    objExcel.Cells(1,intColumn) = strItem
    intColumn = intColumn + 1
    Next

    intColumn = 1

    For Each strItem In objDict.Items
    arrValues = Split(strItem,"||")
    intRow = 1
    For Each strValue In arrValues
    intRow = intRow + 1
    objExcel.Cells(intRow,intColumn) = strValue
    Next
    intColumn = intColumn + 1
    Next

    Thank you for any help.

  2. #2
    Join Date
    Dec 2009
    Posts
    23

    Re: VBScript for parsing multiple text files

    This is not vbscript, but may be helpful. This script is in biterscripting. It will create a tab-separated Excel file from all *.txt files under a given directory. The excel file will be created in this format - one entry per line.

    Code:
    user <tab> total memory <tab> comp model <tab> processor
            <tab> free disk space < tab> total disk space
    The script will handle the presence or absence of colon :. I am adding comments to the script so you can translate it to vbscript if necessary.



    Code:
    # Script CompInventory.txt
    # Input arguments
    var string dir   # Top directory under which to search *.txt files.
    var string outfile # Path to the output file
    
    var string list, file, input, output
    var str user, totmem, compmodel, processor, freediskspace, totdiskspace
    
    cd $dir                                     # Change directory.
    lf -n "*.txt" > $list                      # Collect list of *.txt files.
    
    while ($list <> "")                        # Process files one by one.
    do
    
        lex "1" $list > $file                   # Get the next file.
        cat $file > $input                     # Read file into a string variable
    
        # Discard everything upto "RegisteredUser:"
        stex -c "^RegisteredUser:^]" $input > null
        stex "]^\n^" $input > $user # Till the newline is the user
    
        # Discard everything upto total memory line
        stex -r -c "^Memory and Processor Information&\n&\n&\n^]" $input > null
        wex "1" $input > $totmem         # Next word is total memory
        stex -r "^\n&\:^]" $input > null            # Discard up to next colon
        stex "]^\n^" $input > $compmodel # Till the newline is the computer model
        stex "^:^]" $input > null           # Discard up to next colon
        stex "]^\n^" $input > $processor # Till the next newline is processor
    
        # Discard everything upto free disk space line
        stex -r -c "^Disk Drive Information&\n&\n&\n^]" $input > null
        wex "1" $input > $freediskspace # Next word is free disk space
        stex "^\n^]" $input > null           # Discard up to next line
        wex "1" $input > $totdiskspace # Next word is total disk space
    
        # We have all the info items. Write to $output. Also show it to user.
        echo $user "\t" $totmem "\t" $compmodel "\t" $processor "\t" $freediskspace "\t" $totdiskspace
        echo $user "\t" $totmem "\t" $compmodel "\t" $processor "\t" $freediskspace "\t" $totdiskspace >> $output
    done
    
    # Write output to output file given by user.
    echo $output > { echo $outfile }

    I have tested the script. To test, copy and paste the script code into file C:/Scripts/CompInventory.txt, start biterscripting, and copy and paste this command


    Code:
    script "C:/Scripts/CompInventory.txt" dir("C:/test") outfile("C:/test/output.xls")
    This is assuming C:/test is where the test files are. The output will be shown on screen as well as written to file C:/test/output.xls. Open the output .xls file as usual.

Similar Threads

  1. How to rename files based on other files with VBScript, help
    By Thomas Hewitt in forum Software Development
    Replies: 2
    Last Post: 07-09-2012, 11:13 AM
  2. Text parsing
    By cylinder in forum Software Development
    Replies: 3
    Last Post: 21-04-2011, 12:23 AM
  3. VBScript to split a text file evenly in three
    By Kazz013 in forum Software Development
    Replies: 3
    Last Post: 16-12-2010, 06:35 PM
  4. batch file to split multiple text files in half.
    By yammyguy in forum Windows Server Help
    Replies: 8
    Last Post: 11-05-2009, 06:19 PM
  5. Replies: 5
    Last Post: 22-12-2007, 07:52 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Page generated in 1,711,712,706.10804 seconds with 17 queries