Export to Multiple Excel Sheets

I saw a post with this same subject line, posted in July of 2006, but with no replies. I am now having precisely the same problem.

I am importing data from an OLE DB source. I want to directly store this data in an Excel file. There are far more than 65,536 rows in the DB table, but the version of Excel I have only tolerates a maximum of that many. My solution is to divide the data into separate worksheets within the same Excel file. At any given time, I do not know exactly how many rows are moving from the database to the Excel file, so is there a way to dynamically create a new worksheet every time I reach 65,536 rows?

Thank you.

Hi Allen,

there is no an easy way to do this.

I can give you a couple of not-so-easy options though:

1. Build a script or custom destination that will know how to split data, create and load sheets. You will need to talk to the OLE DB JET provider directly.

2. Do it in two passes:

- First pass; partiotion data by adding a new column to identify divided partitions and stage the new data in a table or flat file.

- Second pass; Build a package to find out how many partitions there is, create the destination sheets, and run a loop with number of iterations equal to number of partitions created in the previous step. In each iteration, run a child package that can filter data for its partition ans load it into an appropriate Excel sheet.

I am sure there are additional approaches to implement this. It depends on restrictions you are bound with: are you willing to write code, can you stage or preprocess data, etc.

Thanks.

|||Actually I've already gotten the data divided up through a script. Currently, I have 65,000 rows being sent to one Excel file, and all the excess rows offset to a temporary flat file. Then, I reiterate and draw another 65,000 lines out of that flat file and store it into a new Excel file. So basically I have everything set up, and I have multiple Excel file outputs, but I would prefer to combine all of this into a single Excel file with multiple worksheets. Is there an easy way to do this?

Thank you for the help.
|||

That should not be too hard: instead of generating new Excel files, per iteration, create sheets in the existing file. Use Execute SQL Task to create those sheets (DDL example: create table `<sheet_name>` (`<col1> VarChar[50], ...) ).

Let me know if you need more assistance.

|||

Hi Allen,

Myself facing similar problem wants to know how you achieved this task. Had you been successful achieving this task. Is it possible to post me lines of code or atleast provide the url which has:

1) Code that estimates the size of the data and divide the data based on size

2) Code that talks to Jet Provider for Excel directly to create and populate the worksheets using loop.

Many thanks

Subhash Subramanyam

|||It took me a few days of playing around in SSIS before I modified the process for my personal situation, but most of the methodology I got from here:
http://blogs.conchango.com/jamiethomson/archive/2005/12/04/SSIS-Nugget_3A00_-Splitting-a-file-into-multiple-files.aspx

I have no idea what Jet Provider is. Sorry I'm still very new to data manipulation.

If you have any other specific questions about how I modified the process for my own situation, feel free to ask after checking out that blog entry.