Removing CRLF from CSV created by SQL query

Welcome Forums General PowerShell Q&A Removing CRLF from CSV created by SQL query

This topic contains 4 replies, has 3 voices, and was last updated by

 
Participant
1 year ago.

  • Author
    Posts
  • #86660

    Participant
    Points: 0
    Rank: Member

    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

    Participant
    Points: 320
    Helping Hand
    Rank: Contributor

    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

    Participant
    Points: 0
    Rank: Member

    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

    Participant
    Points: 0
    Rank: Member

    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

  • #89306

    Participant
    Points: 0
    Rank: Member

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

The topic ‘Removing CRLF from CSV created by SQL query’ is closed to new replies.