0

I have a .csv file which I'm grouping on two properties 'DN', 'SKU' and then performing a sum on another property 'DeliQty' This works fine and the sum is reflected back to the group. However I then need to re group just on 'DN' and write out to separate files. I've tried Select-Object -Expand Group but this reverts to the original contents without the summed lines.

Is there a way to un group preserving the summed lines and then group again?

$CSVFiles = Get-ChildItem -Path C:\Scripts\INVENTORY\ASN\IMPORT\ -Filter *.csv

foreach ($csv in $CSVFiles) {
    $group = Import-Csv $csv.FullName | Group-Object DN, SKU
    $group | Where Count -gt 1 | ForEach-Object {
    $_.Group[0].'DeliQty' = ($_.Group | Measure-Object DeliQty -Sum).Sum 
    } 
    }
ColinA
  • 99
  • 1
  • 9

1 Answers1

0

You may do the following:

$CSVFiles = Get-ChildItem -Path C:\Scripts\INVENTORY\ASN\IMPORT\ -Filter *.csv

foreach ($csv in $CSVFiles) {
    $group = Import-Csv $csv.FullName | Group-Object DN, SKU | Foreach-Object {
        if ($_.Count -gt 1) {
            $_.Group[0].DeliQty = ($_.Group | Measure-Object DeliQty -Sum).Sum
        }
        $_.Group[0]
    } 
    # outputs summed and single group objects
    $group
}
AdminOfThings
  • 23,946
  • 4
  • 17
  • 27
  • Thanks for your response. This works to a point. However the summed line is shown in $Contents in addition to other lines from $group. Example: Two lines exist in the original $Contents with the same DN and SKU. Line 1 DeliQty = 10 Line 2 DeliQty = 20 When looking at $Contents after Group-Object DN. The sum has been performed and updated the custom object. However Line 2 is also still present. Line 1 DeliQty = 30 Line 2 DeliQty = 20 My understanding was that both lines would be replaced by the summed line. The required result should be a single line with the summed amount. – ColinA Jan 28 '21 at 09:42
  • Do you want to output only lines where `Count` > 2? Or do you want to output summed lines AND lines that could not be grouped? – AdminOfThings Jan 28 '21 at 14:31
  • Hi, I need the summed lines AND those that could not be grouped. The main input .csv contains some duplicate rows, I'm rolling them into one line, summing the qty. The output will contain non affected rows and the summed rows. Thanks – ColinA Jan 28 '21 at 15:13
  • Thank you very much, this is working as required! – ColinA Jan 28 '21 at 17:59