Problem Exporting data to Excel
-
Hi
I have a problem of exporting table data to excel shhet. table contain more than 100,00 records when it is trasfering it says that excel exceeds max no of records which is obvious
Is there anyway of tranfering data in to several sheets
My Blog:
http://dineshasanka.spaces.live.com/ -
Quick and dirty fix:
If you have an incremental ID in the table, restrict the data returned in the query. Then set up tow or three dts jobs to each export a subset of the data to a different spreadsheet.
You can then play around with the spreadsheets.
For a more intuitive way (but a lot more complicated), you need to get the dts package to ppopulate a recordset. You can then use activex to create a spreadsheet, add a worksheet and export the exact number you want from the recordset into it, then a new worksheet and some more of the records etc. That way, you can get it all in one speadsheet on different tabs. There's a lot of code out there to do this, but it will take a little time to set up and be a slower in the long run.
------------------------------
The Users are always right - when I'm not wrong! -
Thankx Jonathan,
I thought there should be a easy way of doing it from sql server 🙁
anyway thank for the answer
My Blog:
http://dineshasanka.spaces.live.com/ -
Don't forget, you can specify exactly how many rows to transfer in the transformation data task properties dialog box on the options tab.
Here is another thought - if had identity on the table, or a temp version of it - and say you had 4 pages - you could select
myFields from myTable where myIdentityField % 4 = 0 for page 1
myFields from myTable where myIdentityField % 4 = 1 for page 2
etc....
[font="Courier New"]ZenDada[/font]
-
hmm
Good Trick
Thankx
My Blog:
http://dineshasanka.spaces.live.com/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply