Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

The Best Kept Secret About SQL Query Analyzer Expand / Collapse
Author
Message
Posted Thursday, September 08, 2005 10:17 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 17, 2014 8:58 PM
Points: 33, Visits: 372
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/yShlafman/thebestkeptsecretaboutsqlqueryanalyzer.asp
Post #218249
Posted Wednesday, October 26, 2005 1:13 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, February 25, 2013 6:45 AM
Points: 70, Visits: 15

A very useful trick.
Thank you.

Henrik Staun Poulsen
Stovi Software, Denmark

Post #232281
Posted Wednesday, October 26, 2005 1:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 17, 2010 1:11 AM
Points: 2, Visits: 15

"In Query Analyzer, there was no way to copy the heading of the columns from a Result Set, if the output was in grid"

Unfortunately, this is still true whether you use method 1 (results as text) or method 2 (results to file)...

Clear and to the point article though...

Post #232282
Posted Wednesday, October 26, 2005 1:20 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 06, 2012 1:17 AM
Points: 34, Visits: 150

"The Best Kept Secret About SQL Query Analyzer"

Er, wasn't this technique published in the August 2004 edition of SQL Server Magazine (InstantDoc ID 43115)?




Post #232287
Posted Wednesday, October 26, 2005 1:41 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 09, 2005 10:22 AM
Points: 102, Visits: 1
Thank God it was repeated!
Post #232288
Posted Wednesday, October 26, 2005 1:44 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, August 27, 2009 12:56 AM
Points: 348, Visits: 7

Hi,

It's a nice trick, but the explanations in the article are a bit overloaded.

The only thing that you have to do is to set the Options tab as shown on picture 1.

Then it's done. You can copy results to Excel.

When you want to switch to grid mode again, press <CTRL>-<D>.

If you want to switch back to the text mode, I click <CTRL>-<T>.

Options settings in QA

Picture 1: Settings in QA to print column headers.




Post #232289
Posted Wednesday, October 26, 2005 1:51 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, January 25, 2010 12:24 PM
Points: 79, Visits: 17

Man, There is a so much more easier way to do this, here is what I do...

1. In Query Analyser, ensure that your results target is to text and the output format is Comma Delimited (CSV) also ensure that Print Column headers(*) is ticked - This is set under the Tools|Options|Results Tab.

2. Execute your SELECT statement

3. In the Results Window highlight the entire output (Ctrl+A) the copy (Ctrl+C)

4. Open Excel

5. Highlight Column A Grid Slot 1 in Excel and paste (Ctrl+V) you will note that all of the data will reside in column A

6. Highlight The whole of Column A (Click the A bar at the top of the column)

7. Then goto the data drop down Menu then select "Text to Columns" (Alt+D+E)

8. In the Convert Text to columns wizard, select the "delimited" option them click next

9. Then Place a tick in the "Comma" box and untick the rest, then go next

10. The next window would be to change the data format, a good tip here is if you SELECT has a DateTime column, ensure that you highlight that column at this point and set the data type to text

11. Then Go FINISH

 

and you are done...

Post #232290
Posted Wednesday, October 26, 2005 2:02 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 06, 2012 1:17 AM
Points: 34, Visits: 150

Why do you use such a long-winded way? If you have Results output format as Tab Delimited you can just paste straight into Excel and each column of the query will end up in an Excel column. This only fails if the query has text columns that contain line break or similar characters, in which case the result in Excel is a mess




Post #232291
Posted Wednesday, October 26, 2005 2:07 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, November 26, 2012 7:59 PM
Points: 169, Visits: 62
I'm sure the article will be appreciated by many, but I had to rate the article down because of that enticing Subject which turned out to be something of a large exageration ...

Hardly a secret. It's perfectly natural to export to Excel that way. Now if the article had really told me how to cut and paste the grid results into Excel - with the header row - then I'd have given the article a really good rating



Post #232292
Posted Wednesday, October 26, 2005 2:15 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 06, 2012 1:17 AM
Points: 34, Visits: 150

"Now if the article had really told me how to cut and paste the grid results into Excel - with the header row"

But it did...

"It's perfectly natural to export to Excel that way"

What does this mean? Are you saying that you knew innately that you had to set Results output format as Tab Delimited?




Post #232295
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse