Powershell script to remove any line with extra double quote "

There's probably a bunch of ways to do this. But here's a quickie.

foreach ($line in $file)
{
	$line -replace '("\$"[\w\s]*)(")?([\w\s]+)?(")?("\$")','$1$3$5'
}

Select allOpen in new window

It is matching your specific example patterns though. EG:

"$"Limited  Company "Maxis Segar ""$"
"$"Limited  Company Maxis Segar "$"
"$"Blah Blah "$"
"$"sdfsdfsdf "Blah Blah" "$"

$test = Get-Content C:\IT\querry.txt

$newtest = $test -replace '""' , '"'

$newtest > c:\it\newquerry.txt

Benjamin, it is not about replacing two successive double quotes only - each double quote inside of the delimiters should get removed.

Another way to do it is to remove the delimiters, remove double quotes, then add back delimiters:

Get-Content file.txt -replace '"\$"', '' -replace '"', '' -replace '(.+)', '"$"$1"$"' | Out-File file2.txt

Select allOpen in new window

There are no real pros or cons compared to -replace '("\$"[\w\s]*)(")?([\w\s]+)?(")?("\$")','$1$3$5' or similar regular expressions.

Hi,

I'm trying with one of the example script here but getting error as below.

Windows PowerShell
Copyright (C) 2014 Microsoft Corporation. All rights reserved.

PS C:\Users\BDXTY> Get-Content D:\Programs\actual\Folder3\Output\SolidFile.csv -replace '"\$"', '' -replace '"', '' -replace '(.+)', '"$"$1"$"' | Out-File D:\Programs\actual\Folder3\Output\SolidFile.csv
Get-Content : A parameter cannot be found that matches parameter name 'replace'.
At line:1 char:47
+ Get-Content D:\Programs\actual\Folder3\Output\SolidFile.csv -replace '"\$"', '' -replace '"',  ...
+                                               ~~~~~~~~
    + CategoryInfo          : InvalidArgument: (:) [Get-Content], ParameterBindingException
    + FullyQualifiedErrorId : NamedParameterNotFound,Microsoft.PowerShell.Commands.GetContentCommand

PS C:\Users\BDXTY>

Sorry, forgot to copy the parens over to EE:

(Get-Content D:\Programs\actual\Folder3\Output\SolidFile.csv) -replace '"\$"', '' -replace '"', '' -replace '(.+)', '"$"$1"$"' | Out-File D:\Programs\actual\Folder3\Output\SolidFile.csv

Select allOpen in new window

Also,  be careful, you are overwriting the original file. Dangerous if in a pipe, which reads the file content while it processes (and replaces) it - leading to an empty output file ... The parens take care of that, as file content is first read in completely before getting processed.

Hi Qlemo.
The script works but somehow it removed all double quote for each column delimeter, from the example below.

"$"Limited  Company "Maxis Segar ""$"

It should be like this

"$"Limited  Company Maxis Segar "$"

With above input it generates the above output. If it does not for you, can you please post an input and output file, as you get it?

Hi Qlemo
You can try with this input file attached here, What I would like this scripts does for me is to preserve all "$"  but only remove those extra double quote within a delimiter.
input.csv

Hi,
One more issues that  puzzled me now, I tried with creating many entries in the text file by simply duplicating the 1st line in text file to multiple entry to be exact 1000 duplicate line. I ran the command again, at this time the size of the text file was double, as shown in the screenshot, any reasons why this happened?
Capture.PNG

Hi Qlemo,
I managed to resolved this, by adding -encoding ASCII, than the file has the same size as the original file.
Btw, don't you mind to explain how this works at every -replace ? what actually it does here ...

-replace '"\$"', '|' -replace '"', '' -replace '\|', '"$"' -replace '(.+)\$', '"$1"$'

Thanks Qlemo, you always be  nice and helping us here :)