Home » SQL & PL/SQL » SQL & PL/SQL » data to excel (windows 2000)
data to excel [message #397225] Thu, 09 April 2009 09:14 Go to next message
waqasbhai
Messages: 118
Registered: August 2008
Location: Pakistan
Senior Member
I have 200,000 rows in a table and i want to export it in excel. I tried using export option in SQL Navigator but the problem is that each worksheet of excel can accomdate only 65000 (or so) rows and after that it stops and leave rest of the rows.
What is the most convinent and fast way to do that.???
Re: data to excel [message #397227 is a reply to message #397225] Thu, 09 April 2009 09:16 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
waqasbhai wrote on Thu, 09 April 2009 16:14
What is the most convinent and fast way to do that.???

Not exporting it.
Who in his right mind will read those 200000 records?
Re: data to excel [message #397228 is a reply to message #397227] Thu, 09 April 2009 09:17 Go to previous messageGo to next message
waqasbhai
Messages: 118
Registered: August 2008
Location: Pakistan
Senior Member
it is the requirment to put all the rows in excel. Its really important so please help it out
Re: data to excel [message #397229 is a reply to message #397228] Thu, 09 April 2009 09:19 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Excel is limited to 65535 rows. There is no way around this, unless you pay Microsoft to code you a special version.
Re: data to excel [message #397232 is a reply to message #397229] Thu, 09 April 2009 09:30 Go to previous messageGo to next message
waqasbhai
Messages: 118
Registered: August 2008
Location: Pakistan
Senior Member
Can't we do that using SQL..???
Re: data to excel [message #397233 is a reply to message #397225] Thu, 09 April 2009 09:36 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
>Can't we do that using SQL..???
no
Re: data to excel [message #397235 is a reply to message #397229] Thu, 09 April 2009 09:41 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You might be able to use the OWA_SYLK package to export the data in a format that would import into multiple worksheets, or export the data into multiple 65000 row files.

At the end of the day, this is an Excel problem.
Oracle can export the data, Excel can't import it.
Re: data to excel [message #397249 is a reply to message #397229] Thu, 09 April 2009 10:24 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
ThomasG wrote on Thu, 09 April 2009 15:19
Excel is limited to 65535 rows. There is no way around this, unless you pay Microsoft to code you a special version.


Excel 2007 1048576 Rows.
Re: data to excel [message #397431 is a reply to message #397225] Fri, 10 April 2009 12:00 Go to previous messageGo to next message
manu_d
Messages: 3
Registered: April 2009
Junior Member
Yes, you can export data into .csv file format by writing a small program in command promt. you can open that file in excel but will only see 65K rows.. Limited by excel.

to view all data you can open in some text editor e.g edit plus... etc...

if you want to load that into some other database use sql loader...
Re: data to excel [message #397436 is a reply to message #397431] Fri, 10 April 2009 12:55 Go to previous messageGo to next message
waqasbhai
Messages: 118
Registered: August 2008
Location: Pakistan
Senior Member
Thanks all for the reply.
yehhh, great info that Excel 2007 supports 1048576 Rows so we can get at least half in a single excel.
Any how, we did by splitting out the data into many excel sheets.
Thank you.
Re: data to excel [message #397484 is a reply to message #397436] Sat, 11 April 2009 02:20 Go to previous message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
waqasbhai wrote on Fri, 10 April 2009 18:55

yehhh, great info that Excel 2007 supports 1048576 Rows so we can get at least half in a single excel.


Quote:
I have 200,000 rows in a table


Seems to me that you could get all several times over...
Previous Topic: Help in using Anlytical function for a requirement
Next Topic: query to give all months
Goto Forum:
  


Current Time: Sat Dec 03 16:24:02 CST 2016

Total time taken to generate the page: 0.10662 seconds