September 2, 2008 at 7:23 pm
Is there a wizard to export the resultset from a query to an excel file that includes the column names?
September 2, 2008 at 9:17 pm
Did you try to Export Wizard? Right click on the database, select Task, then export data. Walk through the steps. I believe it gives you an option somewhere to add headers.
Hope that helps.
September 3, 2008 at 12:22 am
I don't see an option to export to excel. 🙁
September 3, 2008 at 6:54 am
The 2nd menu item that comes up is called "Choose a Destination". The very first drop down menu is for destination. Select Microsoft Excel.
September 3, 2008 at 7:42 am
I right click on the database and select "Tasks" then "Export Data..."
There's not a place in the wizard that chooses destination.
sql server 2005
MS SQL Server Management Studio
????
September 3, 2008 at 7:52 am
What do you see when you click on export tasks?
September 3, 2008 at 8:20 am
maryjane
Are you using SQL Express? If you are the access to the Export Wizard is not available.
If you are using another version then
1. Right Click on database name
2. In the pop-up menu click on "Tasks"
3. In the next pop-up menu 2nd line from the bottom click "Export Data"
4. In window "Chose a Destination"
4a Select "Excel"
4b. Be sure to click on the check box "First Row has column names"
5. Just continue to the bitter end of the wizards questions.
September 3, 2008 at 8:41 am
under Tasks I see:
Detach
Take Offline
Shrink
Backup...
Restore
Mirror...
Ship Transaction Logs...
Generate Scripts...
Import Data...
Export Data...
Copy Database...
the Export Data opens the wizard
September 3, 2008 at 9:04 am
That is how you open the export wizard. I was wondering what you saw when you opened the wizard? If you have never opened it before, you will probably see a welcome screen. The next screen should be a "Choose Data Source". The screen after that is "Choose Data Destination".
Is this what you see or do you see something different?
September 3, 2008 at 9:21 am
I don't see "Choose Data Destination" after "Choose Data Source".
I have the option to choose one of the following:
"Copy data from one or moe tables or views"
"Write query to specify the data to transfer"
"Copy data from one or moe tables or views" is selected by default.
The I select the table I want.
Then I export.
It copies the records and adds it to the same table.
September 3, 2008 at 9:41 am
The page you are requesting should be between those two pages. I do not know why it isn't. However, with that not working, my next best suggestion would be to use either BCP to create a CSV flat file that you can import into excel, or to create an SSIS package to do it.
If you don't have any experience with SSIS, I would recomment BCP. The book online tutorial gives a good explanation of how it works.
September 3, 2008 at 9:44 am
Maryjane in the "Chose Data Source" window what do you select. For example if you chose "SQL Server Native Client 10.0", the next window will have a command bar labeled "Properties" do you click on it?
September 4, 2008 at 7:45 am
if you can put up with having to open excel manually, you can simply paste the result + headers...make sure you have the option to copy result headers switched on.
From a query window, select the Query Menu,->Query Options
and under "Grid" make sure "Include column headers when copying or saving the results" is checked.
Maybe you are looking for something more automated, but I find this is a useful technique for many situations
September 5, 2008 at 7:03 am
Brian Burke (9/4/2008)
if you can put up with having to open excel manually, you can simply paste the result + headers...make sure you have the option to copy result headers switched on.From a query window, select the Query Menu,->Query Options
and under "Grid" make sure "Include column headers when copying or saving the results" is checked.
Maybe you are looking for something more automated, but I find this is a useful technique for many situations
It doesn't even have to be manual like that - change the Execute Mode in Query Analyzer to 'Results To File', go to your Tools --> Options, under the General tab, change the 'Result file extension' to .xls, and I use Tab Delimited as the 'Results output format' on the Results tab, that part probably doesn't matter so much, Excel will recognize CSV just as well.
Then you just run the query, type in the name of the Excel file and tell it where to save, badabing, headers and data.
By the way, if you have multiple operations happening in your script, you'll want to SET NOCOUNT ON before you run it, otherwise it adds all of the '1800 rows affected' messages at the top of your Excel file.
Hope that helps
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply