Toad World® Forums

Export empty table to EXCEL file


#1

We have recently upgraded from 8.0 to 9.7.2. One of our regular processes involves exporting the contents of approx 50 tables to EXCEL files

In TOAD 8.0, one EXCEL file was created for each table. Howerver, under 9.7.2, EXCEL files are only created for tables which actually contain data. In addition, a warning message is displayed for each table which is empty.

Is there a way for TOAD to create the EXCEL file even if the table is empty? I’ve looked through all of the various ‘TOAD Options’, but can’t find anything obvious. I’ve also tried saving as different file types, but TOAD sill won’t create the export file if the ORACLE table is empty.


#2

Sorry, there is no option to control this.


#3

Was there a reason why this behaviour was changed between 8.x & (presumably) 9.x?

It means that we now have a problem in that we don’t know how many EXCEL files will be created if we export the contents of ‘n’ tables (other than it will be less than or equal to ‘n’!)

I know it normally makes sense to not create export files if the Oracle table is empty, but given that this is a change in the default behaviour, I would have thought there should be some sort of option to control whether to create empty export files or not.

Is this a possible enhancement to add such an option?


#4

There’s a good chance that was an un-documented feature…

I’m wondering if there’s a way to take adavantage of the Automation
Designer to build a script that would dynamically inspect the empty tables and
build the XLS files anyway with a dual query of some sort.

Are the 50 tables static? Maybe you could start the directory out with blanks
XLS files for each table and let Toad clobber the one that have data as a
workaround? I know that’s something you could do with the automation
designer.


#5

It’s been a while, but I’m guessing we changed it because someone
thought it was a bug. I will say, you aren’t the first person
who’s asked for this, maybe it should be an option.


#6

Would it make sense to create the empty file for the empty table if the
“Include Nulls” option is on?
image001.jpeg


#7

The users opinion should outweigh mine, but I don’t think so. Include
nulls makes the text appear, which could be inconvenient when the
exports that do contain data.


#8

I t might of been a bug to some but to others it was a nice feature. I would
agree with Ian that it would be nice that an empty file gets generated when
there is no data returned. If you are outputting the headers then that is all
that would be in the file.

I would think making it an option would be the best for everyone. I would rather
see an empty file then no file. At least with an empty file I know that my
scheduled action ran. I f these files are being put into a shared location for
others to look at and there’s no file, guess who is getting a phone call. :slight_smile:

Ed

[TeamT]


#9

I must admit that I did try the ‘include nulls’ option to see if it did actually generate the file (but it didn’t!). Even if it did, then having ‘’ appear in the fields would probably cause problems elsewhere…

Unfortunately, we have a number of systems where we occasionally have to extract data to EXCEL tables. Therefore, the number of tables isn’t always constant and the table names differ between systems.

At the moment, the simplest option is for us to manually create ‘empty’ XLS files for any empty tables. It is not often that there are ‘empty’ tables to export, so we wouldn’t need to do it that often anyway

However, it would be better to have a user selectable option to “Create output file is table / Grid is empty” (Default to off to retain the current behaviour?). This would ensure that you would always get ‘n’ files created when you export the contents of ‘n’ tables (and that I don’t get any phone calls…)

Message was edited by: Ian Billingham


#10

Next beta has an “allow empty files” option in the export data
dialog.