Break csv data based on server name

Question

I am having below data in my csv file

"Server Name","Control Group","Tape Number","Status"
"BR502","QCLDUSYSW","Q23028","ACTIVE"
"BR502","QCLDUSYSW","Q32521","ACTIVE"
"BR502","QCLDUSYSW","Q05599","ACTIVE"
"BR503","QCLDUIPLW","Q25582","ACTIVE"
"BR503","QCLDUIPLW","Q15529","ACTIVE"
"BR503","QCLDUIPLW","Q05109","ACTIVE"
"BR504","QCLDUSYSW","Q14445","ACTIVE"
"BR504","QCLDUSYSW","Q27785","ACTIVE"
"BR504","QCLDUUSRD","Q26071","ACTIVE"
"BR505","QCLDUGRPD","Q27657","ACTIVE"
"BR505","QCLDUIPLW","Q17404","ACTIVE"

Please let me know how to break it based on server name like below

"Server Name","Control Group","Tape Number","Status"
"BR502","QCLDUSYSW","Q23028","ACTIVE"
"BR502","QCLDUSYSW","Q32521","ACTIVE"
"BR502","QCLDUSYSW","Q05599","ACTIVE"

"Server Name","Control Group","Tape Number","Status"
"BR503","QCLDUIPLW","Q25582","ACTIVE"
"BR503","QCLDUIPLW","Q15529","ACTIVE"
"BR503","QCLDUIPLW","Q05109","ACTIVE"

so on for rest of the servers.

Do you want each group on different files, or you just want it separated but on the same file? — Princewill Ezeidei
– Princewill Ezeidei, Commented Oct 6, 2021 at 17:53
Not in files. different arrays may be. coz I have to do calculation on each set — Empty Coder
– Empty Coder, Commented Oct 6, 2021 at 17:54

mklement0 · Accepted Answer · 2021-10-06 19:24:07Z

4

Use the Group-Object cmdlet to group the records together based on a particular property:

Import-Csv .\input.csv |Group-Object -Property 'Server Name' |ForEach-Object {
  # for each group, output a new CSV file with just the records pertaining to that server, named after the server
  $_.Group |Export-Csv .\$($_.Name).csv -NoTypeInformation
}

edited Oct 6, 2021 at 19:24

mklement0

451k68 gold badges726 silver badges985 bronze badges

answered Oct 6, 2021 at 17:58

Mathias R. Jessen

178k13 gold badges175 silver badges234 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Martin Brown · Accepted Answer · 2021-10-06 19:43:18Z

Something like this might do what you want:

import-csv tst.csv | where 'Server Name' -eq 'BR502'

If you want to examine each set you would use a Group-Object to bunch it on Server Name like this:

Import-Csv tst.csv | Group-Object 'Server Name'

This gives an object that has two properties Name, Count and Group which contain the Value you groped by, the number of items in the group and the list of items in the group. This can be used for calculations on each group. For example if you want to know how many in Control Groups in each server end with D you could use this:

Import-Csv tst.csv | Group-Object 'Server Name' | Foreach-Object { $name=$_.Name; Write-Output $_.Group | Where-Object 'Control Group' -LIKE '*D' | Measure-Object | Select-Object @{Label='Name';Expression={$name}},Count }

If you use aliases that command can be shortened to:

ipcsv tst.csv | Group 'Server Name' | % { $name=$_.Name; $_.Group | ? 'Control Group' -LIKE '*D' | Measure | Select @{L='Name';E={$name}},Count }

Thanks for the answer, but is it possible not to mention the name of the server? coz this is good for one set. what about others?

Collectives™ on Stack Overflow

Break csv data based on server name

2 Answers 2

Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Related