Removing CRLF from CSV created by SQL query
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.
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...
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.
$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
`a Alert bell/beep
`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!!!
You must be logged in to reply to this topic.