How to replace comma with pipe?-CodePudding

I am in the situation where I need to replace the comma with pipe using PowerShell. the fields are within double quotes so I need to remove them as well. but Some fields have comma in the data so When I replace, I should keep fields commas with while space.

Data look like this:

"Whaling, Mark","[email protected]"

I want data to be like this:

Whaling Mark |[email protected]

How to achieve this using PowerShell?. Help appreciated.

My script is this for now:

(Get-Content -ReadCount 0 Compliance2022.txt) -replace ',','|' -replace '"',' ' | Set-Content COMPLIANCE2022_.txt

CodePudding user response：

I did NOT test this whole line, just the code between the Get-Content and Set-Content commands. The regex came from here.

(Get-Content -ReadCount 0 Compliance2022.txt) | ForEach-Object {($_ -Split '(?!\B"[^"]*),(?![^"]*"\B)') -Join(' | ')} | Set-Content COMPLIANCE2022_.txt

Odds are it misses exactly what you want, but maybe we can fine tune it to get to work.

EDIT:

This version removes the quotes.

(Get-Content -ReadCount 0 Compliance2022.txt) | ForEach-Object {(($_ -Split '(?!\B"[^"]*),(?![^"]*"\B)') -Join(' | ')) -replace '"', " "} | Set-Content COMPLIANCE2022_.txt