PowerShell. Group-object usage in one file

Question

I am trying to combine several rows into one, provided that the key cell is the same. And write data from all lines with the same key to the final line.

Example Pic

**Before**  

ID      |  Name |  DateTime            | Duration |     Call_Type   |
1234509 |  Mike | 2020-01-02T01:22:33  |          |     Start_Call  |
1234509 |       | 2020-01-02T01:32:33  |  600     |     End_call    |

AFTER

ID      |  Name |  DateTime            | Duration |     Start_Call     |  End_call             | 
1234509 |  Mike |  2020-01-02T01:22:33 |     600  |2020-01-02T01:22:33 |  2020-01-02T01:32:33  |

Before

ID;Name;DateTime;Duration;Call_Type
1234509;Mike;2020-01-02T01:22:33;;Start_Call
1234509;;2020-01-02T01:32:33;600;End_call

After

ID;Name;Duration;Start_Call;End_call
1234509;Mike;600;2020-01-02T01:22:33;2020-01-02T01:32:33

How to use here

$csv | Group-Object ID

and get the data as in the picture?

RoadRunner · Accepted Answer · 2020-04-15T05:58:51.790

After grouping by ID with Group-Object, you can iterate each group and create a new System.Management.Automation.PSCustomObject with the properties you want to export in your output CSV file.

For ID we simply use the grouping key. Name and Duration we choose the first object that doesn't have a $null or empty version of that property using System.String.IsNullOrEmpty(). For Start_Call and End_Call we choose the object that has those values for the Call_Type property.

The filtering is done by Where-Object. To get the first and expanded versions of the properties, we also use -First and -ExpandProperty from Select-Object.

$csv = Import-Csv -Path .\data.csv -Delimiter ";"

$groups = $csv | Group-Object -Property ID

& {
    foreach ($group in $groups)
    {
        [PSCustomObject]@{
            ID = $group.Name
            Name = $group.Group | Where-Object {-not [string]::IsNullOrEmpty($_.Name)} | Select-Object -First 1 -ExpandProperty Name
            Duration = $group.Group | Where-Object {-not [string]::IsNullOrEmpty($_.Duration)} | Select-Object -First 1 -ExpandProperty Duration
            Start_Call = $group.Group | Where-Object {$_.Call_Type -eq "Start_Call"} | Select-Object -First 1 -ExpandProperty DateTime
            End_Call = $group.Group | Where-Object {$_.Call_Type -eq "End_Call"} | Select-Object -First 1 -ExpandProperty DateTime
        }
    }
} | Export-Csv -Path .\output.csv -Delimiter ";" -NoTypeInformation

output.csv

"ID";"Name";"Duration";"Start_Call";"End_Call"
"1234509";"Mike";"600";"2020-01-02T01:22:33";"2020-01-02T01:32:33"

If you want to remove quotes from the CSV file, you can use the -UseQuotes switch from Export-Csv. However, yhis does require PowerShell 7. If your using a lower PowerShell version, you can use some of the recommendations from How to remove all quotations mark in the csv file using powershell script?.

PowerShell. Group-object usage in one file

1 Answers1