ฉันมีไฟล์ CSV ที่ไม่เป็นระเบียบ ฉันกำลังพยายามใช้ regex เพื่อแยกชื่อและนามสกุลออกจากค่าในคอลัมน์ในไฟล์ 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 อย่างไรก็ตาม ค่านิยมนั้นแตกต่างกันมากสำหรับทุกคน
