How do I sum a CSV column with positive and negative values in powershell?

Question

Name  Balance
----  -------
Tom   (0.09) 
Dick  600.5  
Harry (0.13) 
Sally 5.25

When I import the CSV (see above data) using Import-Csv, the "Balance" column imports as a string and I can't do any math on it. Note: The ( ) surrounded values are negatives. HELP!

$csv = Import-Csv -Path $file | select -Property 'Name','Balance'

$csv | Get-Member


   TypeName: Selected.System.Management.Automation.PSCustomObject

Name        MemberType   Definition                    
----        ----------   ----------                    
Equals      Method       bool Equals(System.Object obj)
GetHashCode Method       int GetHashCode()             
GetType     Method       type GetType()                
ToString    Method       string ToString()             
Balance     NoteProperty string Balance=(0.09)         
Name        NoteProperty string Name=Tom

You can convert the strings into decimales or doubles without any issues after importing your Csv but it's not clear what do you mean by values between parentheses being negatives. you could try with $csv.ForEach({ $_.Balance = $_.Balance -replace '$([\d.]+)$', '-$1' -as [double] }) then the values would be actual doubles you can do math on — Santiago Squarzon
– Santiago Squarzon, Commented Jul 20, 2022 at 19:42
PowerShell can convert it for you as long as you rid the string of parenthesis: ($csv.balance -replace "$|$","" | Measure-Object -Sum).Sum. — Abraham Zinala
– Abraham Zinala, Commented Jul 20, 2022 at 19:43
Tom's balance is -0.09, but the CSV has it as (0.09) as it's an accounting export that's out of my control. It's pretty standard. So the "total" of the Balances should result in 605.53. — Sencha718
– Sencha718, Commented Jul 20, 2022 at 19:45
In that case, swap the RegEx in my example for the one provided by @SantiagoSquarzon and it should give you just that. — Abraham Zinala
– Abraham Zinala, Commented Jul 20, 2022 at 19:49
So the -as in the same line was removing some of the values, I did it seperately after simplifying the -replace with just $csv.ForEach({ $_.Balance = $_.Balance -replace '$', '-' -replace'$','' }) seemed to do the trick. The script came out great. Thanks again all! — Sencha718
– Sencha718, Commented Jul 21, 2022 at 16:02

Santiago Squarzon · Accepted Answer · 2022-07-20 20:09:53Z

2

As noted, the property values of the imported Csv are of the type string, you can however reassign them to the desired type without any problem. For this you can use the -replace operator to replace the parentheses with the minus sign - and the -as operator to safely convert the strings into double or decimal.

Assuming the imported Csv is stored in the $csv variable:

$csv = Import-Csv path\to\csv.csv
$csv.ForEach({
    $_.Balance = $_.Balance -replace '\(([\d.]+)\)', '-$1' -as [decimal]
})
[Linq.Enumerable]::Sum([decimal[]] $csv.Balance) # => 605.53

Decimal is the recommended struct for dealing with currency values as mclayton notes in his helpful comment.

From Remarks:

The Decimal value type is appropriate for financial calculations that require large numbers of significant integral and fractional digits and no round-off errors.

edited Jul 20, 2022 at 20:09

answered Jul 20, 2022 at 20:00

Santiago Squarzon

65.6k5 gold badges26 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

mclayton Over a year ago

You might want to use [decimal] instead of [double] for currency values...

Santiago Squarzon Over a year ago

@mclayton good point. thanks!

Collectives™ on Stack Overflow

How do I sum a CSV column with positive and negative values in powershell?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related