Sql Server to Excel - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

Sql Server to Excel

Author

Topic

dkverma87
Starting Member

1 Post

Posted - 2011-06-24 : 02:13:17

hi all,

I have used the below written query to export data to a excel file..

USE MYDATABASE
INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=d:\SqlServer2Excel.xls;','Select * from [sheet1$]')
SELECT * FROM TableA

:) It works fine.

But, to do so, I need to create a Excel file and then specify header(column name) according to the table (from which I am exporting data).

Now, I want to do it from SQL Query (automatically). Can someone know how will it happen ?

Any reply would be highly appreciated..... Thanks in advance :)

Deepak

latch
Yak Posting Veteran

62 Posts

Posted - 2011-06-27 : 09:20:40

1. Make use of the option HDR and assign YES or NO depending upon your requirement it works:

eg..,
SELECT * FROM OPENROWSET( 'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\testing.xls;HDR=NO','SELECT * FROM [Sheet1$]')

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/openrowset-and-excel-problems

or else try these:

http://searchsqlserver.techtarget.com/tip/Write-query-results-to-Excel-using-a-linked-server-and-T-SQL?bucket=ETA

In order to achieve the export, you must first create an empty Excel file with a fixed name and place on the server. I called it Empty.xls and placed it in the c:\temp directory. This file is not to be deleted and acts like a template for the target Excel file before it is populated by data.

The Empty.xls file is built with a single spread sheet called ExcelTable and the first (and only) line in the file consists of the letters A,B,C,...Z. These letters act like the column names of the Excel table. That means that up to 26 columns can be exported in a single query. (The given stored procedure code can be altered to support more columns in the result set. Simply write F1, F2 ,F3... in the Excel template and change the column list in the procedure to reflect the change.)

The T-SQL stored procedure, sp_write2Excel, gets the target Excel file name and path, the number of columns in the result set and the T-SQL query. The query should use the convert function

SHOW ME MORE

More on SQL-Transact SQL (T-SQL)
Get help from the community
Powered by ITKnowledgeExchange.com
for every non-string exported column since the resulting Excel cells are actually strings.

The procedure copies the empty.xls template file to the new target Excel file. Then it builds a linked server to that file and uses dynamic T-SQL to construct and Insert/Select statements that write the data to the Excel file.

Here is the procedure code:

Create proc sp_write2Excel (@fileName varchar(100),
@NumOfColumns tinyint,
@query varchar(200))
as
begin
declare @dosStmt varchar(200)
declare @tsqlStmt varchar(500)
declare @colList varchar(200)
declare @charInd tinyint

set nocount on

-- construct the columnList A,B,C ...
-- until Num Of columns is reached.

set @charInd=0
set @colList = 'A'
while @charInd < @NumOfColumns - 1
begin
set @charInd = @charInd + 1
set @colList = @colList + ',' + char(65 + @charInd)
end

-- Create an Empty Excel file as the target file name by copying the template Empty excel File
set @dosStmt = ' copy c:\temp\empty.xls ' + @fileName
exec master..xp_cmdshell @dosStmt

-- Create a "temporary" linked server to that file in order to "Export" Data
EXEC sp_addlinkedserver 'ExcelSource',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
@fileName,
NULL,
'Excel 5.0'

-- construct a T-SQL statement that will actually export the query results
-- to the Table in the target linked server
set @tsqlStmt = 'Insert ExcelSource...[ExcelTable$] ' + ' ( ' + @colList + ' ) '+ @query

print @tsqlStmt

-- execute dynamically the TSQL statement
exec (@tsqlStmt)

-- drop the linked server
EXEC sp_dropserver 'ExcelSource'
set nocount off
end
GO
Example for procedure usage:

Use master
go
exec sp_write2Excel
-- Target Excel file
'c:\temp\NorthProducts.xls' ,

-- Number of columns in result
3,

-- The query to be exported
'select convert(varchar(10),ProductId),
ProductName,
Convert (varchar(20),UnitPrice) from Northwind..Products'
In conclusion, the procedure can be used as a general tool for exporting data to an Excel spreadsheet since the bcp utility can only export to a text file.

The usage of this procedure can substitute the usage of DTS package designer or the DTS wizard in order to export data to excel each time such an action is required.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources