[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 457
  • Last Modified:

Parsing out Batch Files into xls. using VBS

I have a batch file that needs to be broken into multiple parts (cells) and printed unto a spread sheet. It's part of a side by side analysis being done across a number of systems.

USE CASE

1. Cell one will have the entire string copied and pasted
2. Cell two will have ONLY the directory path (i.e. "D:\Program Files\PIPC\Interfaces\OPCInt\opcint.exe")
3. Cell three and on will have everything AFTER the path parsed by each switch or "/" (forward slash).

See attached for an idea of how the output would appear.

REM  New Command Line: 1/27/2010 12:44:43 PM [PI-ICU 1.4.7.0][opcint ICU Control 2.3.11.0]
"D:\Program Files\PIPC\Interfaces\OPCInt\opcint.exe" 1 /AF=N /AM=100 /AR=Y /CO=N /CR=N /DA=CONNECT /DI=IDENTIFY /DB=0 /DLL="D:\Program Files\PIPC\Interfaces\OPCInt\Plug-Ins\OPCPluginBitmask.dll" /DLLDB=0 /ER=00:00:01 /ES=CACHE /FM=1 /GL=Y /GS=Y /IF=N /IS=N /IT=N /MA=N /NT=N /SQ=I /TS=N /UWQ=0 /VN=2 /PS=OPC /ID=1 /host=ST-PI:5450 /ec=2 /maxstoptime=120 /perf=8 /UFO_SYNC="\\st-pi-int2\UFO2 Files Share\opcint_OPC_1.dat" /UFO_TYPE=HOT /UFO_ID=1 /UFO_OtherID=2 /f=00:00:01 /f=00:00:01 /f=00:00:01 /f=00:00:01 /f=00:00:01 /f=00:00:01 /f=00:00:01 /f=00:00:01 /f=00:00:01 /f=00:00:01 /f=00:00:01 /f=00:00:01 /f=00:00:01 /f=00:00:01 /f=00:00:01 /f=00:00:01 /f=00:00:01 /f=00:00:01 /f=00:00:01 /f=00:00:01 /f=00:00:01 /f=00:00:01 /f=00:00:01 /f=00:00:01 /f=00:00:01 /f=00:00:01 /f=00:00:01 /f=00:00:01 /f=00:00:01 /f=00:00:01 /f=00:00:01 /f=00:00:01 /f=00:00:01
parsingbatfiles.jpg
0
NanohurtzChrome
Asked:
NanohurtzChrome
  • 3
  • 2
1 Solution
 
Joe OvermanEngineerCommented:
The attached code take yours cursor location (presumably the cell with the data in it) and parses the string as you requested directly below the active cell (assuming that cell is empty).
Function ParseCell()
    
    Dim lngCounter As Long
    Dim lngCount As Long
    Dim lngPointer As Long
    Dim strText As String
    Dim blnPathDone As Boolean
    Dim blnBetweenDone As Boolean
    
    lngPointer = 0
    strText = ActiveCell.Value
    'loop through entire text string
    For lngCounter = 1 To Len(strText)
        'pull the file path based on the Quotation marks
        If Mid(strText, lngCounter, 1) = Chr$(34) And lngPointer = 0 And blnPathDone = False Then
            lngPointer = lngCounter
        End If
        If Mid(strText, lngCounter, 1) = Chr$(34) And lngCounter > lngPointer And blnPathDone = False Then
            Call strFindOpenCell
            ActiveCell.Value = Mid(strText, lngPointer, lngCounter - lngPointer)
            blnPathDone = True
            lngPointer = 0
        End If
        'pull the data between path and first forward slash '/'
        If blnPathDone = True And blnBetweenDone = False Then
            For lngCount = lngCounter To Len(strText)
                If Mid(strText, lngCount, 1) = "/" Then
                    Call strFindOpenCell
                    ActiveCell.Value = Mid(strText, lngCounter + 1, lngCount - lngCounter - 1)
                    blnBetweenDone = True
                    Exit For
                End If
            Next lngCount
        End If
        'pull the parsed componets based on the forward slash '/'
        If Mid(strText, lngCounter, 1) = "/" And lngPointer = 0 Then
            lngPointer = lngCounter
        End If
        If Mid(strText, lngCounter, 1) = "/" And lngCounter > lngPointer Then
            Call strFindOpenCell
            ActiveCell.Value = Mid(strText, lngPointer, lngCounter - lngPointer)
            lngPointer = lngCounter
        End If
    Next lngCounter
End Function

Function strFindOpenCell() As String
    'find the first avaliable cell in columnA
    Dim intRow As Integer
    Dim intCol As Integer
    
    intRow = ActiveCell.Row
    intCol = ActiveCell.Column
    While Cells(intRow, intCol).Value <> ""
        intRow = intRow + 1
    Wend
    Cells(intRow, intCol).Select
End Function

Open in new window

0
 
NanohurtzChromeAuthor Commented:
what I'm looking for looks like the code I attached. The only thing this code is missing is the routing to "print" the whole string in cell A1 from the .txt or .bat file, the directory path ("D:/directory/..etc) in A3, and then the string fully parsed in cells A5.....Ax.

Can those routines be added to this to produce those outputs?
'Author RobSampson

strInputFile = "task.txt"
Set objFSO = CreateObject("Scripting.FileSystemObject")
Const intForReading = 1
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWB = objExcel.Workbooks.Add
Set objSheet = objWB.Sheets(1)
Set objFile = objFSO.OpenTextFile(strInputFile, intForReading, False)
While Not objFile.AtEndOfStream
        strLine = objFile.ReadLine
        If InStr(strLine, "/") > 0 Then
                arrBits = Split(strLine, "/")
                For intBit = 0 To UBound(arrBits)
                        If Trim(arrBits(intBit)) <> "" Then objSheet.Cells(intBit + 1, 1).Value = "/" & arrBits(intBit)
                Next
        End If
Wend
objFile.Close

Open in new window

0
 
Joe OvermanEngineerCommented:
I can follow the code you posted all the way to

        If InStr(strLine, "/") > 0 Then
                arrBits = Split(strLine, "/")
                For intBit = 0 To UBound(arrBits)
                        If Trim(arrBits(intBit)) <> "" Then objSheet.Cells(intBit + 1, 1).Value = "/" & arrBits(intBit)
                Next
        End If
It seems to me this code is attempting to parse the string into the various cells.  Is this the case?  

Another question, you want to paste the full string into A1, what about the next line in the text file?  Where do you want the next full string pasted and then parsed?
0
 
Joe OvermanEngineerCommented:
I made some assumptions.  First, the text file contains line after line of your sample text (without the REM line).  Second, you want the parsed data started in column A and continue in each subsequent column.  

Take this code and paste it into a new module in a new Excel workbook.
Run the script function and I hope you see the output you are looking for.

If my assumptions are wrong, then we can adjust and move forward to get you want you need.
Option Explicit
Function script()
    Dim strInputFile As String
    Dim strLine As String
    Dim objFSO As Object
    Dim objFile As Object
    Dim intCounter As Integer

    strInputFile = "C:\Documents and Settings\Desktop\task.txt"
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Const intForReading = 1
    'Set objExcel = CreateObject("Excel.Application")
    'objExcel.Visible = True
    'Set objWB = objExcel.Workbooks.Add
    'Set objSheet = objWB.Sheets(1)
    Set objFile = objFSO.OpenTextFile(strInputFile, intForReading, False)
    intCounter = 1
    While Not objFile.AtEndOfStream
        strLine = objFile.ReadLine
        Cells(1, intCounter).Select
        Cells(1, intCounter).Value = strLine
        Call ParseCell
        intCounter = intCounter + 1
    Wend
    objFile.Close
End Function

Function ParseCell()
    
    Dim lngCounter As Long
    Dim lngCount As Long
    Dim lngPointer As Long
    Dim strText As String
    Dim blnPathDone As Boolean
    Dim blnBetweenDone As Boolean
    
    lngPointer = 0
    strText = ActiveCell.Value
    'loop through entire text string
    For lngCounter = 1 To Len(strText)
Debug.Print Mid(strText, lngCounter, 1);
        'pull the file path based on the Quotation marks
        If Mid(strText, lngCounter, 1) = Chr$(34) And lngPointer = 0 And blnPathDone = False Then
            lngPointer = lngCounter
        End If
        If Mid(strText, lngCounter, 1) = Chr$(34) And lngCounter > lngPointer And blnPathDone = False Then
            Call strFindOpenCell
            ActiveCell.Value = Mid(strText, lngPointer, lngCounter - lngPointer)
            blnPathDone = True
            lngPointer = 0
        End If
        'pull the data between path and first forward slash '/'
        If blnPathDone = True And blnBetweenDone = False Then
            For lngCount = lngCounter To Len(strText)
                If Mid(strText, lngCount, 1) = "/" Then
                    Call strFindOpenCell
                    ActiveCell.Value = Mid(strText, lngCounter + 1, lngCount - lngCounter - 1)
                    blnBetweenDone = True
                    Exit For
                End If
            Next lngCount
        End If
        'pull the parsed componets based on the forward slash '/'
        If Mid(strText, lngCounter, 1) = "/" And lngPointer = 0 Then
            lngPointer = lngCounter
        End If
        If Mid(strText, lngCounter, 1) = "/" And lngCounter > lngPointer Then
            Call strFindOpenCell
            ActiveCell.Value = Mid(strText, lngPointer, lngCounter - lngPointer)
            lngPointer = lngCounter
        End If
    Next lngCounter
End Function

Function strFindOpenCell() As String
    'find the first avaliable cell in columnA
    Dim intRow As Integer
    Dim intCol As Integer
    
    intRow = ActiveCell.Row
    intCol = ActiveCell.Column
    While Cells(intRow, intCol).Value <> ""
        intRow = intRow + 1
    Wend
    Cells(intRow, intCol).Select
End Function

Open in new window

0
 
NanohurtzChromeAuthor Commented:
Fantastic!
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

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