Import Oracle Dbase over 65k rows

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

Everyday I used to import the oracle database (Sales Data - Invoice
Numberwise) in Excel.
Now the data is almost about 1,50,000 rows and excel have a limitations of
65000 rows.
Is there any solution, say if the recordset data is above 65000 rows then
excel should
add another sheet & copy the data upto 65000 rows in one sheet & above 65000
is in
another sheet.

Help in this regard will be hieghly appreciated.
 
Mr. Chip Pearson,

Thanks for taking the time to post. Your knowledge is helpful in so many
ways. Procedure, Function, Implementation code, All with helpful and great
comments.

Thanks,

Christmas May

Chip Pearson said:
You could adapt the code for importing files with more than 64K lines
described on and dowloadable from
http://www.cpearson.com/excel/ImportBigFiles.htm . The code is written for
text and CSV files, but should easily adaptable to any type of data source.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)
Click to expand...
 
If you use ADO to retrieve your data,
1. Create the Recordset Object
2. Set the PageSize property to 65, 535 (first row is for column names)
3. Open your recordset
4. Loop though the Recordset by Page, adding a new worksheet for every page
a. write the column names to row 2
b. write all the data for the page at A2 using the CopyFromRecordset object
 
Back
Top