how to pass over empty files on file load?

[stevelord]

Hi, I have files automatically saved to a folder for activity taking place, and sometimes there are days where no activity takes place and the file is empty.

On days of activity, the file will have 3 tabs showing the activity in different ways, and my script is loading data from tab 2 which ahs the data I need.

On days of no activity, the file will have 1 tab containing a couple of lines about no activity and the other two tabs will not be present.

Solution I tried was to identify the file size of empty files and add where filesize()>20000; to try to make it only look at files containing data.  The empty files are all the same ~20kb file size, and files with data are 60kb for one group and 120kb+ for another group, so I have plenty of leeway to say >30000 bytes and such.  Even so, the qlikview is still tripping on the empty files.

Any ideas on a modification to this approach, or another approach I can use?  (Other than a recurring manual activity to isolate empty files and remove if present every day.)  Thanks!

[whiteline]

Hi.

If you mean Excel files you can use ODBC connection to the file and then get the list of sheets with

sheets:

SQLTables;

[#3]

LOAD all files including the empty files but do it using a loop, once you have loaded the files check NoOfRows() for the table just loaded, if it is 0 drop the table, otherwise load it.

[stevelord]

Actually, the catch is I am trying to load (for instance) Sheet2 data from the excel file, but when the excel file is empty, there is no Sheet2 and I get the biff table not found type error message when I tried to Load * that heap of files. If it was just a matter of Sheet2 being empty, I’d be fine loading it because I have where statements picking columns and rows with the data I want (and none of the empty rows in the file that actually has data).

So the basic issue is some files have tab 2, and some do not, and I want to load only the ones where a tab 2 is present.

[stevelord]

Anyone to connect to all the files in a folder, pull the list of tables, and load the ones that have a particular table?

[sukydhak]

Do a ODBC Connection to the xls with a where statement for Sheet2 if found that file is ok to load else look at the next file. 

[Peter_Cammaert]

Switch off regular error processing by the Scripting Engine, by setting

SET ErrorMode = 0;

Now, catch all errors that may point to a missing file (if you wan't to read all files that match a particular naming pattern), a missing sheet (or table which seems to be the same), missing columns on an existing sheet or simply an otherwise correct sheet without any useful data (check NoOfRows()). Look for an item called "Error Variables" in QlikView help. There's a list of possible error codes.

Remeber to set ErrorMode back to the default value at the end of your exception handling code.