Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

stevelord
Valued Contributor

how to pass over empty files on file load?

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!

6 Replies
whiteline
Honored Contributor II

Re: how to pass over empty files on file load?

Hi.

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

sheets:

SQLTables;

Not applicable

Re: how to pass over empty files on file load?

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
Valued Contributor

Re: how to pass over empty files on file load?

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
Valued Contributor

Re: how to pass over empty files on file load?

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
New Contributor III

Re: how to pass over empty files on file load?

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. 

Re: how to pass over empty files on file load?

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.