CODEWITHSUNDEEP
powershell
Newbee
Newbee

Reputation: 23

Merge of multiple rows to single row in csv Using Powershell

I have a .csv file which looks like 

ID, Dept
1,x
1,y
1,z
2,a
2,b
2,c

output should be

ID, Dept
1, x;y;z
2, a;b;c

I tried the with below in the PowerShell but it is returning 0 for both columns

$csvValues = Get-Content "DeptDetails.csv"
$duplicates = $csvValues | group-object ID | ? Count -gt 1

$objs = New-Object System.Collections.ArrayList
ForEach ($duplicate in $duplicates){
$objs.Add([pscustomobject]@{ID = ($duplicate.Group.ID | select -Unique) -as [int];
GroupName=($duplicate.Group.Dept | ? Length -gt 0) -join ';'})
}
$objs | Sort ID

Upvotes: 2

Views: 6190

Answers (1)

Martin Brandl
Martin Brandl

Reputation: 58931

Use the Import-Csv cmdlet to load your csv. Group the entries by its ID using the Group-Object cmdlet. Then you can iterate over the group using the Foreach-Object cmdlet and create your desired Object for each ID. Finally export it back to csv using the Export-Csv cmdlet:

Import-Csv 'DeptDetails.csv' | Group-Object ID | ForEach-Object {
    [PsCustomObject]@{
        ID = $_.Name
        Dept = $_.Group.Dept -join ';'
    }
} | Export-Csv 'DeptDetails_output.csv' -NoTypeInformation

Upvotes: 1

Related Questions