CODEWITHSUNDEEP
powershellcsvexport-to-csv
St5987
St5987

Reputation: 27

Power shell append with export-csv

I know this question has been answered many times so I apologize for asking it again. I wrote my first power shell script to perform an aggregation task. I cant figure out how to append to an existing csv file. Right now my script is aggregating only column, I want to modify it to aggregate multiple columns so I need to append my output.

My input file looks something like this:

SalesID Qty Amount
1        2    5
1        3    6
2        5    9
2        6    5

My current output is:

SalesID     Sum
1            5       
2            11

BUT I want the output to be something like this:

SalesID Sum Amount
 1        5     11  
 2        11    14

My script:

import-csv $inFilePath | Group-Object $GroupByColumName| %{
    New-Object psobject -Property @{
        $GroupByColumName= $_.Name
        Sum = ($_.Group | Measure-Object $SumColumnName -Sum).Sum
    }
}|
export-csv $outFilePath -encoding "UTF8" -notype

Please help me out here. Thanks.

Upvotes: 0

Views: 169

Answers (1)

Matt
Matt

Reputation: 46730

The information is already there for you to access you just need to get it. 

$data | Group-Object SalesID | %{

    New-Object pscustomObject -Property @{
        SalesID = $_.Name
        Quantity = ($_.Group.Qty | Measure-Object -Sum).Sum
        Amount = ($_.Group.Amount | Measure-Object -Sum).Sum
    }
} | Select-Object SalesID,Quantity,Amount | export-csv $outFilePath -encoding "UTF8" -notype

For every salesID we get he sums of the quantities and amounts. Below is console output before

SalesID Quantity Amount
------- -------- ------
1              5     11
2             11     14

Upvotes: 1

Related Questions