Export 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 2000 Forums

Transact-SQL (2000)

Export SQL to Excel

Author

Topic

infodemers
Posting Yak Master

183 Posts

Posted - 2010-01-12 : 15:22:57

Hi I am using the following code to export data from SQL to an excel file. I wish to have it order by the first column when ever I open the excel file. How can I do it running this code?

Exec Master..xp_cmdshell 'bcp "SELECT ''CutomerID'',''LastName'',''FirstName'',''date'' UNION ALL (Select [CutomerID], [LastName], [FirstName], convert(varchar,[date]) from Tbl_Memory order by CutomerID)" queryout "\\servername\Reports\Customers.xls" -c'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-13 : 01:21:30

Try this

Exec Master..xp_cmdshell 'bcp "SELECT * FROM (SELECT ''CutomerID'' as [CutomerID],''LastName'' as [LastName],''FirstName'' as [FirstName],''date'' as [date],1 as sort_order UNION ALL Select [CutomerID], [LastName], [FirstName], convert(varchar,[date]),2 as sort_order from Tbl_Memory ) as t order by sort_order,CutomerID" queryout "\\servername\Reports\Customers.xls" -c'

Madhivanan

Failing to plan is Planning to fail

infodemers
Posting Yak Master

183 Posts

Posted - 2010-01-15 : 12:48:35

Hi Madhivanan,

That did the job. I only remove the sort_order column from the select because i do not want to see that column in the results.

Thanks for your help!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-18 : 02:01:39

quote:
Originally posted by infodemers

Hi Madhivanan,

That did the job. I only remove the sort_order column from the select because i do not want to see that column in the results.

Thanks for your help!

Yes it is

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources