SQL 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 2005 Forums

Transact-SQL (2005)

SQL to Excel

Author

Topic

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2009-06-09 : 19:08:27

Hello gurus,

I am looking for a "working" sample code that extracts data from SQL Server(2005) and populates excel spreadsheet.

Many thanks in advance

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-06-09 : 19:12:16

Have you tried import/export wizard, SSIS, or bcp.exe? Or are you looking for a T-SQL query?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2009-06-09 : 19:25:20

Hi Tara,

Thanks for prompt response.

Yes, I have tried import/export.

However, I would like to use T-SQL query because I am going to be doing a lot of calculations with the query before exporting to excel.

Thanks again

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2009-06-09 : 20:58:28

Ok, I have been able to find something to gives me what I am after, in terms of inserting into excel by selecting from sql server.

What I am missing now is how to avoid having to having to create column names first.

In other words, I would like to have the column header be created same time the spreadsheet is being populated with data.

Any ideas?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-06-10 : 02:21:39

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

Madhivanan

Failing to plan is Planning to fail

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2009-06-10 : 08:10:09

Thanks very much for that referral.

I saw that but got confused by the syntax.

How does it allow me to use aggregate functions such as:

select (first + second + third + fourth) as total from my table, etc ?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-06-10 : 08:31:32

In the above link where it specifies

--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)

Change that to

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

Madhivanan

Failing to plan is Planning to fail

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2009-06-10 : 09:49:24

Ok, please help me one more time.

Here is the code I am trying to fit into your solution:

select * from cte union all
select 'Total',sum_Networks,sum_DBAdmins,sum_NetProgrammers,sum_UnixAdmin,sum_Total from cte2 union all
select 'Average',avg_Networks,avg_DBAdmins,avg_NetProgrammers,avg_UnixAdmin,avg_Total from cte2 union all
select 'Min',min_Networks,min_DBAdmins,min_NetProgrammers,min_UnixAdmin,min_Total from cte2 union all
select 'Max',max_Networks,max_DBAdmins,max_NetProgrammers,max_UnixAdmin,max_Total from cte2 union all
select '%',sum_Networks*100/sum_Total,sum_DBAdmins*100/sum_Total,sum_NetProgrammers*100/sum_Total,sum_UnixAdmin*100/sum_Total,100 from cte2

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2009-06-10 : 11:59:51

Hi Madhivanan,

I have a different question. Please ignore the last one if it is too difficult.

When tried the execute the stored proc with this code:

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

I am getting an error that username not provided.

How should I enter username/password and where?

Everything I have tried so far is not working.

Thanks for all your help.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-06-12 : 01:53:14

Can you post the exact error message?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources