3 Replies Latest reply: May 15, 2014 10:00 AM by tomkrier _ RSS

    How to not export out a .csv or .xls with no records

    tomkrier _

      I have multiple jobs that run off reports and sometimes the models apply to only parts of the report that show up every now and then. The current job I'm working on has a report every single day.  I can run that model every single day but 80% of the time I will get zero records which is fine.  I was just wondering if there is a way with the scrpits or something to check if there is at least one record.  If there is at least one record to export, then export it.  If there are no records to export, they why export at all?  This would be a nice tool to have in datapump.  The users are just wasting time opening up exports with no records.

        • How to not export out a .csv or .xls with no records
          Gareth Horton

          Tom,

           

          As long as you don't have the scenario in the Excel files whereby you are replacing worksheets, and do not want to delete the entire file if you get zero records, the following code should be fine.

           

          Make sure that you put this in the PostExport section of the script:

           

          'Script Starts here

          '----


           

          'Iterate through all the outputs

          For Each item As JobLogItem In Log.GetAllOutputItems()

           

          'if the record count is zero ...

            if item.recordcount=0 then

           

               'Use a Distribution to delete the output file

               Log.AddDistribution_Delete(item.location)

                

               'Add an event to the log to show the file was deleted on purpose

               Log.AddEvent("Output file deleted due to zero recordcount")

           

               'If you want the process to report as failed, then uncomment

               'the two lines beginning with ExportCompleted

           

               'ExportCompleted = False

               'Return ExportCompleted

                

            end if

           

          Next

           

          'Script ends here

          '----


           

          Hope this helps

           

           

          Gareth

           

          Originally posted by tomkrier:

          I have multiple jobs that run off reports and sometimes the models apply to only parts of the report that show up every now and then. The current job I'm working on has a report every single day.  I can run that model every single day but 80% of the time I will get zero records which is fine.  I was just wondering if there is a way with the scrpits or something to check if there is at least one record.  If there is at least one record to export, then export it.  If there are no records to export, they why export at all?  This would be a nice tool to have in datapump.  The users are just wasting time opening up exports with no records. /b[/quote]

          • How to not export out a .csv or .xls with no records
            Grant Perkins

            Originally posted by tomkrier:

               If there are no records to export, they why export at all?  /b[/quote]I valid point but it may depend on the nature and purpose of the report.

             

            Many years ago I was working with a system that allowed users to produce regular 'response alarm' reports which were then delivered (to a printer in those days) in various filtered blocks. Often the report for a particular subset grouping would have no records, so a decision was made to eliminate the output for zero records. Whereupon the admin people running the systems were inundated with failure reports for the process not working!

             

            The reasons were explained and the user pacified. But the lighter usage users then adopted the habit of not bothering to look for any output (because they might not have seen any for some hours or even days)  and so missed alerts that they needed to take action for.

             

            In the end we compromised and always produce a header page as proof that the process had run and at the same time introduced a 'report to disk' option for remote checking, followed shortly by an mail message (DEC Vax or Unix server internal mail at that time.)

             

            The ultimate objective was to inform people that the process had run and that there was something produced.  The final addition was to allow them to see whether they needed to look at it.

             

            I think that covered all the likely options.

             

            In Data Pump terms I guess it would be possible to send a mail (for example) to a user to confirm that the process completed but produced nothing of interest for them.

             

            (Edit to correct a couple of typos.)

             

            [size="1"][ July 01, 2005, 07:04 PM: Message edited by: Grant Perkins ][/size]

            • How to not export out a .csv or .xls with no records
              tomkrier _

              I couldn't get that code to work, but it looks like what I would like to do.  One thing I thought of, which I currently have running in another report is in the email title include the item.description.  Which turns out to be the record count.  so they will know right away if it worth while opening up.