Removing CRLF from CSV created by SQL query

This topic contains 3 replies, has 3 voices, and was last updated by  Bill C 5 days, 8 hours ago.

  • Author
    Posts
  • #86660

    Nathan W
    Participant

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

  • #86675

    postanote
    Participant

    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'

  • #87512

    Nathan W
    Participant

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

  • #88067

    Bill C
    Participant

    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

You must be logged in to reply to this topic.