在CSV中搜索powershell中a列中的多个字符串

Question

我有一个sample.csv文件

#Period,Account,Entity,Year,Version,Currency,HSP_Rates,Scenario,Data
Apr,1,9,FY22,F,L,H,And,2
Apr,1,9,FY22,F,L,H,And,2
Apr,1,9,FY22,F,L,H,OR,2

这里我想得到输出csv文件场景只等于AND

#Period,Account,Entity,Year,Version,Currency,HSP_Rates,Scenario,Data
Apr,1,9,FY22,F,L,H,And,2
Apr,1,9,FY22,F,L,H,And,2

我写的代码不给需要输出

$csvRaw = Get-Content -Path 'D:sample.csv' -Raw 
$Csv = $CsvRaw.TrimStart('#') | ConvertFrom-Csv
$NewCsv = $csv | ForEach-Object {
[PsCustomObject]@{
Period    = $_.Period
Account   = $_.Account
Entity    = $_.Entity
Year      = $_.Year
Version   = $_.Version
Currency  = $_.Currency
HSP_Rates = $_.HSP_Rates
Scenario  = $_.Scenario | where {$_.Scenario -match "And" }
Data      = $_.Data
} }
$OutCsv = ($NewCsv | ConvertTo-Csv -NoTypeInformation).TrimStart('"#')
# Converting back to CSV surround everything with double quotes. # We need to insert back the hash sign for the #period header$OutCsv[0] = """#" + $OutCsv[0]
$OutCsv | Out-File 'D:sample_1.csv'

Answer 1

在[PSCustomObject]的构造中似乎存在语法问题。试着更换:

$NewCsv = $csv | ForEach-Object {
[PsCustomObject]@{
Period    = $_.Period
Account   = $_.Account
Entity    = $_.Entity
Year      = $_.Year
Version   = $_.Version
Currency  = $_.Currency
HSP_Rates = $_.HSP_Rates
Scenario  = $_.Scenario | where {$_.Scenario -match "And" }
Data      = $_.Data
} }

:

$NewCsv = $csv | ForEach-Object {
[PsCustomObject]@{
Period    = $_.Period
Account   = $_.Account
Entity    = $_.Entity
Year      = $_.Year
Version   = $_.Version
Currency  = $_.Currency
HSP_Rates = $_.HSP_Rates
Scenario  = $_.Scenario
Data      = $_.Data
} } | Where { $_.Scenario -match "And" }

Which——正如Theo所评论的——基本上与:

$NewCsv = $csv | Where { $_.Scenario -match "And" }

同样，如果您正在使用Powershell 6+，您可以利用ConvertTo-CSVcmdlet中的-UseQuotes参数从输出中删除引号。