3

In a PowerShell script I'm exporting data read from a SQL Server query into a .csv file tab delimited. I need to remove any carriage return characters.

-replace '`r`n' isn't working. I think the character is char(13).

Here's my code line that doesn't work: 

(Get-Content $MyCSV_DOR) | % {$_ -replace '`r`n', " "} | out-file -FilePath $MyCSV_DOR -Force -Encoding ascii

I've also tried the below:

`r, `n, \r, \n and \r\n

All of them fail to remove the character. I've also looked at the suggested other posts.

Are there any suggestions?

phuclv
  • 37,963
  • 15
  • 156
  • 475
Conrad S.
  • 774
  • 4
  • 10
  • 19
  • Did you try [this](https://stackoverflow.com/questions/19127741/replace-crlf-using-powershell) – Bassie May 11 '18 at 03:33
  • 4
    Single quoted strings don't accept escaped characters, only double quoted ones, so that would need to be `"\`r\`n"`, however `get-content` doesn't output any strings with line breaks because it breaks the file up on line breaks itself, and outputs individual lines. You need `get-content -raw` to read the entire file into the pipeline as one string including linebreaks – TessellatingHeckler May 11 '18 at 03:35
  • `(Get-Content $MyCSV_DOR) -replace "\`r\`n" | Set-Content -Path $MyCSV_DOR -Force -Encoding ascii` – TessellatingHeckler May 11 '18 at 03:36
  • 1
    I always use this for linefeeds `[Environment]::NewLine` – mjsqu May 11 '18 at 04:02
  • @TessellatingHeckler you should put that as an answer to the question +1 – gvee May 11 '18 at 06:24

2 Answers2

4

By default, get-content automatically splits a file into lines on newlines and if out-file receives an array of lines, it puts the newlines back. So your -replace does nothing because the newlines have already been removed by Get-Content. You need to use the -raw parameter to read the file as a single block of text. The following should do what you want

(Get-Content -Raw $MyCSV_DOR) -replace "[`r`n']+, ' ' | 
     Out-File -FilePath $MyCSV_DOR -Force -Encoding ascii -nonewline

Note: I changed the replace pattern to replace any sequence of one or more of carriage return or newline characters by a single space

Bruce Payette
  • 2,511
  • 10
  • 8
0

Use Regex matching: -replace "[\r\n]"," "

RocKeRG
  • 1