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