Post reply

exporting a table into excel sheet

  • May 17, 2007 at 7:23 am

    #176661

    Hi guys,

    can you please tell me how to export a table data into a dynamically created excel sheet.

  • May 17, 2007 at 7:55 am

    #707141

    Hello,

    While executing the data query itself, you can export the data to a file as follows:

    Click on Tools -> Options

    Select Results tab and then select the option Results to File in "Default results target" drop down and the format option as Tab delimited.

    Click on Ok

    Now you run the query to fetch the data and SQL Server automatically prompts you to give the file name.

    You are done.

    Hope this helps.

    Thanks

     

     

     


    Lucky

  • May 17, 2007 at 8:07 am

    #707144

    Thanks Lucky, but the thing is the particular table is comsuming very much disk space. what i'm planning to do is export it to an excel (once in three days or something like that) for back-up and delete the data from teh table. i wnat to automate it.

    since i'm new to DBA activity, donno how to do it.

    Please help me in this regard.........

  • May 17, 2007 at 1:34 pm

    #707248

    Sounds like you're in need of an archive solution.  Backup your Database and Log files and do a weekly deletion maybe.

    If Excel is the route you have to take you'll need an Active-X task inside a DTS that you can then schedule to run Daily or Weekly.

    Regards,
    Matt

  • May 17, 2007 at 1:39 pm

    #707249

    Keep in mind Excel has a 64k row limit.

  • May 17, 2007 at 6:12 pm

    #707279

    You don't need to archive as an "Excel" file... just export it as a Tab delimited file and reimport if you need it.

    If you don't have SA privs, BCP would be the best way...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • May 17, 2007 at 11:32 pm

    #707305

    thanks matt,

    your solution really helped me a lot...

  • May 20, 2007 at 6:36 am

    #707678

          go to excel >> data >> import external data >> new database query setup the database you want to access from there you can select the tables or stored procedures you want to export to excel.

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

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