Export to excel - column with CR/LF

  • i have a request to export some table data to excel and the "notes" column (varchar 255) contains multiple lines separated by CR/LF. when I export to excel, the first record with CR/LF messes up the column alignment in excel, throwing off the format from that point on. how can i export to excel so that it preserves these CR/LF. or if not, how can I remove these characters so that excel can handle it?

    thanks!

    see attached example

  • You can use field terminator.Please read below link for more information.

    https://msdn.microsoft.com/en-us/library/ms191485.aspx

    --rhythmk
    ------------------------------------------------------------------
    To post your question use below link

    https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
    🙂

  • Is it an automated process or a one-off requirement? In the latter case, you can use the SSMSBoost[/url] add-in to export the query results to Excel.

    -- Gianluca Sartori

  • thanks for the suggestions.

    the bcp terminator switches didn't seem to help. no matter what the bcp uses to terminate fields and rows, excel still craps out on the CR/LF characters.

    i'll have a look at SSMSBoost later. Looks like something I could use.

    To resolve, I found the easier thing is to scrub the columns of the CR/LF (and TAB) characters :

    UPDATE temp_EXPORT

    SET SUMMARY = REPLACE(SUMMARY, CHAR(13), ' '),

    NOTE_TEXT = REPLACE(NOTE_TEXT, CHAR(13), ' '),

    RESOLUTION_TEXT = REPLACE(RESOLUTION_TEXT, CHAR(13), ' ')

    UPDATE temp_EXPORT

    SET SUMMARY = REPLACE(SUMMARY, CHAR(10), ' '),

    NOTE_TEXT = REPLACE(NOTE_TEXT, CHAR(10), ' '),

    RESOLUTION_TEXT = REPLACE(RESOLUTION_TEXT, CHAR(10), ' ')

    UPDATE temp_EXPORT

    SET SUMMARY = REPLACE(SUMMARY, CHAR(9), ' '),

    NOTE_TEXT = REPLACE(NOTE_TEXT, CHAR(9), ' '),

    RESOLUTION_TEXT = REPLACE(RESOLUTION_TEXT, CHAR(9), ' ')

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply