This discussion is archived
4 Replies Latest reply: Mar 19, 2013 8:27 AM by emss RSS

xlsx export does not finish - extremely slow Excel export

emss Newbie
Currently Being Moderated
Installed 64bit SQLDeveloper on Win 7 64 bit machine with 4 meg ram.

When I export a large file/qry to Excel, the job basically will not end. The progress bar reports progress iandrecord count increments...eventually freezing the SLQDevelper application. This was one reason why I procured the 64 bit machine - the same problem was being experienced on a 32 bit Win xp system.

I use another tool, PLSQL Developer on the 32 bit as a work-around, and larger files are exported cleanly....but I am hoping to avoid having to use different Developer tools/applications - I prefer SQLDeveloper.

Any thoughts or help on how to get SQLDevelper to perform large (10M) file exports?
  • 1. Re: xlsx export does not finish - extremely slow Excel export
    Gary Graham Expert
    Currently Being Moderated
    Hi,

    This is a known issue with export to xls formats. You'll have better performance with export to csv.
    You can also try increasing the AddVMOption  -Xmx640M setting in your ...\sqldeveloper\ide\bin\ide.conf file.

    Regards,
    Gary
    SQL Developer Team
  • 2. Re: xlsx export does not finish - extremely slow Excel export
    emss Newbie
    Currently Being Moderated
    Any word out there as to if/when Oracle might be able to get a fix out by chance?

    Been quite some time now this defect has had me using multiple tools.
  • 3. Re: xlsx export does not finish - extremely slow Excel export
    Gary Graham Expert
    Currently Being Moderated
    This functionality relies on Apache POI. SQL Developer switched from POI 2.5.1 to 3.7 as of our release 3.1. POI 3.9 became available early December 2012, but I have no idea if/when it might replace 3.7 in our tool.

    The Apache POI change log indicates there were performance and memory improvements in the 3.8 and 3.9 releases. Maybe one or more of those ( search the log for mem or perf ) could help in your case:
    http://poi.apache.org/changes.html

    -Gary-
  • 4. Re: xlsx export does not finish - extremely slow Excel export
    emss Newbie
    Currently Being Moderated
    Follow-up to this thread regarding use of .csv technique.

    The .csv technique is very fast in download speed. There of course is another pit-fall - Excel, when opening a data element in a .csv or .xml file, will drop the leading zeros. I've examined the data before Excel open, find Oracle's output is correct, and in my opinion, the fault lies in Microsoft for corrupting the data's integrity from the original content extracted.

    There are two ways I an aware to export from Oracle using SQLDeveloper, and import to Excel to resolve data that needs to retain leading zeros - for example Item Numbers and Postal Zip Codes.

    One is to export to a .csv...then open Excel and import via a data source. One must then change the data type from GENERAL to TEXT for each respective column needing leading zeros to be retained. I do not prefer this method, but it works.

    The other is to export data as a .csv, but in the sql, code the data needing the work-around as such: *,'="' || to_char(a.Segment1) || '"' as Item_Number*. This example is for an Item Number/Segment1 situation. When the .csv is opened, the column displays correctly, but the data is a formula...not a data character string. Therfore, simply click on the column, copy and paste special as a value, and the data integrity is resolved. (may not even need to cpy/paste special if vlookups not being performed). So, this techinique seems to be a little for user friendly and effecient.

    As an end-user, two bads don't make a right...shame on Oracle and SQLDeveloper for providing a defective output tool; shame on Microsoft for corrupting data at Excel open.

    Edited by: emss on Mar 19, 2013 8:24 AM

    Edited by: emss on Mar 19, 2013 8:26 AM

Legend

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