Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Distribute more than 65536 rows in multiple sheets (Read 2821 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Distribute more than 65536 rows in multiple sheets
30.03.06 at 09:49:32
 
Haris from Qatar asked the following question.
 
Question:
My tables of Oracle are much bigger in size than one Excel sheet can take and thus all the time I am having result sets for more than, 65536 rows, so, could it be possible that outout will be distributed in 2,3,or more sheets as required.
 
Answer:
For the moment you will need to partition the table yourself to fit on different sheets. It is not as complex as you may think. Try to find a good key to split your table into different chunks each less than 65536 in size.
 
Count the rows in the table first: select count(*) from mytable
 
Then experiment to see whether you can partition the table into smaller chunks. For example:
select count(*) from mytable where id between 1 and 10000
 
When you have found the queries you can use you can use the syntax to explicitly direct the output into different tabs. You can also choose to do the assigning to different tabs manually:
select * from mytable where id < 10000 to Sheet1!A1;
select * from mytable where id between 10000 and 20000 to Sheet2!A1;
select * from mytable where id >= 20000 to Sheet3!A1;

Back to top
 
« Last Edit: 30.03.06 at 09:49:52 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1