Using Sort-Object to sort a Currency/Money

user_19526527 0
Viewed 92

I have a inventory database from my company and I'm wanting to sort some entries based on pricing. I was thinking originally I would have to do everything by hand but I figured Sort-Object should work... until I remembered Sort-Object and its infamous string sorting. Easy, i'll sort by converting it to an integer except of course a currency value has symbol such as $ at the start.

The original code I used which caused the string sorting is below. The classic 200 is higher than 1000 etc:

$Result | Sort-Object -Property Price | Format-Table -Property Price

The int code I tried is:

$Result | Sort-Object -Property { [int]$_.Price } | Format-Table -Property Price

This results in output like "Cannot convert value "$414.50" to type "System.Int32". | Error: "Input string was not in a correct format." Makes sense, cant convert a $ to an int.

So is there any way around this without me having to sort by hand?

Thanks

sort-object currency powershell sorting
2 Answers

Firstly, you probably want [decimal] instead of [int] because [int] "414.50" is 414, not 414.50 so you'll be losing precision.

That aside, I'm adapting this answer for C#: https://stackoverflow.com/a/56603818/3156906

$fi = new-object System.Globalization.NumberFormatInfo;
$fi.CurrencySymbol = "`$";

@("`$10.00", "`$2.00") | Sort-Object -Property @{
    "Expression" = { [decimal]::Parse($_, "Currency", $fi) }
};
# $2.00
# $10.00

The advantage of this is that invalid database values like - e.g. $1.$10 - that might have crept in will throw an exception, as will different currencies like £1.00 so you're getting a bit of extra data validation for free.

Note that the results remain as strings, but they're sorted as currency amounts (decimals). If you want the actual numeric value you'll need to convert the values separately...

user_31569060

To add to mclayton's helpful answer:

It is simpler to use a predefined [cultureinfo] instance that uses the your currency format, such as en-US (US-English) in the [decimal]::Parse() call, in combination with C, the currency format specifier.

@(
  [pscustomobject] @{ Price='$414.50' },
  [pscustomobject] @{ Price='99.02$' }
  [pscustomobject] @{ Price='999.03' }
  [pscustomobject] @{ Price='$5.04' }
) | 
  Sort-Object { [decimal]::Parse($_.Price, 'C', [cultureinfo] 'en-US') }

Output (correctly numerically sorted):

Price
-----
$5.04
99.02$
$414.50
999.03

Note:

  • As the sample input values show, there's some flexibility with respect to what input formats are accepted, such as a trailing $, and a value without $_.

  • If the current culture can be assumed to be en-US (or a different culture that uses the same currency symbol and formatting, notably also the same decimal separator, .), you can omit the [cultureinfo] 'en-US' argument in the [decimal]::Parse() call above - though for robustness I suggest keeping it.

    • As an aside: PowerShell's casts (which don't support currency values) always use the invariant culture with string operands, irrespective of the current culture. Thus, something like [decimal] '3.14' is recognized even while a culture that uses , as the decimal separator is in effect.

    • While the invariant culture - whose purpose is to provide representations that aren't culture-dependent and remain stable over time - is based on the US-English culture, it can not be used here, because its currency symbol is ¤; e.g., (9.99).ToString('C', [cultureinfo]::InvariantCulture) yields ¤9.99.

  • An input value that cannot be parsed as a currency causes an (effectively) non-terminating error,[1] and such values sort before the currency values.

    • If you simply want to ignore non-conforming values, use try { [decimal]::Parse(...) } catch { }
    • If you want to abort processing on encountering non-confirming values pass -ErrorAction Stop to the Sort-Object call.

[1] A .NET method call that fails causes a statement-terminating error, but since the error occurs in a script block (in the context of a calculated property), only the statement inside the script block is terminated, not the enclosing Sort-Object call

user_453750
Related