Pasting Query Results into XL from SSMS 2012

Question

Pasting Query Results into XL from SSMS 2012

hayden_jones

SSCrazy

Points: 2099
More actions
March 13, 2014 at 12:07 pm

#305285

Previous to 2012, grid results from SSMS queries pasted into Excel beautifully and were easy to format.
Things have changed.
in 2008, The results from this query pasted into a spreadsheet with the query contained in a single Excel cell:
(sample - not the whole script)
select top 300
Avg_CPU_Time
,Total_Physical_Reads
,convert(datetime,Last_execution_time) as Timestamp
,Stored_Procedure
,Query_text
from dbadmin.dbo.History_CPU_IO_ByQueryAndSP
This was wonderful. I could deliver to developers wonderfully tidy reports on query resource usage with timestamps
along with which stored procedure the queries came from.
Can't do that in SSMS 2012. Try it. It's a disaster. The Query_text, when pasted, spreads across
multiple Excel columns, including the ones designated for other data. The result is totally unreadable.
Try it. You'll see what I mean. Or see attached pdf.
Does anyone have suggestions on how to make the query_text stay in it's own cell? I've tried converting
query_text to varchar and ntext. Same results.
As long as I still have some SSMS 2008 instances around I can do the reporting from there, but
that's inconvenient and it will be going away someday.

Viewing 12 posts - 1 through 11 (of 11 total)

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

Ville-Pekka Vahteala SSCrazy Points: 2458 More actions · Answer 1

Have tried text import wizard?

I have to use it all the time, because Finnish decimal separator is , and I need to import all floats as text and convert all . to ,

hayden_jones SSCrazy Points: 2099 More actions · Answer 2

Using the import/export wizard is time consuming and cumbersome. Frequently, it takes a few attempts

to get it to work. It also assumes you know precisely what you want in your output.

The beauty of SSMS and the way it allows tsql queries and scripts to run is that

you can fiddle with the sql query fomat until you get results you like.

Previously, you could highlight what you want, choose "copy with headers" an paste it

into Excel. That is easy. It also seems likely that the same problems would be

encountered with Export to Excel that I am complaining about here.

My question has not changed. Is there a way to take the Query_Text from dbadmin.dbo.History_CPU_IO_ByQueryAndSP

(which displays in a single cell) and paste it into a single cell in Excel? This is highly desireable. The last

several versions of SQL Server made pasting query output into Excel easy. Import/Export wizard is in

no way a substitute for easy-and-useful.

Perhaps Convert or Cast can be helpful? Perhaps there are now hex characters in the result that can

be replaced with spaces or nulls? There sure is something in that output that causes the content

to misbehave.

Ville-Pekka Vahteala SSCrazy Points: 2458 More actions · Answer 3

Is it possible that you have line breaks in query text when script is executed 2012?

Is there difference when pasting to excel for following query?

SELECT 'This is line 1.' + CHAR(13)+CHAR(10) + 'This is line 2.'

wwhite 78113 Grasshopper Points: 14 More actions · Answer 4

Copy the SSMS Query into Notepad first. Then copy from there to Excel.

I thought I broke something in Excel, good to know the cause is SSMS 2012.

Thanks

Shawn Melton SSC-Insane Points: 24685 More actions · Answer 5

Bug was reported for this on connect: SQL Management Studio output Copy and Paste or Save to CSV gives unexpected results

Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton

Luis Cazares SSC Guru Points: 183706 More actions · Answer 6

This isn't a pretty solution, but have you tried using REPLACE?

select top 300

Avg_CPU_Time

,Total_Physical_Reads

,convert(datetime,Last_execution_time) as Timestamp

,Stored_Procedure

,REPLACE(REPLACE(Query_text, CHAR(13), ' '), CHAR(10), ' ') AS Query_text

from dbadmin.dbo.History_CPU_IO_ByQueryAndSP

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

hayden_jones SSCrazy Points: 2099 More actions · Answer 7

Yes, that's exactly what I tried per hint from Ville-Pekka Vahteala . That helps a lot. That takes care of

line feed and carriage return. Now I need a replace for horizonal tab and I think I'll be done.

I am having a hard time finding the Char() value for the tab. Do you know that?

Luis Cazares SSC Guru Points: 183706 More actions · Answer 8

Is CHAR(9).

You need to remember that Books On Line is your friend 😉

http://technet.microsoft.com/es-es/library/ms187323.aspx

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Ville-Pekka Vahteala SSCrazy Points: 2458 More actions · Answer 9

And here is the English link.

http://technet.microsoft.com/en-us/library/ms187323.aspx

Luis Cazares SSC Guru Points: 183706 More actions · Answer 10

Ville-Pekka Vahteala (3/20/2014)
And here is the English link.
http://technet.microsoft.com/en-us/library/ms187323.aspx

I'm sorry, sometimes I forget to change it.:-D

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

hayden_jones SSCrazy Points: 2099 More actions · Answer 11

Books Online used to be my freind. Then I installed SSMS 2012 and Books Online disappeared into the

Microsoft treasure-hunt abyss. It's over here. No, it's over here. Well, no, that's a link to the download

of the doc that describes where to find the download of the ..................... I still don't know where to find it.

BOL used to install quickly and easily onto my hard drive. Not any more.