February 12, 2014 at 5:19 am
Hi,
While generating Ad-Hoc reports, I copy SSMS (SQL 2014) results to Excel. When there is <New Line Character> in the result set, excel formatting will be disturbed.
I have a work around, but looking for actual setting to address the problem.
FYI: When I connect to same SQL server, run the same query from a different machine (SQL 2005) this works as expected (New Line Character will be inserted into same Excel Field/Cell), this seems to be related to SQL Server Management Studio setting. Can someone help me to find a solution?
Thanks,
Vijay
-Vijred (http://vijredblog.wordpress.com)
February 18, 2014 at 11:03 pm
I'm assuming what you are saying is that you have been copying from SSMS "Grid Output" and pasting in Excel. I know that the "Grid Output" option strips out new line characters which would be why you previously did not have a problem. I haven't used 2014 yet, but I suspect that there may have been a change to the way that the grid output now works.
Can you try using the following around any of the columns that might have new line characters in them?
REPLACE(REPLACE([column],CHAR(10),''),CHAR(13),'') AS [column]
Alternatively, you could surround all the text fields with double quotes, but if you have new line characters in the fields it is likely that you also have double quotes, so you would need to use the following (it's hard to tell but it is replacing double quotes with two double quotes - it's just how Excel likes things - not saying I agree with it):
'"' + REPLACE([column],'"','""') + '"' AS [column]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply