Saya memiliki file CSV yang berantakan. Saya mencoba menggunakan regex untuk mengekstrak nama depan dan nama belakang dari nilai di kolom di file csv. Nama depan dan belakang akan memiliki kolomnya masing-masing.
File CSV (dengan kombinasi pembatas berbeda):
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
Untuk menambahkan kolom nama depan dan belakang, saya menggunakan kode berikut:
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'
Saat saya menjalankan kode ini, semua nilai nama depan harus John, dan semua nilai nama belakang harus Doe. Namun, nilainya sangat berbeda untuk semua orang.
