Share via

Using Powershell to merge rows and put differences in each column

LarsvanBeek 0 Reputation points
2023-04-08T14:50:01.87+00:00

Hi, I have the following table:

"EAN" | "Series"

1010 | Twilight

1010 | Sparkle

I would like to merge these based on EAN into one row with all serie information in their own column, like this:

"EAN" | "Series" | "Serie2

1010 | Twilight | Sparkle

I found a way to combine the two but cannot find a way to get the series information in separate columns:

Group-Object EAN |
Select 	@{n="EAN";Expression={$_.Name}},_

@{n='Serie';E={($_.Group.Serie | Sort-Object -Unique) -join ''}}|
PowerShell
PowerShell
A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
2,328 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. LarsvanBeek 0 Reputation points
    2023-04-08T14:51:25.4+00:00

    any help is very much appreciated!

    0 comments
  2. Rich Matheisen 45,906 Reputation points
    2023-04-09T18:48:43.8466667+00:00

    Doing this a bit more complicated because a CSV must be rectangular. I.e., every row must have the same number of columns. In your example, simply creating the columns that have data will produce a "ragged" array of columns (uneven column widths).

    # the fixed part of the column header name
$ColHdr = "Data"

# get the maximum number of columns needed to hold the data (minus the 'name' in the 1st column)
$max = ($Groups | measure-object -Property Count -maximum).maximum
# create an ordered has to hold the aggregated data ('Name' is always going to be there)
$NewRow = [ordered]@{
    Name = ""
}
foreach ($d in (1..$max)){
    $NewRow[("{0}{1}" -f $ColHdr,$d)] = ""
}
Import-Csv C:\junk\agg.csv -Delimiter "|" |
        Group-Object Name | 
            ForEach-Object{
                $numdata = $_.Group.count - 1   # adjust for comparison to a zero based subscript
                $NewRow.Name = $_.Name          # only one name per group. use as the 1st columns data
                for ($i=0; $i -lt $max; $i++){
                    if ($i -gt $numdata){
                        $NewRow[("{0}{1}" -f $ColHdr,($i+1))] = ""  # columns left to fill, but no more data
                    }
                    else{
                        $NewRow[("{0}{1}" -f $ColHdr,($i+1))] = $_.Group.Data[$i]   # fill column with data from CSV
                    }
                }
                [PSCustomObject]$NewRow
            } | Sort-Object Name

    The code will create output that looks like this:

    Name Data1    Data2   Data3
---- -----    -----   -----
1010 Twilight Sparkle
EAN  Series   Serie2  Serie3