Replace commas present in quotes in a CSV file

Question

_{Tuesday, February 7, 2017 6:04 PM}

Trying to replace commas present in a CSV file which are only present in between quotes.

Source : abc, 1, 2,"abc, 123", 3,"xyz, 67",5

Expected : abc, 1, 2,"abc-123", 3,"xyz-67",5

I am using Get-content and replace and also contemplating on using SPLIT. Please advice if there is another function or SPLIT can be used to only look and replace for file content that is between quotes.

tried using below command but it doesnt help if there are multiple texts in quoted:

(Get-Content $Path4 | ForEach-Object {$_.Replace($_.Substring($_.indexof("`"")), $_.substring($_.indexof("`"")).replace(',',''))})

All replies (11)

_{Tuesday, February 7, 2017 6:29 PM ✅Answered}

$Pattern = '(?<!"".+),(?!.+"")'
$Text = '"abc, 123"'
$Text -replace $Pattern , '-'

Grüße - Best regards

PS:> (79,108,97,102|%{[char]$_})-join''

_{Tuesday, February 7, 2017 6:36 PM}

the above solution doesn't work when there are multiple instances of same format in the same line of the filetried using below command but it doesn't help if there are multiple texts in quoted:

(Get-Content $Path4 | ForEach-Object {$_.Replace($_.Substring($_.indexof("`"")), $_.substring($_.indexof("`"")).replace(',',''))})

_{Tuesday, February 7, 2017 6:38 PM}

$csv = Import-Csv file.csv |%{
**     # do this for each field that might have commas.**
**    $_.Name =  $_.Name -replace ','.'-'**
}

\(ツ)_/

_{Tuesday, February 7, 2017 6:40 PM}

I want to retain the other commas which are not in quotes.

_{Tuesday, February 7, 2017 6:51 PM}

I want to retain the other commas which are not in quotes.

That is why you manage it as a CSV file by importing it.

\(ツ)_/

_{Tuesday, February 7, 2017 7:08 PM}

Hi ,

With the help of regex,

Get-Content -Path $FilePath | Foreach-Object -Process {
$_ -Match '(\B"[^"]*),([^"]*"\B)' | Out-Null
$_ -replace $Mathces[0],($Matches[0] -replace ',','-')
}

ref: Here

Regards,

Kvprasoon

Regards kvprasoon

_{Tuesday, February 7, 2017 10:57 PM}

You have a typo in your code and that approach replaces only the first occurence of a comma between double quotes .... just like my suggestion. ;-)

Grüße - Best regards

PS:> (79,108,97,102|%{[char]$_})-join''

_{Tuesday, February 7, 2017 11:00 PM}

You have to treat every "cell" by its own. And the "text" in that cell is only allowed to have one comma in between double quotes. Otherwise it will fail.

Grüße - Best regards

PS:> (79,108,97,102|%{[char]$_})-join''

_{Thursday, February 9, 2017 4:04 PM}

thank you, this helped in deriving to this regex "(?<=,`".*?), (?=.*?`",)" and able to use get-content to fetch a file and amend each line with this pattern. 

_{Friday, February 10, 2017 8:28 AM}

This regex won't work.

Yes it does. Try it with the "Source" from the first original post.

Grüße - Best regards

PS:> (79,108,97,102|%{[char]$_})-join''

_{Friday, February 10, 2017 8:29 AM}

This regex **does work:
**

You're right. This works too.  ;-)

Grüße - Best regards

PS:> (79,108,97,102|%{[char]$_})-join''