powershell - Split column value in CSV file using multiple and duplicate delimiters -

i have csv file mess. i'm trying use regex extract first name , last name value in column in csv file. first , last names have own columns.

the csv file (with different combinations of delimiters):

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

to add first , last name columns, using following code:

function fixrxclaimreportaddfirstlastnamecolumn {   param ($csvfile)    write-host "adding columns 'first name' , 'last name' $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' 

when run code, first name values should john, , last name values should doe. however, values different all.

you're thinking complicated. remove additional information end of description field name, trim name , split first , last name before adding these new properties input object.

try this:

import-csv 'c:\path\to\input.csv' | foreach-object {   $rawname = $_.description -replace '-[^-]*-[^-]*$'   $firstname, $lastname = $rawname.trim() -split ' *[ \?\.,-] *'   $_ | add-member -type noteproperty -name firstname -value $firstname   $_ | add-member -type noteproperty -name lastname -value $lastname   $_ } | export-csv 'c:\path\to\output.csv' -notype