This discussion is archived
8 Replies Latest reply: Feb 18, 2013 2:11 PM by rp0428 RSS

exporting a resultset from SQL developer to excel

913877 Newbie
Currently Being Moderated
Hi,

The result set of one of my query contains around 15 million rows. Am trying to copy the resultset to an excel by right clicking the resultset and then using -> export data ->xls.
But this operation is very slow as only 100,000 rows are getting loaded in last 10 minutes.

Is there a way to create a pivot table in spreadsheet use my the result set in SQL developer as a source?

Regards
-Learnsequel

Edited by: 910874 on Feb 22, 2012 2:15 AM
  • 1. Re: exporting a resultset from SQL developer to excel
    SusilKumarNagarajan Explorer
    Currently Being Moderated
    SQL developer freezes when huge set of data is extracted. As you say it is 15million records, use Toad if you have.
    Or

    Spool the output in a txt file with columns Comma separated.

    After spooling , Split the file into parts and save in XL sheets if you are not using latest MS-Excel.

    Use OS commands to split large file. ( Much easier on Unix )
  • 2. Re: exporting a resultset from SQL developer to excel
    19426 Guru
    Currently Being Moderated
    SQL developer freezes when huge set of data is extracted
    I fear Excel will freeze even more, 15 million rows in an Excel spreadsheet? Very admirable!
  • 3. Re: exporting a resultset from SQL developer to excel
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    910874 wrote:

    The result set of one of my query contains around 15 million rows. Am trying to copy the resultset to an excel ....
    Sorry, but this just sounds plain stupid.

    EXCEL IS NOT A DATABASE!

    Never mind the fact that Excel does not even support 1 million rows in a worksheet. It is not designed and not developed to deal with crunching and analysing millions of rows. A database is.

    Use your software tools correctly. Treat Excel as a spreadsheet and the database as a database.
  • 4. Re: exporting a resultset from SQL developer to excel
    913877 Newbie
    Currently Being Moderated
    Is there a way to create a pivot table in spreadsheet use my the result set in SQL developer as a source?
  • 5. Re: exporting a resultset from SQL developer to excel
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    910874 wrote:
    Is there a way to create a pivot table in spreadsheet use my the result set in SQL developer as a source?
    No. You can connect your Excel directly to the database server via ODBC.

    Excel can thus pass a SQL query to Oracle. Oracle can do the major crunching of data, returning a manageable and relevant data set to Excel. Excel can then handle the rendering of that data set and provide additional analysis functionality on that data set.

    Standard client-server.

    To connect Excel to SQL-Developer, is client application to client application connectivity. This is not the norm. And requires both applications to support some kind of Inter Process Communication (IPC) interface to talk to one another. In Windows, a typical interface for this is (or was) DDE - Dynamic Data Exchange. This has evolved into more complex OLE, ActiveX and COM interfaces, but is still supported and a fairly easy thing to implement when you code an Windows application.

    SQL-Developer is however a platform agnostic Java application that runs on multiple operating systems. And it is unlikely to provide extensive Windows style IPC integration using DDE/OLE/ActiveX/COM.
  • 6. Re: exporting a resultset from SQL developer to excel
    RMi Newbie
    Currently Being Moderated
    Here are the Excel limitations:

    Excel 2007/2010
    Worksheet size      1,048,576 rows by 16,384 columns

    Source: http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP010342495.aspx?CTT=5&origin=HP005199291

    Excel 2003
    Worksheet size      65,536 rows by 256 columns

    Source: http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP005199291.aspx

    15 million records is far beyond that, not to mention a very bad use case of Excel.

    Like others said, just use the database to do the crunching and Excel to just display the relevant data.
  • 7. Re: exporting a resultset from SQL developer to excel
    991791 Newbie
    Currently Being Moderated
    You might want to look into "Excel MS Query" (under Data -> From Other Sources -> Microsoft Query in Excel 2010). This will allow you to build a pivot table in Excel linked directly to your source database (via your ODBC connection). I'd expect performance with 15m records would be a problem but MSQuery lets you summarize and filter the data. Maybe you'd want get your result set down to < 100k rows and you could always create a couple different pivot views.
  • 8. Re: exporting a resultset from SQL developer to excel
    rp0428 Guru
    Currently Being Moderated
    It's nice that you want to contribute to the forums.

    But your contributions would be much more helpful if you reply to current threads. This thread is over year old.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points