Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
psdp
Starting Member
3 Posts |
 Posted - 2007-08-29 : 06:31:35
|
| Hi,I am trying to import data from a table with huge volume (more than 70000 records) to a Excel file. THe problem, because of the limitation on the number of rows (which is somewhere around 65000), I am unable to do a full import..Can you suggest a method thru which additional rows (i.e. rows beyond the limit on excel) can be ported to the next sheet in the same workbook? thanks in advance |
|
|
mwjdavidson
Aged Yak Warrior
735 Posts |
Posted - 2007-08-29 : 09:50:00
|
This is going to be a fair amount of work, especially if you're not that experienced with DTS. So firstly, I'd ask if this data really needs to be exported to Excel. That being the case, you're going to have to manipulate the Excel object model in an ActiveX script task to add a sufficient number of sheets to your workbook to contain all the data in the export, and then use a loop to insert the appropriate rows to each sheet (dynamically setting the destination through script or a dynamic properties task). That's how I'd do it, anyway. Having said that, I haven't used DTS in over two years, so I could be talking rubbish!  Mark |
 |
|
|
psdp
Starting Member
3 Posts |
Posted - 2007-08-30 : 00:10:03
|
quote:
Originally posted by mwjdavidson
This is going to be a fair amount of work, especially if you're not that experienced with DTS. So firstly, I'd ask if this data really needs to be exported to Excel. That being the case, you're going to have to manipulate the Excel object model in an ActiveX script task to add a sufficient number of sheets to your workbook to contain all the data in the export, and then use a loop to insert the appropriate rows to each sheet (dynamically setting the destination through script or a dynamic properties task). That's how I'd do it, anyway. Having said that, I haven't used DTS in over two years, so I could be talking rubbish! Mark
Thanks for the response...If you ask me if this data really needs to be exported to Excel, yeah...obviously..otherwise, I don't have to make a post  ..well..the requirement is to generate a report in excel pulling out data from couple of tables...so, we thought, a dts solution might help to achieve that without using an Excel Object Library...Thanks again for your thoughts...Can anyone else help? |
|
|
|
mwjdavidson
Aged Yak Warrior
735 Posts |
Posted - 2007-08-30 : 07:38:44
|
| The point of the question was that there are generally alternatives that achieve the same business objective using different (perhaps more appropriate) technology and/or techniques. The row limit in Excel has often precluded its use for these sorts of things for me in the past. I was merely pointing out that this is going to be relatively time-consuming unless you are expert in the technologies involved. You might have more luck posting in the "Import/Export (DTS) and Replication (2000)" forum.The only other approach I can suggest is giving your destination workbooks a sufficient number of sheets to handle any likely volume of data (but include some error handling to deal with exceptions), and then follow the approach outlined in my previous post. This would get around having to manipulate the Excel object model.Mark |
|
|
|
psdp
Starting Member
3 Posts |
Posted - 2007-08-31 : 00:14:59
|
quote:
Originally posted by mwjdavidson
The point of the question was that there are generally alternatives that achieve the same business objective using different (perhaps more appropriate) technology and/or techniques. The row limit in Excel has often precluded its use for these sorts of things for me in the past. I was merely pointing out that this is going to be relatively time-consuming unless you are expert in the technologies involved. You might have more luck posting in the "Import/Export (DTS) and Replication (2000)" forum.The only other approach I can suggest is giving your destination workbooks a sufficient number of sheets to handle any likely volume of data (but include some error handling to deal with exceptions), and then follow the approach outlined in my previous post. This would get around having to manipulate the Excel object model.Mark
Thanks for your valuable inputs...Got your point...Let me try the approach you have suggested and see my luck...Thanks again.. |
|
|
|
mwjdavidson
Aged Yak Warrior
735 Posts |
Posted - 2007-08-31 : 04:30:39
|
| No problem. Let us know how it goes.Mark |
|
|
|
|
|
|
|
|
DTS Table data to Excel