Post reply

Spool to Excel from SQL Server 2005 - help!

  • December 26, 2006 at 2:08 pm

    #115544

    Hi

    I'm using SQL Server 2005 and was wondering if there is a command so I can spool out a table into Excel 2003 (without using the results to file option)

    The issue is that I have a large number of tables which I need exported to Excel so am trying to look for a simple script to do this, which can be incorporated into a SP.

    Also, I connect via Windows Authentication (so there is no sa or pwd) and I would like the Excel file to be created (if possible... if not I can create them and then populate with the table data)

    Many thanks! and Merry Christmas

  • December 26, 2006 at 3:01 pm

    #680018

    Did you try to call DB from Excel using External Query?

    _____________
    Code for TallyGenerator

  • December 26, 2006 at 3:22 pm

    #680022

    Hi Sergiy,

    I want everything to be done through SQL and result in an Excel file.  Not from Excel connecting to SQL Server.

    Thanks

  • December 26, 2006 at 5:16 pm

    #680033

    Do you need Excel speadsheet or .csv file will be enough?

    _____________
    Code for TallyGenerator

  • December 27, 2006 at 2:12 am

    #680050

    Excel Spreadsheet, as this is the best way to present the data to the client.

    Thanks

  • December 27, 2006 at 6:43 am

    #680067

    This sounds like a perfect time to use a DTS Package.  Set up one DataSource (Your SQL Server) and one Destination (Your XLS file).

    Set up a datapump task using either a Select statement or a Stored Proc for each Table/Select/Stored Proc you want to place into the spreadsheet.

    This will allow you to start all of your exports at one time and send each to a different sheet within your XLS file.

    You'll then be able to schedule this to run or run it manually and you can even set up automatic emails to your end user and attach the XLS file.

    Regards,
    Matt

  • December 27, 2006 at 8:22 am

    #680095

    "This sounds like a perfect time to use a DTS Package"
     
    If you are using SQL2005, you can use the Import/Export wizard in SSMS. Right click on a database, choose Tasks and near the bottom of the menu is the Export Data option. This will start the wizard. Define your source as the database that contains the data you want to export into Excel and the destination as the Excel workbook that will receive the data. Folllow the prompts from there.

    MG

    "There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
    Tony Hoare

    "If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.

  • December 29, 2006 at 9:12 am

    #680527

    If it helps, you can setup an excel file as a linked server. From there you read and write to it, as though it were a table on a linked sql server. The file would need to exist though.

    SQL guy and Houston Magician

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply