[Solved ]Sqlcmd Utility - Exporting to .xls format

Hello.

Is there a good way to extract information from a table in a database to a .xls file?

Is it possible? All i've been able to do was to export all the data to the same first line of each row of the excel file but I would need each column to be in a different column of, not all in one. I dont know if im clear.

Are there better way? (By better way i mean something that can be easily automated)

seehttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926


KH

http://mssqltips.com/tip.asp?tip=1202


MohammedU.
Microsoft SQL Server MVP
Moderator
SQL-Server-Performance.com

All postings are provided “AS IS” with no warranties for accuracy.

Somebody has a solution that does not require openrowset? It has been disabled on our servers for security reason.

I could use bcp since it do exactly what i want it to do but bcp cannot put the column names on the first row.

Ne need to point out nigel solution about that. I saw it and i dont like it. What im looking for is more a way of doing that with sqlcmd,osql or another command and not using bcp.

Check if this helps:http://weblogs.sqlteam.com/mladenp/archive/2006/07/25/10771.aspx
you can remove the column names part or use it if you need it..

***********************
Dinakar Nethi
***********************
http://weblogs.sqlteam.com/dinakar/

I found the solution but first i just saw i posted my problem in sql server 2000 and not 2005 like i wanted to. My mistake, but since the solution i found work as much in sql 2000 with osql and 2005 with sqlcmd, I'll post it anyway.

The solution :

I used the -s attribute of sqlcmd/osql to define the column separators as an horizontal tab and define the output file as a .xls file.

That made it so that when i open the file with excel, the columns are placed exactly the way i want them to (each table column in a different column of the file). It also place the header, which is good but can be easily disabled if needed.



I prefer simpler solution like this one than 2 page of code to do the same.

Yeah it could be usefull.

But if i want the guys that will go over my code later to understand what it does fast, i always think its better to keep it simple.