Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
I have to export data from SQL server 2005 to excel with header as table header,
I have tried this
INSERT INTO OPENROWSET ('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\Documents and Settings\administrator.Ser\Desktop\Report.xls;',
'SELECT * FROM [DATA$]')
select * from Test1
Posted 31-Jul-12 20:40pm
Comments
Pandvi at 1-Aug-12 1:56am
   
For exporting SQL to Excel, you can use a Dataset in your application; and then fill the Dataset with your SQL Query Result; finally you can build your excel workbook with data in the Data set. As for the header and table attributes, I am sorry that I can't help.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Alter PROCEDURE asp_Classtest
AS                   
BEGIN   
 
--creating new copy of CSV every time 
EXEC xp_cmdshell 'del H:\CSVTest\asp_ClassReport.csv'    
 
EXEC xp_cmdshell 'copy H:\CSVTest\asp_ClassReportModel.csv h:\CSVTest\asp_ClassReport.csv'
 

INSERT INTO OPENROWSET ('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\Documents and Settings\administrator.Ser\Desktop\Report.xls;',
'SELECT * FROM [DATA$]')
select * from Test1
 

END
 
with the above approach, if you know the columns already, then you can create header in the model.csv file.
  Permalink  
Comments
RockingDev at 1-Aug-12 3:18am
   
Thanks santosh but I need to directly export data to Excel without using any other file format
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

To export data to new EXCEL file with heading(column names), create the following procedure
 

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'
 
SOurce:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926[^]
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 7,800
1 Sergey Alexandrovich Kryukov 7,072
2 DamithSL 5,604
3 Manas Bhardwaj 4,986
4 Maciej Los 4,790


Advertise | Privacy | Mobile
Web03 | 2.8.1411023.1 | Last Updated 1 Aug 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100