How to convert string to double in PowerShell?

user_8857248 0
Viewed 11562

I have a script in PowerShell that I am using to import data from a CSV. I am receiving an error

Cannot convert value "Journal Amount" to type "System.Double". Error: "Input string was not in a correct format." At C:\TestPowerShell\TestPowerShell1.ps1:98 char:1 + $JournalAmount = [double] $_.PSObject.Properties['Journal Amount'].Va ... + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidArgument: (:) [], RuntimeException + FullyQualifiedErrorId : InvalidCastFromStringToDoubleOrSingle

Here is my code:

Get-Content "config.txt" | foreach-object -begin {$h=@{}} -process { $k = [regex]::split($_,'='); if(($k[0].CompareTo("") -ne 0) -and ($k[0].StartsWith("[") -ne $True)) { $h.Add($k[0], $k[1]) } }

$FileBrowserInitialDir = $h.FileBrowserInitialDir
$Header = 'Terminal','Company','Journal Account Code','Project','Task','Line of Business','Date','Journal Amount','Report Entry Vendor Description'

$TIDataFilePath = $h.TIDataFilePath
$TIDataFileBaseName = $h.TIDataFileBaseName

$TIScreenID = $h.TIScreenID
$TIControlFile = $h.TIControlFile
$TIErrLog = $h.TIErrLog

$ResultsFilePath = $h.ResultsFilePath
$ResultsFileBaseName = $h.ResultsFileBaseName

$dt = Get-Date -Format "MMddyyyyHHmmss"
$TIDataFile = "$($TIDataFileBaseName)$($dt).txt"
$ResultsFile = "$($ResultsFileBaseName)$($dt).txt"

Add-Type -AssemblyName System.Windows.Forms
$FileBrowser = New-Object System.Windows.Forms.OpenFileDialog -Property @{ 
    InitialDirectory = $FileBrowserInitialDir
    Filter = 'CSV (*.csv)|*.csv'
    Title = "Select Expense csv file"
}
$null = $FileBrowser.ShowDialog()

if($FileBrowser.FileName -eq ""){
    exit
}


 $EXPENSECSV = Import-CSV $FileBrowser.FileName -Header $Header
 New-Item -path $TIDataFilePath -name $TIDataFile -type "file"
 $EXPENSECSV |foreach-object{    

$Terminal =  $_.PSObject.Properties['Terminal'].Value
$Terminal = $Terminal.Insert(0,'0')

$CompanyTemp =  $_.PSObject.Properties['Company'].Value
$CompanyTemp = $CompanyTemp.Insert(0,'0')
#$Company= [String]::Concat($_.Terminal,$CompanyTemp) 
$Company= [String]::Concat($Terminal,$CompanyTemp) 
$LineofB =  $_.PSObject.Properties['Line of Business'].Value
#$JournalAmount = [double] $_.PSObject.Properties["Journal Amount"].Value
 $LineofB = $LineofB.Insert(0,'0')

 $Account = $_.PSObject.Properties['Journal Account Code'].Value
$Sub = $LineofB

$TranDateStr =  $_.PSObject.Properties['Date'].Value
$JournalAmount = [double] $_.PSObject.Properties['Journal Amount'].Value
$Description =  $_.PSObject.Properties['Report Entry Vendor Description'].Value

    $row = ""
    $row +=  "`"Level1`","
    $row +=  "`"$($Company)`","
    $row +=  "`"$($Account)`","
    $row +=  "`"$($Sub)`","
    $row +=  "`"$($TranDateStr)`","


    # Convert Concur Amount to Credit/Debit Amount
    # Postive Amount  --> Debit Amount
    # Negative Amount --> Credit Amount
    if($JournalAmount -lt 0){
        $JournalAmount*= -1
        $row += "`"$($JournalAmount)`","   #Credit Amount
        $row += "`"`","             #Debit Amount
    }
    else {
        $row += "`"`","             #Credit Amount
        $row += "`"$($JournalAmount)`","   #Debit Amount
    }

    $row += "`"$($Description)`""

   Add-Content -Path "$($TIDataFilePath)$($TIDataFile)" -Value $row
 }

 #Results
 New-Item -path $ResultsFilePath -name $ResultsFile -type "file"

 $ResultsStr =   "----------------------------------------------------------------------------------`n"
$ResultsStr +=  "                             Concur to Dynamics SL                                   `n"
$ResultsStr +=  "----------------------------------------------------------------------------------`n"
$ResultsStr +=  "Copy and paste the following into the Transaction Import screen.`n"
$ResultsStr += "Line 1: Data File Name`n"
$ResultsStr += "Line 2: Screen ID`n"
$ResultsStr += "Line 3: Control File Name`n"
$ResultsStr += "Line 4: Output Log File`n`n`n"
$ResultsStr += "$($TIDataFilePath)$($TIDataFile)`n"
$ResultsStr += "$($TIScreenID)`n"
$ResultsStr += "$($TIControlFile)`n"
$ResultsStr += "$($TIErrLog)"


 Add-Content -path "$($ResultsFilePath)$($ResultsFile)" -value $ResultsStr

Start-Process "$($ResultsFilePath)$($ResultsFile)"
powershell type-conversion
2 Answers

To answer the question as asked:

  • Casting a string to [double] - as you've attempted yourself - does work, but only if the string is recognized as a (floating-point) number.

    • Caveat: Since PowerShell's casts are culture-invariant, only . is recognized as the decimal mark, and , is always interpreted as the thousands-grouping separator.

    • To parse culture-sensitively, use [double]::Parse($string) to parse based on the rules of the current culture (as reflected in $PSCulture); pass a [cultureinfo] instance as the second argument to parse based on the rules of a given culture (e.g., to parse based on the rules of the French culture, use [double]::Parse($string, [cultureinfo] 'fr-FR'))

  • If the string value is not recognized, you can use one of the following approaches to handle that case:

    • Use try / catch to handle the error; e.g., to quietly default to 0:

      • [double] $double = try { $string } catch { 0 }

    • Use -as, the conditional type conversion operator, which either returns an instance of the specified type or, if the conversion fails, $null:

      • $double = $string -as [double]; if ($null -eq $double) { ... }

As for what you tried:

As Mathias R. Jessen points out, the likely cause of your problem is that your CSV file does a have a header row itself, yet you also supplied headers via the -Header parameter.

This results in the CSV file's header line to be misread as the first data row, as a result of which the first object returned by Import-Csv then contains the column (header) names as property values - which then causes the [double] cast to fail, given that you're effectively executing the following:
[double] 'Journal Amount'

Therefore:

  • Omit the -Header parameter in your Import-Csv call.

  • As an optimization, simplify the property-value retrieval by using normal dot notation; simply use quoting to specify property name Journal Amount, because it contains a space:

Import-CSV $FileBrowser.FileName | # !! No -Header argument
  ForEach-Object {
    # Simpler and more efficient than:
    #   $JournalAmount = [double] $_.PSObject.Properties['Journal Amount'].Value
    $JournalAmount = [double] $_.'Journal Amount'
  }
# ...
user_453750

This fixed the issue: 

$JournalAmount = $JournalAmount -as [double]
$JournalAmount = $_.PSObject.Properties['Journal Amount'].Value
user_88572480
Related