correcting script error when file is empty

[brunobertels]

Hi All 

Hi have a script to load daily excel files  with a sub DoDir loop because those excel file are placed in Folder by year , sub folder by month and sub folder by day. 

I need to load those column's name : 

TYPEPLI

ER

GROUPE_GESTION

ROUTE_COURANTE

SYSTEME_EXT

JOUR

NB

My script works well except when the excel file is empty : no column name in the sheet

in that case the script is in error : 

"L'erreur suivante s'est produite: Table 'Feuil1$' not found"

How i can avoid this error ant ensure that if the file is empty the script can jump to the next file ?

Below is my actual script : 

Sub DoDir(Root)

for each ext in 'xls'

for each File in FileList(Root&'\*.'&ext)

Table_Share:

load
FilePath()as Chemin,
TYPEPLI,
//Remplace vide par 'SANS ER' dans la colonne ER:
if(len(trim(ER))=0,'SANS ER',ER) AS ER,
//GROUPE_GESTION
ROUTE_COURANTE,
if(index([ROUTE_COURANTE], '_', 1) > 0, left([ROUTE_COURANTE], index([ROUTE_COURANTE], '_', 1) - 1), [ROUTE_COURANTE]) AS [ORIGINE_ROUTE_COURANTE],
SYSTEME_EXT,
//JOUR,
NB,

//Date Stock pour fichier sur SERVEUR
makedate('20'&
Subfield(filepath(), '/', 4),
Subfield(filepath(), '/', 5) ,
Subfield(filepath(), '/', 6) ) as DATE_STOCK,

// formate la date JOUR au format EU et renomme le champs en DATE_ENTREE
date(date#(JOUR,'YYYY-MM-DD'),'DD/MM/YYYY') as DATE_ENTREE
from ['$(File)']
(biff, embedded labels, table is Feuil1$)

WHERE
date(date#(JOUR,'YYYY-MM-DD'),'DD/MM/YYYY') <=
makedate('20'&
Subfield(filepath(), '/', 4),
Subfield(filepath(), '/', 5) ,
Subfield(filepath(), '/', 6) );

Next File

Next ext

for Each Dir in DirList(Root&'\*')

call DoDir(Dir)

next Dir

end sub

call DoDir('lib://STOCK GESTION/')

Store Table_Share into [lib://STOCK GESTION/Table_Share.qvd];
drop table Table_Share;

thanks in advance 

[Digvijay_Singh · Accepted solution]

Not sure but you might want to skip the error by checking Script Error code manually for the error you are receiving but it would have side effects if the same error code is generated due to some other reasons- 

https://help.qlik.com/en-US/sense/February2022/Subsystems/Hub/Content/Sense_Hub/Scripting/ErrorVaria...

[marcus_sommer]

You may add a pre-load before your origin load which may look like:

t: load @1 as F from ['$(File)'] (biff, no labels, table is Feuil1$)

and then you could check the rowno() of the table or maybe the fieldvaluecount() and then executing the main-load or doing nothing and therefore jumping to the next file.

An alternatively may be to check the filesize() - an empty or not complete Excel might be much smaller as the filled ones. And of course the already mentioned ERRORMODE is also a possibility.

In each case it might be useful to track the result of the check or the script error in a trace-statement or just writing it within another table which is monitored within a governance-control application.

- Marcus 

[brunobertels]

Hi 

thanks for your respons 

I manage my laod error due to "tale Feuil1 not found with that's script 

Sub DoDir(Root)
for each ext in 'xls'
for each File in FileList(Root&'\*.'&ext)

// set error mode 0 pour accepter si un fichier sur une date et vide et eviter erreur Load 11 table vide :
//
set ErrorMode=0;

/////////////////////////////////////////////////
Table_Share:
LOAD
.....

from ['$(File)']
(biff, embedded labels, table is Feuil1$);

If ScriptError=11 then
exit script; EndIf
//////////////////////////////////
Next File
Next ext
for Each Dir in DirList(Root&'\*')
call DoDir(Dir)
next Dir
end sub
call DoDir('lib://STOCK GESTION/')

[brunobertels]

Hi Marcus 

Thanks for the advice 

unfortunately a didn't know how to implement the check rowno() part of the script 

I manage my issue with a set error mode =0 before loadind 

then a if statement at the end like this 

If ScriptError=11 then
exit script; EndIf

[marcus_sommer]

It might be in regard to my example above done with something like:

if noofrows('t') > 0 then
   ....
end if

- Marcus