VBS to PowerShell

This topic contains 4 replies, has 2 voices, and was last updated by Profile photo of Pranab Mahapatra Pranab Mahapatra 2 years, 2 months ago.

  • Author
    Posts
  • #18925
    Profile photo of Pranab Mahapatra
    Pranab Mahapatra
    Participant

    I am looking for your help to write the below block of the VBS code in PowerShell in an efficient way using the power of PowerShell

            Set objTS = objFSO.OpenTextFile[strInputCSVFile, ForReading]
            Do Until objTS.AtEndOfStream
                    strCSVLine = Trim[objTS.ReadLine]
                    If [Len[Trim[strCSVLine]] > 0 And InStr[UCase[strCSVLine],"DSP_CA_"] > 0 ] Then
                    arrCSV = CSVParse[strCSVLine]
                    strRegion = arrCSV[0]
                    strGroup = arrCSV[1]
                    strPolicy = arrCSV[3]
                    strSP = ""
    
                    arrPolicy = Split[strPolicy,";"]
                    For x = 0 To UBound[arrPolicy]
                        strTmpPol = Trim[Replace[arrPolicy[x],Chr[34],""]]
                        If InStr[strTmpPol,"+"] > 0 And InStr[strTmpPol,"/"] > 0 Then
                            If InStr[strTmpPol," 0 Then
                                strSP = Trim[Mid[strTmpPol,InStr[strTmpPol,"/"]+1,InStr[strTmpPol,"< "]-InStr[strTmpPol,"/"]-1]]
                            Else
                                strSP = Trim[Mid[strTmpPol,InStr[strTmpPol,"/"]+1,Len[strTmpPol]]]
                            End If
                            strOUTLine =  strRegion + "," + strGroup + "," + strSP
                        End If
                    Next
                    Wscript.Echo strOUTLine
               End If
            Loop
    

    [b][i][u]Input[/u]:[/i][/b]
    Region,Group,Description,Policy,Retired
    EMEA,DSP_CA_ASYNCRPCDISABLE_2010_00,Microsoft Outlook Asynchronous RPC Disable v2010,"""+SWGLB001/ASYNCRPCDISABLE_2010_00""",No
    EMEA,DSP_CA_DOTNET_45_01_MP_01,Microsoft .NET Framework v4.5,"""-SWGLB001/DOTNET_40_01;-SWGLB001/DOTNET_40_00;+SWGLB001/DOTNET_45_01""",No
    EMEA,DSP_CA_IECUSTOMIZATION_15_00_AD_01,IECUSTOMIZATION_15_00,"""+SWGLB001/IECUSTOMIZATION_15_00 """,Yes
    EMEA,DSP_CA_ACROBATREADER_1000_00_MP_02,ACROBATREADER_1000_00,"""+SWGLB001/HF_ACROBATREADER1011_00;+SWGLB001/HF_ACROBATREADER1014_02;+SWGLB001/ACROBATREADER_1000_00""",Yes

    [i][b][u]Output[/u]:[/b][/i]
    Region,Group,ActivePolicy
    EMEA,DSP_CA_ASYNCRPCDISABLE_2010_00,ASYNCRPCDISABLE_2010_00
    EMEA,DSP_CA_DOTNET_45_01_MP_01,DOTNET_45_01
    EMEA,DSP_CA_IECUSTOMIZATION_15_00_AD_01,IECUSTOMIZATION_15_00
    EMEA,DSP_CA_ACROBATREADER_1000_00_MP_02,HF_ACROBATREADER1011_00
    EMEA,DSP_CA_ACROBATREADER_1000_00_MP_02,HF_ACROBATREADER1014_02
    EMEA,DSP_CA_ACROBATREADER_1000_00_MP_02,ACROBATREADER_1000_00

    Above scripts reads each line of CSV file
    1. If the line has "DSP_CA_" Consider for parsing
    2. Extract the policy name if it has a + sign at the beginning (policy name starts after "/")
    3. If has multiple policy with + sign, for each policy creates a new record

  • #18926
    Profile photo of Tim Curwick
    Tim Curwick
    Participant

    Pranab,

    PowerShell works great with csv files, because it can natively convert between csv and objects. Try something like this. (This works in PowerShell 4.0. We'll have to tweak it a little for older versions.)

    Tim Curwick
    MadWithPowerShell.com

    $Groups = Import-CSV -Path C:\temp\input.csv
    $Groups = $Groups | Where-Object -Property Group -like "DSP_CA_*"
    
    $NewPolicies = New-Object System.Collections.ArrayList
    
    ForEach ( $Group in $Groups )
        {
        $Policies = $Group.Policy.Trim('"').Split(";")
        ForEach ( $Policy in $Policies )
            {
            If ( $Policy.Substring(0,1) -eq "+" )
                {
                $NewPolicies += [pscustomobject]@{ 'Region'       = $Group.Region
                                                   'Group'        = $Group.Group
                                                   'ActivePolicy' = $Policy.Split( "/", 2 )[1] }
                }
            }
        }
    $NewPolicies | Export-CSV -Path C:\Temp\Output.csv
  • #18935
    Profile photo of Pranab Mahapatra
    Pranab Mahapatra
    Participant

    Thanks Tim for your help.
    Although I am new tp PS world, your code is easy to follow.
    Only thing I need to understand from [pscustomobject] part.

  • #18949
    Profile photo of Tim Curwick
    Tim Curwick
    Participant

    Pranab,

    This part defines a hashtable of keys and values.

    @{ 'Region'       = $Group.Region
       'Group'        = $Group.Group
       'ActivePolicy' = $Policy.Split( "/", 2 )[1] }

    Putting the [pscustomobject] variable type in front of the hashtable converts it from a hashtable to a custom object. The keys become properties.

    The +=, of course, adds the new custom object to the arraylist $NewPolicies.

    The is by far the easiest way to create a new, custom object. This method became available in PowerShell 3.0.

    If we needed to be 2.0 compatible, I would do something like this:

    $NewPolicies += $Group | Select-Object -Property Region, Group, @{ Label = 'ActivePolicy'; Expression = { $Policy.Split( "/", 2 )[1] } }

    Tim Curwick

  • #19042
    Profile photo of Pranab Mahapatra
    Pranab Mahapatra
    Participant

    Thanks 🙂

You must be logged in to reply to this topic.