-2

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","faflitto@cgf.com"

I want data to be like this:

Whaling Mark |faflitto@cgf.com

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
Santiago Squarzon
  • 41,465
  • 5
  • 14
  • 37
Ved
  • 35
  • 5
  • 5
    Honestly, `Import-Csv pathtofile.ext | Export-Csv pathtofile2.ext -Delimiter '|'` – Santiago Squarzon Apr 20 '22 at 20:12
  • I appreciate your help. it is not working my case. i guess because it is txt file even though it is comma separated.. Thank you anyway @SantiagoSquarzon – Ved Apr 20 '22 at 20:31
  • 4
    In which sense is not working? – Santiago Squarzon Apr 20 '22 at 20:34
  • I ran this code Get-Process Import-Csv pathtofile.ext | Export-Csv pathtofile2.ext -Delimiter '|' . it was throwing errors. i knew i made some mistake. trying ... – Ved Apr 20 '22 at 20:44
  • 3
    Why is `Get-Process` before `Import-Csv` ? That might be your issue. Furthermore, you should clarify [in your question](https://stackoverflow.com/posts/71945645/edit) instead of in comments. – Santiago Squarzon Apr 20 '22 at 20:46
  • I am so sorry. it is working but there are double quotes in the fields and some fields have comma those should be removed and replace with white space. Thank you very much. @SantiagoSquarzon – Ved Apr 20 '22 at 20:53

1 Answers1

0

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
Darin
  • 1,423
  • 1
  • 10
  • 12