Link to home
Start Free TrialLog in
Avatar of Mohammed Hamada
Mohammed HamadaFlag for Portugal

asked on

Cleaning CSV file with powershell script

Dear Experts,

I am trying to use powershell to clean a CSV file from certain values. The output of powershell is normally like this


Name                                                                                                            $_.AgentsByUri                                                                                                                  
----                                                                                                                             --------------                                                                                                                  
ResponseGroup1                                                                                         {sip:user@domain.ca, sip:user2@domain.ca}

I want to clear the csv file from the {sip:} And leave users with their uri only. So the output would be like the following

Name                                                                                                            $_.AgentsByUri                                                                                                                  
----                                                                                                                             --------------                                                                                                                  
ResponseGroup1                                                                                        user@domain.ca, user2@domain.ca

I tried to do this with trim but didn't work properly.
Avatar of oBdA
oBdA

I'd suggest to change the script that creates the csv, so that you have a proper export that doesn't need to be cleaned afterwards.
Instead of just exporting the original "AgentsByUri" property, use a calculated property.
You currently probably have something like
Get-Whatever | Select-Object -Property Name, AgentsByUri | Export-Csv ...

Open in new window

To fix the issue above, just join the array in AgentsByUri:
Get-Whatever | Select-Object -Property Name, @{n='AgentsByUri'; e={$_.AgentsByUri -join ', '}}| Export-Csv ...

Open in new window

Avatar of Mohammed Hamada

ASKER

Hi oBdA,

Tried it, it removed the {} but sip: is still there.

I guess I have to add the sip: somewhere to remove it?
Sorry, missed the sip part; the calculated property would be
@{n='AgentsByUri'; e={($_.AgentsByUri | ForEach-Object {$_.Split(':')[1]}) -join ', '}}

Open in new window

Get-CsRgsAgentGroup | Select-Object -Property Name, @{n='AgentsByUri'; e={($_.AgentsByUri | ForEach-Object {$_.Split(':')[1]}) -join ', '}}

This brought name of the group but the AgentsByUri came empty without any result.
RGS.jpg
What did your initial script look like?
This one worked and removed {} only

Get-CsRgsAgentGroup | Select-Object -Property Name, @{n='AgentsByUri'; e={$_.AgentsByUri -join ', '}}

Open in new window

That looks like my first suggestion; your "real" initial script should be different, otherwise you shouldn't have had the "{}" around the address list.
Original one is this

Get-CsRgsAgentGroup | select name,{$_.AgentsByUri} | Sort name
Sorry, can't test this with the original cmdlet.
From what I can test with an array property containing several 'sip:...' entries, that should work just fine, unless that property is actually an array with only a single element.
Could you paste the output of these into two [code][/code] blocks:
gm -MemberType Property -InputObject (Get-CsRgsAgentGroup | select -first 1).AgentsByUri

Open in new window

(Get-CsRgsAgentGroup | select -first 1 -expa AgentsByUri) -replace '[a-z]', '*'

Open in new window

This is the first one gm -membertype's output

PS C:\Witness\Get-CsConnections.v3.4> gm -MemberType Property -InputObject (Get-CsRgsAgentGroup | select -first 1).AgentsByUri
Get-CsRgsAgentGroup : The pipeline has been stopped.
At line:1 char:39
+ gm -MemberType Property -InputObject (Get-CsRgsAgentGroup | select -first 1).Age ...
+                                       ~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [Get-CsRgsAgentGroup], PipelineStoppedException
    + FullyQualifiedErrorId : Microsoft.Rtc.Rgs.Management.GetOcsRgAgentGroupCmdlet
 


Identity                 : service:ApplicationServer:SBG-POOL01.domain.net/14869f45-35e1-4c3d-abfe-0912828d2b84
Name                     : Sales 
Description              : Sales 
ParticipationPolicy      : Informal
AgentAlertTime           : 40
RoutingMethod            : Attendant
DistributionGroupAddress : 
OwnerPool                : SBG-POOL01.domain.net
AgentsByUri              : {sip:bi@publicdomain.com, sip:car@publicdomain.com, sip:she@publicdomain.com, sip:pa@publicdomain.com...}

Open in new window



The second command's output is going to be this

PS C:\> (Get-CsRgsAgentGroup | select -first 1 -expa AgentsByUri) -replace '[a-z]', '*' >1.txt
Get-CsRgsAgentGroup : The pipeline has been stopped.
At line:1 char:2
+ (Get-CsRgsAgentGroup | select -first 1 -expa AgentsByUri) -replace '[a-z]', '*'  ...
+  ~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [Get-CsRgsAgentGroup], PipelineStoppedException
    + FullyQualifiedErrorId : Microsoft.Rtc.Rgs.Management.GetOcsRgAgentGroupCmdlet




AbsolutePath   : bi@publicdomain.com
AbsoluteUri    : sip:bi@publicdomain.com
LocalPath      : bi@publicdomain.com
Authority      : 
HostNameType   : Unknown
IsDefaultPort  : True
IsFile         : False
IsLoopback     : False
PathAndQuery   : bi@publicdomain.com
Segments       : {bi@publicdomain.com}
IsUnc          : False
Host           : 
Port           : -1
Query          : 
Fragment       : 
Scheme         : sip
OriginalString : sip:bi@publicdomain.com
DnsSafeHost    : 
IdnHost        : 
IsAbsoluteUri  : True
UserEscaped    : False
UserInfo       : 

AbsolutePath   : ca@publicdomain.com
AbsoluteUri    : sip:ca@publicdomain.com
LocalPath      : ca@publicdomain.com
Authority      : 
HostNameType   : Unknown
IsDefaultPort  : True
IsFile         : False
IsLoopback     : False
PathAndQuery   : ca@publicdomain.com
Segments       : {ca@publicdomain.com}
IsUnc          : False
Host           : 
Port           : -1
Query          : 
Fragment       : 
Scheme         : sip
OriginalString : sip:ca@publicdomain.com
DnsSafeHost    : 
IdnHost        : 
IsAbsoluteUri  : True
UserEscaped    : False
UserInfo       : 

AbsolutePath   : sh@publicdomain.com
AbsoluteUri    : sip:sh@publicdomain.com
LocalPath      : sh@publicdomain.com
Authority      : 
HostNameType   : Unknown
IsDefaultPort  : True
IsFile         : False
IsLoopback     : False
PathAndQuery   : sh@publicdomain.com
Segments       : {sh@publicdomain.com}
IsUnc          : False
Host           : 
Port           : -1
Query          : 
Fragment       : 
Scheme         : sip
OriginalString : sip:sh@publicdomain.com
DnsSafeHost    : 
IdnHost        : 
IsAbsoluteUri  : True
UserEscaped    : False
UserInfo       : 

AbsolutePath   : pa@publicdomain.com
AbsoluteUri    : sip:pa@publicdomain.com
LocalPath      : pa@publicdomain.com
Authority      : 
HostNameType   : Unknown
IsDefaultPort  : True
IsFile         : False
IsLoopback     : False
PathAndQuery   : pa@publicdomain.com
Segments       : {pa@publicdomain.com}
IsUnc          : False
Host           : 
Port           : -1
Query          : 
Fragment       : 
Scheme         : sip
OriginalString : sip:pa@publicdomain.com
DnsSafeHost    : 
IdnHost        : 
IsAbsoluteUri  : True
UserEscaped    : False
UserInfo       : 

AbsolutePath   : be@publicdomain.com
AbsoluteUri    : sip:be@publicdomain.com
LocalPath      : be@publicdomain.com
Authority      : 
HostNameType   : Unknown
IsDefaultPort  : True
IsFile         : False
IsLoopback     : False
PathAndQuery   : be@publicdomain.com
Segments       : {be@publicdomain.com}
IsUnc          : False
Host           : 
Port           : -1
Query          : 
Fragment       : 
Scheme         : sip
OriginalString : sip:be@publicdomain.com
DnsSafeHost    : 
IdnHost        : 
IsAbsoluteUri  : True
UserEscaped    : False
UserInfo       : 

AbsolutePath   : nic@publicdomain.com
AbsoluteUri    : sip:nic@publicdomain.com
LocalPath      : nic@publicdomain.com
Authority      : 
HostNameType   : Unknown
IsDefaultPort  : True
IsFile         : False
IsLoopback     : False
PathAndQuery   : nic@publicdomain.com
Segments       : {nic@publicdomain.com}
IsUnc          : False
Host           : 
Port           : -1
Query          : 
Fragment       : 
Scheme         : sip
OriginalString : sip:nic@publicdomain.com
DnsSafeHost    : 
IdnHost        : 
IsAbsoluteUri  : True
UserEscaped    : False
UserInfo       : 

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of oBdA
oBdA

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you so much, that worked great