Solved Remove CR/LF that are in between double quotes.

January 29, 2013 at 11:38:54
Specs: Windows 7

I had a large comma delimited, text quoted text file that I need to modify. Some of the data has a CR/LF within its double quotes. This happens all through out the file. Here is an examle.
"123", "bla bla CR/LF bla bla", "Alpha 123", "signature CR/LF Tom Jones", ..., CR/LF

I need only the CR/LF's removed that are within the double quotes, the ones at the end of the line must stay (they are outside of the double quotes).

Any help would be much appreciated!


See More: Remove CR/LF that are in between double quotes.

Report •


#1
January 29, 2013 at 11:42:54

Also, I have been trying to learn gawk for windows. Based on another post I have attempted:

gawk -F"\042" "/\042/{gsub(\"\r\n\" ,\" \", $2)}1" OFS="\042" file

I am not sure if it works as I cant figure out how to output this to a file.

gawk is not required for this solution, just something that I am trying.

Thanks


Report •

#2
January 29, 2013 at 12:59:26
✔ Best Answer

I just happen to have PowerShell ISE up, so here's a PowerShell solution.
[string]$line = ""
Get-Content ".\test.csv" | ForEach-Object {
  if (!([Regex]::Matches(($line += $_), '"').Count -band 1)) { $line; $line = "" }
}

How To Ask Questions The Smart Way


Report •

#3
January 29, 2013 at 13:32:22

Razor - Thanks for the solution. I was able to run the command just fine but how do I send the results to a new file? It is currenlty sending the results to the screen.

Thanks!


Report •

Related Solutions

#4
January 29, 2013 at 13:39:51

Same way as with batch. Change the last line to:
} >> out.csv

How To Ask Questions The Smart Way


Report •

#5
January 29, 2013 at 14:04:40

Razor! Rock Star! That worked perfectly!

Now that I look at the data I see that I should have asked to replace the CR/LF with a space. I will try and manipulate the script to make it do that. But if you have any quick ideas.

You have been really great, thank you so much for your help!


Report •

#6
January 29, 2013 at 15:53:32

Well, if you don't mind having a space at the end of a line . . .
[string]$line = ""
Get-Content ".\test.csv" | ForEach-Object {
  if (!([Regex]::Matches(($line += $_ + " "), '"').Count -band 1)) { $line; $line = "" }
} >> out.csv

How To Ask Questions The Smart Way


Report •


Ask Question