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

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

  3. MohammedU New Member

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

    Madhivanan

    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