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
65,000 row limit (Read 3865 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
65,000 row limit
21.06.07 at 13:45:07
 
65,000 row limit
 
Question:
We currently use an Oracle based ERP package and are about to move to an SQL based one. All Oracle data (tables, etc.) need to be exported across, but there may be over 65k records. Would Excel-DB be able to spread into multiple worksheets?
 
Answer:
At the moment it is not doing it automatically however it is rather simpel to work around it. Usually in such a large table there is a key column - an id of some sorts. Just add to the query an order by your_id. Then query the information into the spreadsheet. When SQL*XL reaches the end of the sheet it will notify you and abort the query. Just look what the last key value was put in the sheet (e.g. 1234567) and resume the query from that value on the next sheet by running the same query but adding:
where your_id > 1234567
order by your_id
 
I will look into adding an automatic continuation on the next sheet.
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: 65,000 row limit
Reply #1 - 24.08.07 at 07:49:30
 
Automatic continuation is now achieved and the 65k row limit is lifted. Whenever a query is run that returns more rows than there are available to the end of the sheet a new sheet is created and the data is put in the new sheet. If that one is full yet another new sheet is created, and so on. Note that this is all done silently so you will only notice when all of the records have been retrieved.
 
See:
Beyond Excel's maximum row limit  
http://www.oraxcel.com/cgi-bin/yabb2/YaBB.pl?num=1187878422/0#0
Back to top
 
 

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