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
-
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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply