VBS to PowerShell

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

Input:
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

Output:
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

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

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.

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

Thanks :slight_smile: