The Best Kept Secret About SQL Query Analyzer
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/yShlafman/thebestkeptsecretaboutsqlqueryanalyzer.asp
-
A very useful trick.
Thank you.
Henrik Staun Poulsen
Stovi Software, Denmark
-
"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...
-
"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)?
-
Thank God it was repeated!
-
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>.
Picture 1: Settings in QA to print column headers.
-
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...
-
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
-
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 🙂
-
"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?
-
Something wrong in this kingdom.
Is it joke?
For what reason such a meaningless article?
My mark - 0. Or even -2.
-
As far as I was aware, it wasn't that this was a secret, it was just so obvious that noone bothered to mention it before.
And certainly not with so many '!!!!!!!!!'s
How about this instead.
Open Enterprise Manager. Navigate to a table. Highlight it.
Hit Ctrl+C.
Goto Query Analyzer.
Hit Ctrl+V
-
Hi Graham,
It could be that I'm really tired (and I am), but step 3 of Method 1 says:
Click "Results in Text"
So we're no longer in "Grid" mode. The article therefore doesn't tell us how to get the header-row of the grid results into Excel. I'd like to do that.
You ask "What does this mean? Are you saying that you knew innately that you had to set Results output format as Tab Delimited?". Well, yes. I use Excel a little bit, and SQL Server a lot. I've been putting results into Excel this way for years. You are correct that it's not entirely natural as it was learned behaviour, but it's as natural to me as eating pizza.
Regards
Lance
-
Man..! I normally read all articles that are posted here at SQLServerCentral, some very good ones and some not that good. But ok, all topics can't interest everyone.
But when I got the mail and read the headlline "Best Kept Secret.." I was really suprised. Wow, was there something to know about a tool that I've used for sooo many years?
I hope that this article has been to use for someone. I self just got suprised that this made it to the "article of the day".
robbac
___the truth is out there___ -
Not a secret, but a good post for those who have not spotted it.
Viewing 15 posts - 1 through 15 (of 94 total)
You must be logged in to reply to this topic. Login to reply