Converting Table to Excel with Header of table
This Stored Procedure explains about how we can convert an Sql Server Table to Excel. We can get this excel with headers of this table included in it. The main parameter we should pass to this Stored Procedure is Database name, Table name and finally File name. In which name we should get that output file with extension. It is shown below.
Exporting table to excel with header of table:
The below given stored procedure explains about exporting an excel from our Sql table. We often need such requirement that our tables should be converted to excel and should be used. This given procedure makes our work easier. We should give our database name, table name and the path where our excel should be stored, as parameter in this stored procedure. Then after executing this stored procedure our table will be converted to excel as an output in the path we specified already.
Our Normal BCP queries will be used for exporting an table to excel. But we can't get header of the table after exporting. so to avoid that here we get header from information schema table and merge this with our table result.Stored Procedure:
--EXEC Excel_With_Header 'MyDatabase', 'MyTableName','E:\ExcelName.xls'
CREATE procedure Excel_With_Header
(
@My_dbname varchar(100),
@My_tablename varchar(100),
@My_filename varchar(100)
)
as
--Generate column names as a recordset
declare @My_columns varchar(8000), @sql varchar(8000), @data_fileName varchar(100)
Select @My_columns=coalesce(@My_columns+',','')+column_name
From information_schema.columns
Where table_name=@My_tablename
select @My_columns = '''''' + replace(@My_columns,',',''''',''''') + ''''''
--Create a dummy file to have actual data
select @data_fileName=substring(@My_filename,1,len(@My_filename)-charindex('\',reverse(@My_filename)))+'\data_file.xls'
--Generate column names in the passed EXCEL file
set @sql='exec master..xp_cmdshell ''bcp " select '+@My_columns+' as t" queryout "'+@My_filename+'" -c -T'''
exec(@sql)
--Generate data in the dummy file
set @sql='exec master..xp_cmdshell ''bcp "select * from '+@My_dbname+'..'+@My_tablename+'" queryout "'+@data_fileName+'" -c -T'''
exec(@sql)
--Copy dummy file to passed EXCEL file
set @sql= 'exec master..xp_cmdshell ''type '+@data_fileName+' >> "'+@My_filename+'"'''
exec(@sql)
--Delete dummy file
set @sql= 'exec master..xp_cmdshell ''del '+@data_fileName+''''
exec(@sql)
GO