In PowerShell how to replace carriage return

Question

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?

Answer 1

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