时间戳

首页 > 解决方案 > Powershell csv仅在特定列中查找带有反斜杠的值并重命名它们

powershell - Powershell csv仅在特定列中查找带有反斜杠的值并重命名它们

问题描述

我正在尝试过滤“结果”列中的 csv 文件以获取带有反斜杠的值，然后用适当的名称重命名它们。

这是example.csv

Accession,SrvDate,EntryDate,FinalReportDate,Patient First Name,Patient Last Name,DOB,Gender,Race, Ethnicity,Patient Address,Patient City, Patient State,Patient Zip,Patient Phone,Test Code,Test Name,Result,ClientID, Client Name,Phys  ID, Phys  Name
2132900941,NULL, 11-24-2020,11/29/2020,MICHELL,PENDERGRAS,09/30/1981,F,,U,35 RUSEVELT ST,PRUDENCE, RB,2909, (401)516-5642,Z620, Sars-COVID19, Positive,99984,"ASHRAF FARID, M.D.",50845,ATPAV RIGTED
2232900942,NULL, 11-25-2020,11/29/2020,MICHEL,PENDERGRA, 9/30/1982,M,,U,315 RUSEVELT ST,PRUDENCE, RB,2909, (401)516-5643,Z620, Sars-COVID19, Negative,99984,"ASHRAF FARID, M.D.",50845,ATPAV RIGTED
2332900943,NULL, 11-26-2020,11/29/2020,MICHE,PENDERGR,  9/30/1983,F,,U,325 RUSEVELT ST,PRUDENCE, RB,2909, (401)516-5644,Z620, Sars-COVID19, \989,99984,"ASHRAF FARID, M.D.",50845,ATPAV RIGTED
2432900944,NULL, 11-27-2020,11/29/2020,MICH,PENDERG,  9/30/1984,M,,U,335 RUSEVELT ST,PRUDENCE, RB,2909, (401)516-5645,Z620, Sars-COVID19, \990,99984,"ASHRAF FARID, M.D.",50845,ATPAV RIGTED
2532900945,NULL, 11-28-2020,11/29/2020,ALLISO,JEZA,10/13/1977,F,,U,15 KAUTEEKEE AVE, SOUTH PRUDENCE,RB,2911, (908)930-9213,Z620, Sars-COVID19, \61,99984,"ASHRAF FARID, M.D.",50845,ATPAV RIGTED
2632900946,NULL, 11-29-2020,11/29/2020,ALLIS,JEZ, 10/13/1978,M,,U,151 KAUTEEKEE AVE,SOUTH PRUDENCE,RB,2911, (908)930-9214,Z620, Sars-COVID19, \990,99984,"ASHRAF FARID, M.D.",50845,ATPAV RIGTED
2732900947,NULL, 11-30-2020,11/29/2020,ALLI,JE,  10/13/1979,F,,U,152 KAUTEEKEE AVE,SOUTH PRUDENCE,RB,2911, (908)930-9215,Z620, Sars-COVID19, @,99984,"ASHRAF FARID, M.D.",50845,ATPAV RIGTED
2832900948,NULL, 12-1-2020,11/29/2020,ALL,J,  10/13/1980,M,,U,153 KAUTEEKEE AVE,SOUTH PRUDENCE,RB,2911, (908)930-9216,Z620, Sars-COVID19, Positive,99984,"ASHRAF FARID, M.D.",50845,ATPAV RIGTED

\990 应命名为“推定阳性”

\989 应命名为“无效”

\61 应命名为“QNS”

这是我试图用来简单地过滤反斜杠值的脚本，但后来我卡住了：

$inval = [regex]::escape('\989')
$prespos = [regex]::escape('\990')
$in = Import-Csv (Get-ChildItem -Path C:\example.csv).Fullname | ? "Result" -in $inval, $prespos

或像这样：

$in = Import-Csv (Get-ChildItem -Path C:\example.csv).Fullname | ? "Result" -in "\990", "\989"

但是变量 $in 没有输出。

你能帮我解决吗。

谢谢！

标签： powershellcsv

解决方案

Result如果该字段包含与您要替换的数据完全匹配的内容，我会执行以下操作。

# read Csv as object in $data
$data = Import-Csv example.csv
# hash of replacement values
$hash = @{'\990' = 'Presumptive Positive'; '\989' = 'Invalid'; '\61' = 'QNS'}
# only select rows that need to be replaced
foreach ($row in $data | Where Result -in $hash.keys) {
    # update current row result with replacement value
    $row.Result = $hash[$row.Result]
}
# output object as CSV
$data | Export-Csv output.csv -NoType

推荐阅读