Mohammed Hamada
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.
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.
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?
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 ', '}}
ASKER
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
This brought name of the group but the AgentsByUri came empty without any result.
RGS.jpg
What did your initial script look like?
ASKER
This one worked and removed {} only
Get-CsRgsAgentGroup | Select-Object -Property Name, @{n='AgentsByUri'; e={$_.AgentsByUri -join ', '}}
That looks like my first suggestion; your "real" initial script should be different, otherwise you shouldn't have had the "{}" around the address list.
ASKER
Original one is this
Get-CsRgsAgentGroup | select name,{$_.AgentsByUri} | Sort name
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:
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
(Get-CsRgsAgentGroup | select -first 1 -expa AgentsByUri) -replace '[a-z]', '*'
ASKER
This is the first one gm -membertype's output
The second command's output is going to be this
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...}
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 :
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you so much, that worked great
Instead of just exporting the original "AgentsByUri" property, use a calculated property.
You currently probably have something like
Open in new window
To fix the issue above, just join the array in AgentsByUri:Open in new window