Post reply

Exporting Table to multiple excel sheets based on a field in a table

  • June 14, 2009 at 12:38 pm

    #201397

    Hi,

    New to SSIS. We have a need to read in an excel spreadsheet that has been altered by someone. I can see, even without knowing exactly how to do it, that I can read a single excel sheet into a table through SSIS; I'll figure that one out. My problem is that I need to take the table and export it back to excel, breaking it out into multiple sheets based on the values of one field in the table.

    I realize I can write an SSRS report to read the table with page breaks on the change of the value in a group using this same field that I want to divide into seperate sheets; exporting that to excel would create the different sheets. But I'd like ot make this a "one-click" affair, allowing someone to place the spreasheet in a certain place, click a button (simple .net application?), and have it saved out to a new spreasheet with multiple sheets.

    Can I do this from in side SSIS once I get the excel spreadsheet read into a table?

    Thanks,

    Fig

  • June 14, 2009 at 3:59 pm

    #1009403

    I'm (almost) copying one of the replies from today, so it seems like it's "Excel-day" 🙂

    I've just seen another thread (http://www.sqlservercentral.com/Forums/FindPost734494.aspx) that included the following link: http://www.simple-talk.com/sql/t-sql-programming/sql-server-excel-workbench/ .

    This link might help you to get your data from and to Excel.

    It's using T-SQL but I think that's something that can be handled...


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • June 14, 2009 at 7:07 pm

    #1009425

    This article http://www.sqlservercentral.com/articles/Integration+Services/61542/ dynamically creates tabs in an excel spreadsheet and exports data (from a select statement) into it. It is designed to run from within SSIS. Note that I found the screen shots extremely difficult to work with.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

Viewing 3 posts - 1 through 3 (of 3 total)

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