我有一个PowerShell脚本,用于从CSV导入数据。但是我收到了以下错误:
无法将值“Journal Amount”转换为类型“System.Double”。错误:“输入字符串不符合正确的格式。” 位置 C:\TestPowerShell\TestPowerShell1.ps1:98 字符:1 + $JournalAmount = [double] $_.PSObject.Properties['Journal Amount'].Va ... + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidArgument: (:) [], RuntimeException + FullyQualifiedErrorId : InvalidCastFromStringToDoubleOrSingle
这是我的代码:
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)"
Import-Csv
中指定-Header $Header
时,它会将所有行都解释为数据(包括第一行),这就解释了为什么你在“Journal Amount”列中得到了“Journal Amount”值 :) - Mathias R. JessenWrite-Host $_.PSObject.Properties['Journal Amount'].Value
的代码,这可能会帮助你理解为什么该值无法转换为 Double。 - Nick Graham