Author Posts

September 18, 2014 at 11:12 am

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

September 18, 2014 at 1:02 pm

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

September 18, 2014 at 10:51 pm

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.

September 19, 2014 at 2:29 pm

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