Converting strings to numbers based on arbitrary culture settings

Question

I need to do some math work with numbers from a text file that are formatted based on culture, so I could get 1,234.56 or I could get 1.234,56 or even 1 234,56 I think. I had originally thought to get the current culture with

$culture = (Get-Culture).Name

Then convert all numbers to en-US format, do my math, and convert back to the original format. However, something as simple as this isn't working.

$useCulture = New-Object System.Globalization.CultureInfo("en-US")

$value1 = "1,234.56"
$value2 = "1234,56"

[double]$value1 = $value1.ToString("f", $useCulture)
[double]$value2 = $value2.ToString("f", $useCulture)

$value1
$value2

I would expect to get back 1234.56 for both values, but the second one is coming back as 123456, so obviously I am not understanding how ToString is working. My guess is that it assumes what it has is in en-US format, because that is what my machine is configured to, and it is then trying to convert to the same thing, and on $value2 it just assumes the thousands separator was in the wrong place? In any case, how would I go about converting from an arbitrary format to a specified format so I can be sure sums are correct, then convert back to the original arbitrary format? Or, am I totally missing something and there is a much better way to approach this? Also, if at all possible I would like to be able to do this using PS 2.0, since I can't depend on target machines being upgraded to newer versions.

Interesting question. Do you mean that the numbers you need to parse come from many cultures, but you don't know in advance which culture a particular number is from? If so, there is a definite possibility for ambiguity (`1.234` could be a number less than 2 or more than 1000), unless you have other rules (like, the number will always be formatted with 2 decimal places, or something similar). — briantist, Nov 09 '15 at 19:31
That's exactly it. I have a script that launches a "journal" file with Autodesk Revit, which then runs some commands and outputs the time to do so to a text file. The journal file uses VBScript and the output is based on the culture settings of the user. I want my script to then read that text file into an array, repeat as needed, then do some math like sorting, throwing out low and high outliers and averaging the rest, then output results to console back in culture based format. And I don't want to have to address all the possibilities manually as I have no idea how may use the tool. — Gordon, Nov 09 '15 at 19:35
Is it possible to change the VBScript so that it writes the number in one specific culture (`en-US` or whatever, as long as it's consistent) that way you know how to read it back later? You could even write it to a new file or location if you don't want to disturb the original figures. — briantist, Nov 09 '15 at 19:42
I have started looking into that, but there are a BUNCH of edits to the journal then. If I can do it in the "management" script that would be preferred, but I have been looking into what VBS can do, and that looks like it is going to be what I do if I am forced to punt. — Gordon, Nov 09 '15 at 19:50
Do you have a way to know which culture was used for particular file? — user4003407, Nov 09 '15 at 20:53
In a sense yes, because the files are produced by Revit as driven by my PowerShell script. So Get-Culture will give me the "current" culture, which is the culture generating the txt files. But I found a reference that says PowerShell will output based on Culture settings, but expects "neutral" input. Neutral being a bit offensive since it in fact is English speaking world standard, and not "neutral" at all. But, I have a revised journal sent to someone I know in Zagreb to test if VBScript's FormatNumber defaults to . for decimals. — Gordon, Nov 09 '15 at 21:02
If it doesn't I at least can ensure that the only non numeric value is the decimal separator, and a simple replace will get me what I need. Then I'll use PowerShell's formatting to output to the right format based on Culture setting. That said, if there is a nifty way to actually convert on import/input I will happily take it. ;) — Gordon, Nov 09 '15 at 21:04
if all you numbers are of the same precision you could ignore the culture and just use a regex like so `[int]($value1 -replace '\D', '') / 100` — Dave Sexton, Nov 09 '15 at 23:23
I think this post will help you. You can use regular expression to force the formatting. After you have all numbers in the same format, you can use your culture info to translate that: http://stackoverflow.com/questions/2254185/regular-expression-for-formatting-numbers-in-javascript — Rahvin47, Jan 06 '16 at 11:04
How many numbers are in each file you are parsing? As @briantist says, this is an interesting problem. Do you need to solve it for the general case, or for your particular use case. You only need to find a single instance of a number that breaks a rule from one culture to infer it must belong to one or the other. What does the data represent? Can you infer anything from that, like if all numbers are 1.234 and some values must be > 1000, then this must be a file of non-us culture. — Kory Gill, Jan 07 '16 at 07:14

score 0 · Answer 1 · answered Jan 21 '16 at 16:08

So if the overall result is that you want something with only digits and at most one decimal point, I'd try the following:

Replace any non-decimal with a dot
Strip all but the last dot, which we take to be a decimal point

Here is a sample function that worked for a few test cases, but I definitely wasn't exhaustive in my testing:

function ConvertTo-Number {
  param(
    [Parameter(
      Mandatory=$true)]
      [System.String]
      $String
  )

  if($String -notmatch '\D'){
    return [int]$String
  } else {
    $ConvertedValue = ($String -replace '\D','.') -match '^(?<int>.*?)(?<dec>[\d]*)$'

    $ConvertedValue = $matches["int"] -replace '\D',''

    if($matches["dec"]){
      $ConvertedValue+=".$($matches["dec"])"
    }

    return [double]$ConvertedValue
  }
}

You may want to clean up the input string first/if possible, e.g. remove spaces, before anything else.

score 0 · Answer 2 · answered Mar 28 '20 at 14:51

You could do the following:

# Your input value
$value = '1 234,56'

# Determine culture of input format
if ($value -match '\.\d\d$') {
    $culture = [cultureinfo]::GetCultureInfo('en-us')
} elseif ($value -match '\.\d\d\d') {
    $culture = [cultureinfo]::GetCultureInfo('de-de')
} elseif ($value -match ' \d\d\d|,\d\d$') {
    $culture = [cultureinfo]::GetCultureInfo('fr-fr')
}

# Parse input value into a double and perform math, e.g. value * 27.2
$newvalue = [double]::Parse($value,$culture) * 27.2

# Convert computed value back to original culture
$newvalue.ToString('n2',$culture)

Converting strings to numbers based on arbitrary culture settings

2 Answers2