0

I am trying to remove the double quotation marks in the middle of the string field in a CSV file by using a power shell. Please find the attached sample data. Here is the code I am trying but it is not working. please suggest the best solution. thanks in advance.

Get-ChildItem $Outgoing -Filter *.csv | ForEach-Object {
(Get-Content $_.FullName -Raw) | Foreach-Object {
   $_  -replace '(?m)(?<=,|^)"([^,"]*)"([^,"]*)"(?=,|$)', '"$1$2"'  
  } | Set-Content $_.FullName
}

enter image description here

DAR
  • 47
  • 6
  • 1
    Please share the CSV as plain text instead of a screenshot – Santiago Squarzon Feb 19 '22 at 16:39
  • 1
    If _inner_ double quotation marks are `“` (U+201C, *Left Double Quotation Mark*) or `”` (U+201D, *Right Double Quotation Mark*) then it's OK. On the other side, _inner_ `"` (U+0022, *Quotation Mark*) should be doubled otherwise the `csv` is malformed. Generate valid `csv` instead of fixing… Btw, limit PowerShell version tags to actually used (see [mcve]). – JosefZ Feb 20 '22 at 14:27
  • Isn't this a duplicate of your [earlier question](https://stackoverflow.com/q/70526751/9898643). You received two working answers there but accepted none... Also, stop tagging your questions with all PowerShell versions you can find. Either just use the single `PowerShell` tag, or at least remove the version tags you do not actually use. – Theo Feb 21 '22 at 12:00
  • This is a similar question, but not the same one. As I mentioned the code I am using is not working. Updated the PowerShell versions. Any suggestions? – DAR Feb 22 '22 at 05:23
  • We are receiving the CSV files from the vendor, it cannot fix on their side. we have to fix these issues before loading on our side. – DAR Feb 22 '22 at 05:45

0 Answers0