Export to Excel but too many rows

Question

Export to Excel but too many rows

bruce.b.allen

SSC-Addicted

Points: 438
More actions
October 25, 2010 at 3:24 pm

#388097

I'm just learning SSIS and I have a problem that I'm not sure how to come up with the best solution. So here it goes.
I am querying an Oracle database and then will be placing the records in to an excel destination. The problem I run into is that there is just over 67,000 rows so I go over the limit (Excel 2003).
I'm curious what the experts on this forum would do to split the records and then put them into Excel.
I can probably figur out the code/process flow within SSIS, I was just looking for some broad ideas on how to do this.
Is there a way to split them with a query, or would I want to use some sort of script task for this? Perhaps the for each containers?
Any thoughts would be very helpful.
Thanks in advance.

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

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

Derrick Smith SSChampion Points: 10833 More actions · Answer 1

Export to flat files...I hate dealing with Excel and would always recommend against it unless there is no other way. If you install excel 2007/2010 you can export more than the 67,000 rows though.

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 2

I agree with Derrick that flat files would be much easier.

If you have to use Excel, and you have a row number for every row of data going through the dataflow, then you could use the conditional split to direct rows to different Excel sheets based on the row number.

Inserting a row number is not row number is fairly easy to do, should you require it.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

bruce.b.allen SSC-Addicted Points: 438 More actions · Answer 3

Thanks for the suggestions. I'll look at the conditional split and see if I can get that to work. I agree with both of you on the exporting to flat files. Unfortunately it's not my choice...people around here love Excel.

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 4

The more ETL work I do, the more I hate Excel.

Excel is a great product. Just keep it away from ETL.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Derrick Smith SSChampion Points: 10833 More actions · Answer 5

Alvin Ramard (10/25/2010)
The more ETL work I do, the more I hate Excel.
Excel is a great product. Just keep it away from ETL.

Agreed...and the whole lack of a 64bit jetdb driver is ridiculous. Not sure what they were thinking when they stopped that. Rumor has it it's coming back now in the next version of office.

WayneS SSC Guru Points: 95461 More actions · Answer 6

Derrick Smith (10/25/2010)
Alvin Ramard (10/25/2010)
The more ETL work I do, the more I hate Excel.
Excel is a great product. Just keep it away from ETL.
Agreed...and the whole lack of a 64bit jetdb driver is ridiculous. Not sure what they were thinking when they stopped that. Rumor has it it's coming back now in the next version of office.

The 64-bit ACE drivers are available here.

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

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 7

Alvin Ramard (10/25/2010)
The more ETL work I do, the more I hate Excel.
Excel is a great product. Just keep it away from ETL.

Agreed. For some reason most (business) people tend to forget that Excel is a spreadsheet application, not a way to transfer data or EDI messages.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Phil Parkin SSC Guru Points: 247180 More actions · Answer 8

It's because they don't know how to use Access and Notepad is ungainly to use - understandable, I think. But we as IT people are in a position to educate.

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 9

Phil Parkin (10/26/2010)
It's because they don't know how to use Access and Notepad is ungainly to use - understandable, I think. But we as IT people are in a position to educate.

Phil you reminded me of something that happened a while back. A contact from a fairly big company asked if I could provide data in a Notepad format. :w00t:

I didn't bother trying to explain that one. I just said yes.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

bruce.b.allen SSC-Addicted Points: 438 More actions · Answer 10

Alvin Ramard (10/25/2010)
I agree with Derrick that flat files would be much easier.
If you have to use Excel, and you have a row number for every row of data going through the dataflow, then you could use the conditional split to direct rows to different Excel sheets based on the row number.
Inserting a row number is not row number is fairly easy to do, should you require it.

Alvin, thanks for the tips. I created the row count in my query and added the conditional split. Since I'm barely over the 65000ish mark I can do this with 2 files (rather than dynamically creating depending on record count) and won't have to worry about the consequences for at least 10 years...haha...plus my company may decide to upgrade to Office 2010 in 10 years and then I can get rid of the conditional split anyhoo.

This was my first post and I'm definitely impressed. Thanks all for the suggestions.

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 11

bruce.b.allen (10/26/2010)
Alvin Ramard (10/25/2010)
I agree with Derrick that flat files would be much easier.
If you have to use Excel, and you have a row number for every row of data going through the dataflow, then you could use the conditional split to direct rows to different Excel sheets based on the row number.
Inserting a row number is not row number is fairly easy to do, should you require it.
Alvin, thanks for the tips. I created the row count in my query and added the conditional split. Since I'm barely over the 65000ish mark I can do this with 2 files (rather than dynamically creating depending on record count) and won't have to worry about the consequences for at least 10 years...haha...plus my company may decide to upgrade to Office 2010 in 10 years and then I can get rid of the conditional split anyhoo.
This was my first post and I'm definitely impressed. Thanks all for the suggestions.

You're welcome and I'm happy it worked. I haven't actually use that method, but I could see why it wouldn't work.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]