Powershell number format

tl;dr:

Use 0 instead of # in the format string:

PS> $value = 128586256.9; $value.ToString('00000000000000000000.00')

00000000000128586256.90

Note:

• Alternatively, you could construct the format string as an expression:

  • $value.ToString('0' * 20 + '.00')

• The resulting string reflects the current culture with respect to the decimal mark; e.g., with fr-FR (French) in effect, , rather than . would be used; you can pass a specific [cultureinfo] object as the second argument to control what culture is used for formatting; see the docs.

• As in your question, I'm assuming that $value already contains a number, which implies that you've already converted the CSV column values - which are invariably strings - to numbers.

• To convert a string culture-sensitively to a number, use [double]::Parse('1,2'), for instance (this method too has an overload that allows specifying what culture to use).

  • Caveat: By contrast, a PowerShell cast (e.g. [double] '1.2') is by design always culture-invariant and only recognizes . as the decimal mark, irrespective of the culture currently in effect.

zerocukor287 has provided the crucial pointer:

• To unconditionally represent a digit in a formatted string and default to 0 in the absence of an available digit, use 0, the zero placeholder in a .NET custom numeric format string

• By contrast, #, the digit placeholder, represents only digits actually present in the input number.

To illustrate the difference:

PS> (9.1).ToString('.##')

9.1  # only 1 decimal place available, nothing is output for the missing 2nd

PS> (9.1).ToString('.00')

9.10 # only 1 decimal place available, 0 is output for the missing 2nd

Since your input uses commas as decimal point, you can split on the comma and format the whole number and the decimal part separately.

Something like this:

$csv = @'
Item;Price
Item1;4000000
Item2;45817,43
Item3;400000
Item4;570425,02
Item5;15864155,69
Item6;1068635,69
Item7;128586256,9
Item8;8901900,04
Item9;29393,88
Item10;126858346,88
Item11;1190011,46
Item12;2358411,95
Item13;139594,82
Item14;13929,74
Item15;11516,85
Item16;55742,78
Item17;96722,57
Item18;21408,86
Item19;717,01
Item20;54930,49
Item21;391,13
Item22;2118,64
'@ | ConvertFrom-Csv -Delimiter ';'

foreach ($item in $csv) {
    $num,$dec = $item.Price -split ','
    $item.Price = '{0:D20},{1:D2}' -f [int64]$num, [int]$dec
}

# show on screen
$csv

# output to (new) csv file
$csv | Export-Csv -Path 'D:\Test\formatted.csv' -Delimiter ';'

Output in screen:

Item   Price                  
----   -----                  
Item1  00000000000004000000,00
Item2  00000000000000045817,43
Item3  00000000000000400000,00
Item4  00000000000000570425,02
Item5  00000000000015864155,69
Item6  00000000000001068635,69
Item7  00000000000128586256,09
Item8  00000000000008901900,04
Item9  00000000000000029393,88
Item10 00000000000126858346,88
Item11 00000000000001190011,46
Item12 00000000000002358411,95
Item13 00000000000000139594,82
Item14 00000000000000013929,74
Item15 00000000000000011516,85
Item16 00000000000000055742,78
Item17 00000000000000096722,57
Item18 00000000000000021408,86
Item19 00000000000000000717,01
Item20 00000000000000054930,49
Item21 00000000000000000391,13
Item22 00000000000000002118,64

I do things like this all the time, usually for generating computernames. That custom numeric format string reference will come in handy. If you want a literal period, you have to backslash it.

1..5 | % tostring 00000000000000000000.00

00000000000000000001.00
00000000000000000002.00
00000000000000000003.00
00000000000000000004.00
00000000000000000005.00

Adding commas to long numbers:

psdrive c | % free | % tostring '0,0'  # or '#,#'

18,272,501,760

"Per mille" character ‰ :

.00354 | % tostring '#0.##‰'

3.54‰