Author Posts

December 6, 2017 at 2:39 am

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

December 6, 2017 at 3:57 am

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'

December 7, 2017 at 2:30 pm

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

December 9, 2017 at 1:04 am

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",")
# adding Carriage return + New line to end of the string if all the lines are double spaced remove the below line.
$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

December 15, 2017 at 3:40 pm

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