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?
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
