1 2 Previous Next 19 Replies Latest reply: Aug 21, 2013 1:06 PM by Mike Kutz RSS

    Method for Export to CSV for large data sets

    jfosteroracle

      Greetings,

       

       

      I have an application where the customer wants to export a report's data to CSV.  I am using the 'Enable CSV Output' function on the report.  It works ok initially with smaller data sets.  But once the data set gets large (for example over 100,000 records) the process appears to run but 'hangs' and never comes back with the 'File Download' window.  When testing with smaller data sets, it appears that it is exporting 90 records per second to create the CSV file in APEX.  So for a report with 4000 records, it is taking approximately 45 seconds before the 'File Download' window pops up.  By the end of 'season' for this application, I will routinely have over 100,000 records they will want to export to CSV.   Testing with a report with over 100,000 records, if it takes 90 records/second, it would take approximately 18.5 minutes to run.  When I test it in my APEX report and try to export the data, it runs and 'hangs' and the 'File Download' window never appears.

       

       

      I also tested a data set with 111,000 records and tried exporting to a text file in TOAD, That process took 3 minutes to run.  That is approximately 600 records per second.  Much Faster.

       

       

      So my question is, is there a faster way to export a report's data to a CSV but stay within the APEX application?  As I say,  I know I could use TOAD or SQL Developer, but the customer is going to need to export the data from this report from within their APEX application.

       

       

      FYI, I did try looking at Denes Kubicek's 'Custom CSV Export' but that took approximately the same amount of time to run as using the 'Export to CSV' feature in APEX report.

      https://apex.oracle.com/pls/otn/f?p=31517:227:4712959917249::NO

       

       

      I am running Apex 4.1.1

       

      Thanks in advance,

      John

        • 1. Re: Method for Export to CSV for large data sets
          Mike Kutz

          Generating a CSV file seems to come up on the AskTom site a few times a year.

          Ask Tom "export to CSV"

           

          Wrapping that C application in APEX may not be easy.

          You may want to see if your customer is OK with you generating the CSV in the background then emailing it to them when the JOB is done.

           

          Beyond that, see if you can short-circuit the whole "generate a CSV file" process.

          100k rows is a lot for a human.  I doubt a human is going to read each and every line of data.  That means the data is for another piece of software.

          I would look into getting "that other piece of software" to connect to the DB and run the SQL itself.

          That means: you just generate the script that tells "that other piece of software" what to do.

          • 2. Re: Method for Export to CSV for large data sets
            jfosteroracle

            Mike, how would I go about generating the CSV in the background, then emailing them the JOB, as you suggest?  Is there a way to use an 'Export' button to trigger that process?  And what would that process be?

             

            Thanks,

            John

            • 3. Re: Method for Export to CSV for large data sets
              Mike Kutz

              All of these are 'steps' in the overall process.

              You will want to create a package to hold all the code with each 'step' being implemented as one procedure.

              From there, the 'button' in APEX will call just one of them.

              I haven't done this exact thing; I can only guide you on the skeleton structure of 'how to'.

              You will have to fill out the package body.  Also, I'm sure the parameters for the procedures will need to be changed.

               

              I would start by finding out if UTL_FILE method will be fast enough for you.

               

               

              create or replace
              package my_csv_export_api
              as
                /* APEX calls this one
                 This sets up information for DBMS_JOBS/DBMS_SCHEDULER and runs it
              (you use DBMS_JOBS/DBMS_SCHEDULER to run things in the background)
              I think APEX has some utilities that wraps them.
              
              But, if you want to write your own:
                Search google (and AskTom) for examples.  Don't forget to read the documentation for them also.
              
              basically, this procedure needs to get a JOB id (DBMS_JOB) or JOB_NAME (DBMS_SCHEDULER)
              saves that job id/name with the parameters to be used into a table
              and enable (DBMS_SCHEDULER) the job (DBMS_JOB is already 'enabled')
              one of those parameters may have to be the SQL statement.
                 */
                procedure process_report( i have no idea what parameters will be needed );
              
              
                /* this procedure is the one that is ran in the background
              All it needs is the JOB_NAME (DBMS_SCHEDULER) or JOB id (DBMS_JOB)
              From there, it will pull all the other needed parameters from a table.
              After that, it calls the next two procedures.
              */
                procedure run_this_through_scheduler( p_jobname in varchar2 );
              
                /* Uses UTL_FILE to write CSV.  Search for code on AskTom.oracle.com
                   I have no idea how to extract out the SQL for the report in PL/SQL
                   I think some of it is covered in Kubicek's 'Export Excel' sql code.
              
              NOTE: at this point in time, you will not be running within the APEX framework.
              as such, v('PARAMETER_NAME') won't work.
                 */
                procedure export_to_csv( p_sql in varchar2, p_dir in varchar2, p_filename in varchar2);
              
                /* uses APEX_EMAIL to send eMail */
                procedure email_csv( p_to in varchar2, p_message in varchar2, p_dir in varchar2, p_filename in varchar2);
              end;
              /
              
              
              • 4. Re: Method for Export to CSV for large data sets
                jfosteroracle

                Mike,

                 

                This is helpful.  Thanks for taking the time to write all that out.  I will try to work through this.

                 

                Thanks!

                John

                • 5. Re: Method for Export to CSV for large data sets
                  skapex

                  Hi jfosteroracle,

                   

                  I am also facing same issue to download large data sets.

                  Did you get any solution

                  • 6. Re: Method for Export to CSV for large data sets
                    jfosteroracle

                    skapex,

                     

                    No I did not resolve this.  I have not had a chance to try implementing what  Mike suggested above.  For the size of files I need to download it can take up to 20 minutes.  When I do a similar operation using TOAD, it takes less than 5 minutes.

                     

                    If you do find a solution please post it here if you can.  Thanks!

                     

                    John

                    • 7. Re: Method for Export to CSV for large data sets
                      TexasApexDeveloper

                      John,

                        If you are still having issues, let me know.. I MIGHT have a solution for you....

                       

                      Thank you,

                       

                      Tony Miller

                      SmartDog Solutions

                      Austin, TX

                      • 8. Re: Method for Export to CSV for large data sets
                        jfosteroracle

                        Yes, I am still having issues with this.  I need a quicker way to export a report with large amounts of records. 

                         

                        Thanks,

                        John

                        • 9. Re: Method for Export to CSV for large data sets
                          TexasApexDeveloper

                          Question.. How do you want the csv export handed to the users?  E-mail the file? Write it to a network directory and alert  them of the location?

                           

                          Why I ask is, I have an idea of how to run the export as a batch job, creating a Excel style file, and either just saving it to a mapped Oracle directory, or getting the Excel file and attaching it to an e-mail..

                           

                          Thank you,

                           

                          Tony Miller

                          SmartDog Services

                          Austin, TX

                          • 10. Re: Method for Export to CSV for large data sets
                            jfosteroracle

                            Is there any way there could be a pop-up message to save the file to a location of their choice or to open the file, similar to how it works currently when exporting data from a report?

                            • 11. Re: Method for Export to CSV for large data sets
                              TexasApexDeveloper

                              Well, if it was being run as a batch job (outside the APEX session running, due to the time it seems to be taking) writing it locally would possibly be an issue....

                               

                              Have yo looked at a plugin like : - Process Type Plugin - Reports 2 Excel XML

                               

                              to see if that would help?

                               

                              Thank you,

                               

                              Tony Miller

                              SmartDog Services

                              Austin, TX

                              • 12. Re: Method for Export to CSV for large data sets
                                jfosteroracle

                                I will check out that Plugin.  It is worth a try.  Of your options, probably emailing the file would be the best option.

                                • 13. Re: Method for Export to CSV for large data sets
                                  Mike Kutz

                                  Whether our not to "email" will depend on the file size.

                                  Anything over 5MB, I would not trust through e-mail.  100k rows should exceed 5MB very easily (uncompressed).

                                   

                                  In that case, I would save the file to a table and e-mail the link to down load the file.

                                  I've done this on other platforms and in APEX.

                                  There are a few other options I am aware of.. but, let's concentrate on getting the file generated for this thread.

                                   

                                  MK

                                  • 14. Re: Method for Export to CSV for large data sets
                                    jfosteroracle

                                    Tony - I tried the plug in - 'Reports 2 Excel XML' but it didn't help my issue. 

                                     

                                    Earlier you said you had an idea of doing a batch job and saving the file.  Could that be done and then have a pop-up message (similar to current Download Data method) to save the file to a location of their choice?

                                     

                                     

                                    Thanks!

                                    John

                                    1 2 Previous Next