Export data from SQL Server query to excel

Question

I want to export data from a query to excel file. I know that there is a lot of questions like these one here, but no one is acceptable in my situation.

For example, like this topic using OPENROWSET: T-SQL: Export to new Excel file

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
'Excel 8.0;Database=D:\testing.xls;', 
'SELECT * FROM [SheetName$]') select * from SQLServerTable

It only execute successfully when I create testing.xls myself, and also define exactly number of columns that will be export from my query in this excel file. Otherwise, an error occur: Column name or number of supplied values does not match table definition.

I also try another solution here: http://weblogs.sqlteam.com/mladenp/archive/2006/07/25/10771.aspx using Exec master..xp_cmdshell

It is really work, but, I heard that xp_cmdshell is a big security threat for SQL Server. So, may be I shouldn't use it.

Is there any other way I can try?

I want a TSQL solution to execute it in my store procedured, so these solution isn't match my request. — user2500561, Jul 31 '13 at 16:45
Check if the answers in this question will work for you: http://stackoverflow.com/questions/15896765/automate-using-sql-express-to-export-to-excel-not-csv/16027210#16027210 — Lan, Jul 31 '13 at 22:38

OzrenTkalcecKrznaric · Answer 1 · 2013-07-31T05:26:52.907

If you're looking for a quick and dirty solution, you can use Management Studio itself.

Here are the steps:

write your query and run it
right click the Results pane and select "Save Results As..."
select your folder/filename and ensure CSV type is selected below
now open your CSV file using Excel; if appropriate save it in Excel as a native Excel format

That won't help you if you need programmatic solution, in which case you have to use Microsoft (Microsoft.Office.Interop.Excel) or 3rd party solutions; you can even build one using Office Open XML.

If you need TSQL solution, OPENROWSET which you are mentioning in the question should be fine.

You can use Excel-only solution and import data using Data pane in the Excel itself (Import from SQL server).

Another possibility taht doesn't have to be coupled to SQL Server or Excel itself is Powershell. However, I'm not into Powershell so if you prefer this method you will have to investigate a bit more.

Also, this is a bit outdated but interesting read.

I need a TSQL solution, with OPENROWSET, I need to create excel file myself, define columns for it, otherwise I got error: Column name or number of supplied values does not match table definition. — user2500561, Jul 31 '13 at 16:43

score 0 · Answer 2 · edited May 23 '17 at 12:20

Try using a newer version of Excel for this. From your query it looks like you’re using Excel 97 – there are probably a ton of bugs and incompatibilities that exist in this version.

If this doesn’t help other options are: - Do this manually from SSSM like Ozren suggested - Try creating SSIS package for this

Here are couple other threads to get you started

http://www.connectionstrings.com/excel/

How do you transfer or export SQL Server 2005 data to Excel

Export SQL query data to Excel

Export data from SQL Server query to excel

2 Answers2