Don't create empty xls files 1

[nicktagz]

I'm currently using the copy to command to export dbf tables to xls for certain criteria. My question is if there is no data to export is there a way to not create the xls file?

 

[Tom Borgmann]

Hi nicktagz,
why don't you wrap your copy to command in a IF RECCOUNT( TABLE ) > 0....ENDIF ?

-Tom

https://www.blogger.com/profile/08903165976787522012

 

[nicktagz]

Thanks, that's what I was looking for.

 

[Mike Lewis]

You say you want to export "for certain criteria". I take that to mean that you want to skip the XLS if there is no data that meets the criteria.

If that's so, then the easiest way would be something like this:

SELECT MyTable
COUNT TO lnCount FOR x > y  && or whatever criteria you want to apply
IF lnCount > 0
  COPY TO etc. etc. TYPE XLS
ENDIF

But if my assumption is wrong, that is, if you only want to skip the XLS if the original DBF is completely empty, then use Tom's method. But note that that won't skip the copy if all the records in the file have been deleted but not packed, which is probably not what you want.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Tore Bleken]

Run a simple "Select * from yourtable where <yourcriteria> into array laDummy" first, and only continue with the export if _tally>0. Or even simpler, Select into a cursor, and then if _tally>0, use the cursor as source for the export.

 

[Olaf Doschke]

Well, _TALLY also counts the number of copied records of COPY TO, so it's a good advice to use it, but it's not necessary to first SQL-Select data.
One simple idea would be to delete the file you just created, IF _TALLY=0.

An elegant way to not wear out SSDs or in general hard drives, would be using a RAM Disc as first stage and then copy to a real drive if _TALLY>0. It's a bit too late for that nowadays, as SSDs today don't wear out so much anymore on one side and since I actually don't know if there is a RAM disc capable to work on Windows 10 anymore, rather using a virtual hdd or crypto container, which also uses a drive virtualisation. Still a bit over the top, you could really simply write out the file you want to and remove it, when _TALLY=0.

Bye, Olaf.