Thursday, July 4, 2013

Export to excel with column headings using SQL Server

Procedure to export data into excel with columns

create procedure sp_generate_excel_with_columns
@server_name varchar(100),
@db_name varchar(255),
@table_name varchar(100),
@file_name varchar(100)
declare @del_command varchar(200)
declare @file_exists INT
declare @file_path varchar(200)
select @file_path = substring(@file_name,1,len(@file_name)-charindex('\',reverse(@file_name)))
--select @file_path
EXEC xp_fileexist @file_name, @File_Exists OUT
If @file_exists = 1
set @del_command = 'exec master..xp_cmdshell ''del ' +  @file_name +  ''''
declare @columns varchar(8000), @sql varchar(8000), @data_file varchar(200)
select @columns=coalesce(@columns+',','')+column_name+' as '+column_name
from  information_schema.columns  where  table_name =@table_name
select @columns=''''''+replace(replace(@columns,' as ',''''' as '),',',',''''')
--print @columns
--Dummy file to storate actual data from table
select @data_file=substring(@file_name,1,len(@file_name)-charindex('\',reverse(@file_name)))+'\data_file.xls'
--Create EXCEL file with column names
set @sql='exec master..xp_cmdshell ''bcp "select * from (select '+@columns +') as t" queryout "'+ @file_name + '" -S' + @server_name + ' -d' +  @db_name +' -T -c'''
exec (@sql)
--Populate dummy file with data
set @sql='exec master..xp_cmdshell ''bcp "select * from '+@db_name+'..'+@table_name+'" queryout "'+@data_file+'" -S' + @server_name + ' -d' +  @db_name +' -T -c'''
exec (@sql)
--Contact dummy file with data to original file with columns
set @sql= 'exec master..xp_cmdshell ''type '+@data_file+' >> "'+@file_name+'"'''
exec (@sql)
--Delete dummy file
set @sql= 'exec master..xp_cmdshell ''del '+@data_file+''''
exec (@sql)

---- Execute Syntax
exec sp_generate_excel_with_columns @@servername,'Database_Name','Table_Name','c:\Temp\File_Name.xls'

No comments: