У меня есть файл CSV, который беспорядок. Я пытаюсь использовать регулярное выражение для извлечения имени и фамилии из значения в столбце в файле csv. Имя и фамилия будут иметь свои собственные столбцы.
Файл CSV (с разными комбинациями разделителей):
ID,Description,Number JDo,John Doe - Temp - Client Client Ops,SomeValue JDo,John Doe - Temp - Client Client Ops,SomeValue JDo,John Doe - Temp - Client Client Ops,SomeValue JDo,John Doe - Temp - Client Client Ops,SomeValue JDo,John Doe - Temp - Client Client Ops,SomeValue JDo,John Doe - Temp - Client Client Ops,SomeValue JDo,John Doe - Temp - Client Client Ops,SomeValue JDo,John Doe - Temp - Client Client Ops,SomeValue JDo,John Doe - Temp - Client Client Ops ,SomeValue JDo,John Doe - Temp - Client Client Ops ,SomeValue JDo,John Doe-Temp-Client Client Ops,SomeValue JDo,John Doe - Temp-Client Client Ops,SomeValue JDo,John Doe - Temp-Client Client Ops,SomeValue JDo,John Doe-Temp - Client Client Ops,SomeValue JDo,John Doe - Temp - Client Client Ops,SomeValue JDo,John Doe - Temp - Client Client Ops,SomeValue JDo,John Doe - Temp - Client Client Ops,SomeValue JDo,John Doe - Temp - Client Client Ops,SomeValue JDo,John Doe-Temp - Client Client Ops ,SomeValue JDo,John Doe-Temp-Client Client Ops ,SomeValue JDo,John.Doe - Temp - Client Client Ops,SomeValue JDo,John .Doe - Temp - Client Client Ops,SomeValue JDo,John. Doe - Temp - Client Client Ops,SomeValue JDo,John . Doe - Temp - Client Client Ops,SomeValue JDo,John.Doe - Temp - Client Client Ops ,SomeValue JDo,John .Doe - Temp - Client Client Ops ,SomeValue JDo,John. Doe - Temp - Client Client Ops ,SomeValue JDo,John . Doe - Temp - Client Client Ops ,SomeValue JDo,John.Doe-Temp-Client Client Ops,SomeValue JDo,John .Doe-Temp-Client Client Ops,SomeValue JDo,John. Doe-Temp-Client Client Ops,SomeValue JDo,John . Doe-Temp-Client Client Ops,SomeValue JDo,John.Doe - Temp - Client Client Ops,SomeValue JDo,John .Doe - Temp - Client Client Ops,SomeValue JDo,John. Doe - Temp - Client Client Ops,SomeValue JDo,John . Doe - Temp - Client Client Ops,SomeValue JDo,John?Doe - Temp - Client Client Ops,SomeValue JDo,John ?Doe - Temp - Client Client Ops,SomeValue JDo,John? Doe - Temp - Client Client Ops,SomeValue JDo,John ? Doe - Temp - Client Client Ops,SomeValue JDo,John?Doe - Temp - Client Client Ops ,SomeValue JDo,John ?Doe - Temp - Client Client Ops ,SomeValue JDo,John? Doe - Temp - Client Client Ops ,SomeValue JDo,John ? Doe - Temp - Client Client Ops ,SomeValue JDo,John?Doe-Temp-Client Client Ops,SomeValue JDo,John ?Doe-Temp-Client Client Ops,SomeValue JDo,John? Doe-Temp-Client Client Ops,SomeValue JDo,John ? Doe-Temp-Client Client Ops,SomeValue JDo,John?Doe - Temp - Client Client Ops,SomeValue JDo,John ?Doe - Temp - Client Client Ops,SomeValue JDo,John? Doe - Temp - Client Client Ops,SomeValue JDo,John ? Doe - Temp - Client Client Ops,SomeValue JDo,"John,Doe - Temp - Client Client Ops",SomeValue JDo,"John ,Doe - Temp - Client Client Ops",SomeValue JDo,"John, Doe - Temp - Client Client Ops",SomeValue JDo,"John , Doe - Temp - Client Client Ops",SomeValue JDo," John,Doe - Temp - Client Client Ops ",SomeValue JDo," John ,Doe - Temp - Client Client Ops ",SomeValue JDo," John, Doe - Temp - Client Client Ops ",SomeValue JDo," John , Doe - Temp - Client Client Ops ",SomeValue JDo,"John,Doe-Temp-Client Client Ops",SomeValue JDo,"John ,Doe-Temp-Client Client Ops",SomeValue JDo,"John, Doe-Temp-Client Client Ops",SomeValue JDo,"John , Doe-Temp-Client Client Ops",SomeValue JDo,"John,Doe - Temp - Client Client Ops",SomeValue JDo,"John ,Doe - Temp - Client Client Ops",SomeValue JDo,"John, Doe - Temp - Client Client Ops",SomeValue JDo,"John , Doe - Temp - Client Client Ops",SomeValue JDo,John-Doe - Temp - Client Client Ops,SomeValue JDo,John -Doe - Temp - Client Client Ops,SomeValue JDo,John- Doe - Temp - Client Client Ops,SomeValue JDo,John - Doe - Temp - Client Client Ops,SomeValue JDo,John-Doe - Temp - Client Client Ops ,SomeValue JDo,John -Doe - Temp - Client Client Ops ,SomeValue JDo,John- Doe - Temp - Client Client Ops ,SomeValue JDo,John - Doe - Temp - Client Client Ops ,SomeValue JDo,John-Doe-Temp-Client Client Ops,SomeValue JDo,John -Doe-Temp-Client Client Ops,SomeValue JDo,John- Doe-Temp-Client Client Ops,SomeValue JDo,John - Doe-Temp-Client Client Ops,SomeValue JDo,John-Doe - Temp - Client Client Ops,SomeValue JDo,John -Doe - Temp - Client Client Ops,SomeValue JDo,John- Doe - Temp - Client Client Ops,SomeValue JDo,John - Doe - Temp - Client Client Ops,SomeValue
Чтобы добавить столбцы имени и фамилии, я использую следующий код:
Function FixRxClaimReportAddFirstLastNameColumn {
Param ($csvFile)
Write-Host "Adding columns 'First Name' and 'Last Name' to $csvFile"
Import-Csv $csvFile |
Select-Object *, @{n='First Name'; e={if ($_.Description) {
$columnFirstNameValue = $($_.Description -replace '\s+', ' ').split(" ")[0]
if ($columnFirstNameValue -notlike "*,*" -and $columnFirstNameValue -notmatch '\?' -and $columnFirstNameValue -notlike "*.*" -and $columnFirstNameValue -notlike "*-*") {
$columnFirstNameValue.Trim()
} else {
$columnFirstNameValue2 = $($_.Description -replace '\s+', ' ') -split {$_ -eq "-" -or $_ -eq "- " -or $_ -eq " -" -or $_ -eq " - " -or $_ -eq "," -or $_ -eq ", " -or $_ -eq " ," -or $_ -eq " , " -or $_ -eq "." -or $_ -eq ". " -or $_ -eq " ." -or $_ -eq " . " -or $_ -eq "?" -or $_ -eq "? " -or $_ -eq " ?" -or $_ -eq " ? "}
$columnFirstNameValue2[0].Trim()
}
}}}, @{n='Last Name'; e={if ($_.Description) {
$columnLastNameValue = $($_.Description -replace '\s+', ' ').split(" ")[1]
if ($columnLastNameValue -notlike "*,*" -and $columnLastNameValue -notmatch '\?' -and $columnLastNameValue -notlike "*.*" -and $columnLastNameValue -notlike "*-*") {
$columnLastNameValue.Trim()
} else {
$columnLastNameValue2 = $($_.Description -replace '\s+', ' ') -split {$_ -eq "-" -or $_ -eq "- " -or $_ -eq " -" -or $_ -eq " - " -or $_ -eq "," -or $_ -eq ", " -or $_ -eq " ," -or $_ -eq " , " -or $_ -eq "." -or $_ -eq ". " -or $_ -eq " ." -or $_ -eq " . " -or $_ -eq "?" -or $_ -eq "? " -or $_ -eq " ?" -or $_ -eq " ? "}
$columnLastNameValue2[1].Trim()
}
}}} | Export-Csv "$csvFile-Results.csv" -NoTypeInformation -Force
Write-Host "Complete."
Write-Host ""
}
FixRxClaimReportAddFirstLastNameColumn 'C:\Scripts\Tests\Test1.csv'
Когда я запускаю этот код, все значения имени должны быть John, а все значения фамилии должны быть Doe. Однако значения у всех очень разные.
