Get columns from query analyser HELP !

Question

Get columns from query analyser HELP !

ritesh-385882

Mr or Mrs. 500

Points: 533
More actions
December 18, 2007 at 5:02 am

#185162

Hi, I'm trying to run some data from query analyser and then extract this onfo in excel.
As there a lot of columns in analyser I want to copy the column names into excel.
is there any way to get the column name ?
Regards
Ritesh

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

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

Carolyn Richardson SSCrazy Eights Points: 8357 More actions · Answer 1

One way run the query results to text, copy the results to Excel. The data will all be paced in the first column, highlight the column and use the Data option from the menu in Excel and altering the text to columns.... Not great but it does work.

Facts are stubborn things, but statistics are more pliable - Mark Twain
Carolyn
SQLServerSpecialists[/url]

Jeremy-475548 Hall of Fame Points: 3065 More actions · Answer 2

I usually do the following:

Right Click on Query

Query Option | Text

Switch to comma delimited

change results window to text

run query

Right click in results window

save results as csv

Open in Excel

Adam Haines SSC-Insane Points: 23217 More actions · Answer 3

Click Query (Located on the main toolbar) --> Query Options --> Results --> Grid --> Include Column headers....(This should be checked)

Then select all the rows from your result set and copy --> open Excel and paste.

My blog: http://jahaines.blogspot.com

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 4

If you setup Query Analyzer in the Text mode and use Tab's to delimit the data instead of commas, you can copy and paste directly from the Text results window of QA into Excel without all the extra steps.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Adam Haines SSC-Insane Points: 23217 More actions · Answer 5

Jeff,

Ritesh still should have to make sure the column header query option is set wheter it be for text or grid. Ritesh will only have to configure this on the inital run. Any subsesequent queries can be copied/pasted, without changing this setting.

You can copy/paste rows directly from the results grid to Excel, without using tab delimited results to text.

My blog: http://jahaines.blogspot.com

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 6

True enough. Just listing options, Adam...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Remaks SSC Enthusiast Points: 171 More actions · Answer 7

Click Query (Located on the main toolbar) --> Query Options --> Results --> Grid --> Include Column headers....(This should be checked)

Just checking because I'm not entirely sure, but is the option to copy column names in the output an option of Query Analyzer? I'm seeing the option in Management Studio, but not QA. I'm not seeing the Query Options menu under Query on QA at all.

Is there actually a way to set this in QA, or is it just a SSMS setting?

Jeremy-475548 Hall of Fame Points: 3065 More actions · Answer 8

If you're using SQL 2005, then SSMS IS Query analyzer as well... They are not 2 seperate tools like they were in 2000.

Remaks SSC Enthusiast Points: 171 More actions · Answer 9

Sorry, I guess I should have paid attention to the fact that the post is under SQL Server 2005. I've tried to avoid referring to the SSMS query windows as Query Analyzer just to avoid confusion when dealing with folks who use both. :blush:

Ron McCullough SSC Guru Points: 63877 More actions · Answer 10

Use Excel itself to import the data as:

Open EXCEL

In main menu click "Data"

In drop down menu click "Import External Data"

In next drop down menu click "Import Data"

Select data source window appears

In that window select " +New SQL Server Connection.odc"

Click "Open" command button

Complete the Data Connection Wizard" (Server name and log on credentals)

In next window of Wizard select the database you wish to retrieve data from.

Clear the Select to a specific table check box

Click Next - input description Click Next

Select a table name - click OK

In next window click on Edit Query

In next window click on Command Type

SELECT SQL

In command text window replace table name with the name of the stored procedure that will retrieve the data.

"Pubs"."dbo"."test_exel"

Click OK

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]