SQL Server Performance

[Solved ]Sqlcmd Utility - Exporting to .xls format

Discussion in 'General Developer Questions' started by Dale L, Jul 17, 2007.

  1. Dale L New Member


    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)
  2. khtan New Member

  3. MohammedU New Member


    Microsoft SQL Server MVP

    All postings are provided “AS IS” with no warranties for accuracy.
  4. Dale L New Member

    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.
  5. ndinakar Member

  6. Dale L New Member

    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.
  7. Madhivanan Moderator

    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


    Failing to plan is Planning to fail
  8. Dale L New Member

    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.

Share This Page