Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
sql_server 217
t-sql 211
tsql 116
sqlserver 96
BRH 78
#SQLServer 66
#TSQL 56
SQL Server 34
function 11
SSMS 9

Archive · View All
August 2007 17
August 2010 8
June 2012 7
June 2011 7
November 2007 7
August 2012 6
May 2012 6
November 2011 6
August 2011 6
October 2011 6

Madhivanan's TSQL Blog

Export to EXCEL with column names

Oct 10 2008 3:55PM by Madhivanan   

In the post Import/Export to Excel, I showed how to export data to EXCEL
The problem that most users find it is it wont include column names in the file and file should exists already with headings

This procedure would solve that problem
create procedure proc_generate_excel_with_columns
(
        @db_name varchar(100),
        @table_name varchar(100), 
        @file_name varchar(100)
)

as

--Generate column names as a recordset

declare @columns varchar(8000), @sql varchar(8000), @data_file varchar(100) 
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 '),',',',''''')

--Create a dummy file to have actual data
select @data_file=substring(@file_name,1,len(@file_name)-charindex('\',reverse(@file_name)))+'\data_file.xls'

--Generate column names in the passed EXCEL file
set @sql='exec master..xp_cmdshell ''bcp " select * from (select '+@columns+') as t" queryout "'+@file_name+'" -c'''
exec(@sql)

--Generate data in the dummy file
set @sql='exec master..xp_cmdshell ''bcp "select * from '+@db_name+'..'+@table_name+'" queryout "'+@data_file+'" -c'''
exec(@sql)

--Copy dummy file to passed EXCEL file
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)

After creating the procedure, execute it by supplying database name, table name and file path

EXEC proc_generate_excel_with_columns 'your dbname', 'your table name','your file path'

For more informations and to know how to solve the errors, refer this thread Export to Excel

Tags: t-sql, sql_server, export_to_excel,


Madhivanan
3 · 40% · 12947
2
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

"Export to EXCEL with column names " rated 5 out of 5 by 2 readers
Export to EXCEL with column names , 5.0 out of 5 based on 2 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]