Combine two or more rows in a csv using powershell

Question

I receive a file from one of our clients that looks like this: (I added headers to this to it for easier processing). Sample Input:

PlanID,Date,Transaction,Type,Ticker,Amount,Cash
01,121211,div,mf,fjk,25,cash
01,121211,div,mf,fjk,30,cash
01.121211,buy,mf,fjk,55,cash
02,121211,div,sd,ejd,10,cash
02,121211,div,sd,ejd,15,cash
02,121211,buy,sd,ejd,25,cash

I need a way to combine all the rows with Transaction= 'div' by summing up their amount for each PlanID. This is how I desire my output to look like:

Sample Output:

PlanID,Date,Transaction,Type,Ticker,Amount,Cash
01,121211,div,mf,fjk,55,cash
01.121211,buy,mf,fjk,55,cash
02,121211,div,sd,ejd,25,cash
02,121211,buy,sd,ejd,25,cash

So, there is only one div row before buy row (with amount summed up, will always be the buy amount). Any ideas how to approach this would be greatly appreciated?

Thanks much in advance!!

Wouldn't it be simpler to do this in Excel rather then in PowerShell? — Abbas
– Abbas, Commented Dec 20, 2011 at 19:33
It doesn't look too much difficult, I'm going to try to solve it, ;D — mjsr
– mjsr, Commented Dec 20, 2011 at 20:23

jon Z · Accepted Answer · 2011-12-20 20:26:41Z

Import-Csv input.csv | Group-Object planid, transaction | 
Select @{n="PlanId";E={($_.name -split ',')[0]}},
       @{n="Date";e={($_.group)[0].date}}, 
       @{n="Transaction";E={(($_.name -split ',')[1]).trim()}},
       @{n="Type";e={($_.group)[0].type}},  
       @{n="Ticker";e={($_.group)[0].ticker}}, 
       @{n="Amount";e={($_.group | measure-object amount -sum).sum}},
       @{n="Cash";e={($_.group)[0].cash}} |
Export-Csv output.csv -NoTypeInformation

Steps:

Import the input.csv file
Group the objects by planid and transaction
Select the properties you want in your output
1. PlanId = whatever is left of the comma in the name field of the object returned by group-object
2. Transaction = whatever is right of the comma in the name field of the object returned by group-object
3. Amount = sum of all amounts per grouped object
4. Date = the date per grouped object
5. Type = type per grouped object
6. Ticker = ticker per grouped object
7. Cash = cash per grouped object
Export the objects to output.csv (add notypeinformation to strip the top line with type info)

Collectives™ on Stack Overflow

Combine two or more rows in a csv using powershell

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related