Hello everyone.
I have a script that executes a stored procedure on a MSSQL server and creates an CSV. The problem I am having is that one of the database fields in the query is a free form text filed and in many entries it contains CRLF’s. This is causing the CSV to not format correctly. I am looking to add a to my script a method to remove these extra CRLF’s. I would be grateful for any assistance.
Thanks!!!
Just use the -replace option with a RegEx match or depending on the string a -split make work as well.
See this discussion on the very site as well…
‘powershell.org/forums/topic/crlf-and-split’
Thanks for the feed back. I have looked over the examples and tried to do some experimenting and so far have come up short.
The CSV I am creating from the SQL query is being pipe delimited so what I really need to do is to remove any CRLF’s that are between the pipes.
Any help would be greatly appreciated!!!
So replace `r`n in all strings with nothing if you end up with one long line at the end add + “`r`n” at the end of each line.
Note the single and double quotes.
Code
$My_String = $My_String.Replace(“`r`n”,‘’)
$My_String = $My_String + “`r`n”
I think this would be of help
Escape characters, Delimiters and Quotes
https://ss64.com/ps/syntax-esc.html
`0 Null
`a Alert bell/beep
`b Backspace
`f Form feed (use with printer output)
`n New line
`r Carriage return
`r`n Carriage return + New line
`t Horizontal tab
`v Vertical tab (use with printer output)
Hope this helps
Thanks Bill. I think that is starting to get me going closer.
The CSV has over 2 million lines
Here is the code I have tried but it seems to be putting it all in 1 line
$DATE = get-date -f yyyyMMdd
$path = “C:\File_$DATE.csv”
$Data = Get-Content $path -Raw
$Cleanup = $Data.Replace(“`r`n”,“”)
$Cleanup = $Cleanup + “`r`n” | Set-Content $path -Force
I am not sure that I ma missing to get it to not put it all in 1 line 
Thanks a bunch!!!