Home » SQL & PL/SQL » SQL & PL/SQL » table export to excel (12c)
table export to excel [message #674579] Mon, 04 February 2019 04:42 Go to next message
m.abdulhaq
Messages: 254
Registered: April 2013
Location: Ajman
Senior Member
i am trying to extract data from our GL transaction data based in oracle to excel, but could not export it due to size limitations of rows in excel. Actually i have more than 10 million records inside data table of oracle.Any ideas.
Re: table export to excel [message #674581 is a reply to message #674579] Mon, 04 February 2019 05:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

How is this an Oracle problem?

Re: table export to excel [message #674582 is a reply to message #674581] Mon, 04 February 2019 05:48 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Export to multiple excel files obviously.
Re: table export to excel [message #674586 is a reply to message #674579] Mon, 04 February 2019 07:27 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
m.abdulhaq wrote on Mon, 04 February 2019 02:42
i am trying to extract data from our GL transaction data based in oracle to excel, but could not export it due to size limitations of rows in excel. Actually i have more than 10 million records inside data table of oracle.Any ideas.

Excel can connect directly to Oracle via ODBC & can issue INSERT directly into the DB using VB.
Re: table export to excel [message #674588 is a reply to message #674586] Mon, 04 February 2019 07:47 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
if you use the AS_XLSX package to export to a true XLSX spreadsheet, simply insert multiple sheets into the spreadsheet. The software is freeware and is located at

https://technology.amis.nl/2011/02/19/create-an-excel-file-with-plsql/

They even suggest using this package in asktom.oracle

I use it myself and it works very will.

[Updated on: Mon, 04 February 2019 07:48]

Report message to a moderator

Re: table export to excel [message #674591 is a reply to message #674586] Mon, 04 February 2019 08:31 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
BlackSwan wrote on Mon, 04 February 2019 13:27
m.abdulhaq wrote on Mon, 04 February 2019 02:42
i am trying to extract data from our GL transaction data based in oracle to excel, but could not export it due to size limitations of rows in excel. Actually i have more than 10 million records inside data table of oracle.Any ideas.

Excel can connect directly to Oracle via ODBC & can issue INSERT directly into the DB using VB.
The OP is stuck exporting the data from the DB to excel, not importing the data into the DB from excel.
Re: table export to excel [message #674593 is a reply to message #674591] Mon, 04 February 2019 09:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
but could not export it due to size limitations of rows in excel.
And he is (unless wrongly expressed or understood) stuck with row size in Excel so how could this be an Oracle problem? Any way you export to Excel the row size will be the same.

Re: table export to excel [message #674596 is a reply to message #674593] Mon, 04 February 2019 10:13 Go to previous message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
I know that Michel - it's why I told him to export to multiple files, that (and multiple sheets as Bill mentioned) is the only way round that.

Previous Topic: Do I have to use ID as a PK?
Next Topic: Account number selection from 10 parallel session
Goto Forum:
  


Current Time: Fri Mar 29 06:24:51 CDT 2024