Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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-
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-
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
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/')
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
It might be in regard to my example above done with something like:
if noofrows('t') > 0 then
....
end if
- Marcus