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,162 questions
0 comments No comments
{count} votes

2 answers

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

    any help is very much appreciated!

    0 comments No comments

  2. Rich Matheisen 45,111 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