February 14, 2007 at 1:14 pm
Is it possible to execute SQL from Query Analyzer and have the results go to an Excel (.xls) file? I found how to do this using JDBC but it fails with a generic message. I was wondering if there is a way to do it with ODBC. I am pretty sure I can do it with DTS but that would require me to implement on every instance. I want to be able to connect to each instance from my workstation, run the SQL, and have it go to a .xls file.
February 14, 2007 at 1:43 pm
You can get it into a CSV file (which you can then read into Excel) using BCP - for example:
c:\> bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout Authors.txt -c -Sservername -T
(have a look at bcp output or in BOL for more options).
Hope this helps,
- James
--
James Moore
Red Gate Software Ltd
February 14, 2007 at 2:36 pm
Do you want to automate this if so then create a dts pacakge that will allow for this? If doing it from the query analyzer simply output your SQL in text format (results in text) - CTRL - A and then copy the data into Excel. This will include the column names as well
Hope this helps!
February 15, 2007 at 6:19 am
Hi,
You can use sp_makewebtask too. Instead of using it for HTML files you can use for XLS files.
See a simple example below: (You got more explanation on BOL too)
EXECUTE MASTER..SP_MAKEWEBTASK
@OutputFile = 'C:\Report.xls',
@Query = 'Select top 1 * from master..sysobjects',
@HTMLheader = 2, -- Size of the font
@CodePage = 65001, -- Type of font
@ResultsTitle = 'Just a Test Report' -- Header for the Excel File in the sheet.
Theres another way to import SQL Data to Excel files that is using OPENDATASOURCE. I really dont recommend it, it wont works properly everytime, but its worth a try just to see how it works.
'INSERT OPENDATASOURCE('MICROSOFT.JET.OLEDB.4.0, DATA SOURCE ='C:\Report.xls';USER ID=ADMIN;PASSWORD=;EXTENDED PROPERTIES="EXCEL 8.0;HDR=NO;"'')...Sheet1$ --
In the case above you MUST have the Report.xls already created on that share(C:\) with all the fields specified and created. Just like as a template. MS SQL will use that template and fill the fields with data in each collumn that you specify.
Any prob, you can send me an email: dantenor@gmail.com
February 15, 2007 at 7:11 am
Quick and dirty solution:
Select "Result to text" (Ctrl+T)
Select the menu Tools|Options.. then select the Result tab and select in the "Results output format" to "Tab delimited"
That's it. No you can copy the result and past in the Excel sheet.
Bye
Gabor
February 15, 2007 at 8:54 am
Go back to the Tools|Options menu in Query Analyzer and get a little more creative. Change "Result file extension:" to .xls on the General tab, then go to the Results tab and choose Results to File and Tab delimited. Now when you run a query a file dialog will pop up asking where you want to put the results, and Excel will be happy to open it.
You can also use the Save command when the cursor is in the results pane. If you save a results grid it creates a CSV file that Excel will open, but there are no column headers. If it is in text mode and you set up Tab delimited results (and changed the result file extension to .xls) it will create a file (with column headers) that works in Excel.
October 15, 2009 at 5:35 pm
I've created an SSMS 2005/2008 Addin that allows saving SQL query results as an XLS file, with header and value formats intact. You don't have to mess around with copying and pasting to import CSV.
Cheers,
David
SsmsXlsExport.com
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply