export 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

Transact-SQL (2008)

export to excel

Author

Topic

fan2005
Yak Posting Veteran

85 Posts

Posted - 2011-01-31 : 17:19:20

hi i try to export a select result in excel 2007 in sql server 2008
but the file is not created properly

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'

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-31 : 18:25:39

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/10/10/export-to-excel-with-column-names.aspx

Looks like you are using Madhivan's code.

Is there an error message?

Poor planning on your part does not constitute an emergency on my part.

fan2005
Yak Posting Veteran

85 Posts

Posted - 2011-02-01 : 00:57:18

Password:
SQLState = 37000, NativeError = 7202
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Could not find server 'centers' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.
SQLState = 37000, NativeError = 8180
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.
NULL

then I tryed

exec sp_addlinkedserver 'centers'

and then

use master
EXEC master.dbo.proc_generate_excel_with_columns
 'centers', 'dbo.a10','d:\my.xls'

but it still has error

Password:
SQLState = 37000, NativeError = 7202
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Could not find server 'centers' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.
SQLState = 37000, NativeError = 8180
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.
NULL

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-01 : 05:43:59

That error is pretty clear...

Poor planning on your part does not constitute an emergency on my part.

fan2005
Yak Posting Veteran

85 Posts

Posted - 2011-02-02 : 07:55:04

IT IS SOLVED

LOGGING IN AS WINDOWS AUTHENTICATION
------------------------------------

USE [center]
GO
/****** Object:  StoredProcedure [dbo].[proc_generate_excel_with_columns]    Script Date: 02/02/2011 17:22:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 

ALTER procedure [dbo].[proc_generate_excel_with_columns]
(       @SERVERNAME VARCHAR(100),  
        @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 -S'+@SERVERNAME+' -T '''

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 -S'+@SERVERNAME+' -T '''
PRINT @sql
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)

fan2005
Yak Posting Veteran

85 Posts

Posted - 2011-02-02 : 09:04:45

HI
IF I WANT TO HAVE HEADERS IN EXPORTED FILE WHAT SHOULD I DO?
I alse want to export different select out-put into different sheets.
THANKS

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-02-02 : 09:26:45

Refer method 5
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Madhivanan

Failing to plan is Planning to fail

fan2005
Yak Posting Veteran

85 Posts

Posted - 2011-02-02 : 09:54:09

I'm already using solution 5
but there is no headers

for number 4 see this
http://msdn.microsoft.com/en-us/library/ms180099%28v=sql.90%29.aspx

fan2005
Yak Posting Veteran

85 Posts

Posted - 2011-02-02 : 12:23:19

if you pass @table_name with owner like 'dbo.tablename' @columns would be null



select @columns=''''''+replace(replace(@columns,' as ',''''' as '),',',',''''')


select 
        @columns=coalesce(@columns+',','')+column_name+' as '+column_name
from 
        information_schema.columns
where 
        table_name=@table_name

then again if you pass @table_name without owner
would be null

set @sql='exec master..xp_cmdshell ''bcp "select * from '+'['+@db_name+']'+'.'+@owner+'.'+@table_name+'" queryout "'+@data_file+'" -c -S'+@SERVERNAME+' -T '''
exec(@sql)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-02-03 : 02:59:14

See what pring @sql return?

Madhivanan

Failing to plan is Planning to fail

fan2005
Yak Posting Veteran

85 Posts

Posted - 2011-02-03 : 09:32:12

quote:
Originally posted by madhivanan

See what pring @sql return?

Madhivanan

Failing to plan is Planning to fail

1.I fixed it with adding @owner
2.but still if the table(##tablename) is in tempdb @columns would be null. So no headers will be in excel file.
3.I also want to insert data in different sheets. but i don't know how its possible using this SP.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources