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)
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.
<< I prefer simpler solution like this one than 2 page of code to do the same. >> It depends on where you want to use it Madhivanan Failing to plan is Planning to fail
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.