Export to Excel

I used bcp to export data to Excel file
master..xp_cmdshell 'bcp "select * from MyDB..Test" queryout
"D:\ex.xls" -c'
It is working. But It is actually stored as Text(Tab Delimitated) type.
I have to open it and do saveas and select "Microsoft Excel Workbook"
to actually save it as Excel file. Is there any way to avoid this?
MadhivananIf you actually want an Excel workbook as output, use a DTS package instead.
bcp is used only for text (or native) output.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1115982106.363043.37730@.o13g2000cwo.googlegroups.com...
I used bcp to export data to Excel file
master..xp_cmdshell 'bcp "select * from MyDB..Test" queryout
"D:\ex.xls" -c'
It is working. But It is actually stored as Text(Tab Delimitated) type.
I have to open it and do saveas and select "Microsoft Excel Workbook"
to actually save it as Excel file. Is there any way to avoid this?
Madhivanan|||Can you tell me how to use DTS? I connected SQL Server as Source and
Excel ODBC as Destination. When I run I get the error 1 task(s) falied
during execution
Madhivanan|||The books online (BOL) discuss DTS. You can also post the question in the
.dts newsgroup. Also, have you tried just using the wizard? This sounds
like a straightforward export.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1115983608.621366.69350@.g14g2000cwa.googlegroups.com...
Can you tell me how to use DTS? I connected SQL Server as Source and
Excel ODBC as Destination. When I run I get the error 1 task(s) falied
during execution
Madhivanan