How to skip empty folder in a For-Next loop in load scrip

[A7R3]

Hi,

I am using the the following script to load files within folder name with dates. Users will upload the files when there is new files made available. There are instances that users will leave empty folder with no file in it, and the QS will show the following error: 

'Cannot open file: 'lib://QSD/20200801/Report*.xls'

Here is my load script.

FOR Each vDir in DirList('$(vDataPath)/*')

    Table:

    LOAD Distinct
    TRIM(Mid(filebasename(), 16)) as Products,
    FileTime() as ReportTime,
    Date(Floor(FileTime())) as ReportDate,
    MonthName((Floor(FileTime()))) as MonthYear,
    Year(FileTime())*12+Month(FileTime()) as ReportYearMonthKey,
    @13 as SpecID,
    @2 as MatlNumber,
    @3 as MatlDescription,
    @14 as "Spec. Type"
    FROM [$(vDir)/Report*.xls] (biff, no labels, header is 10 lines)
    Where @14='MAT_PART_E';

Next

The directory structure is as follow, and there are .xls file within each folder to be loaded into QS.

10/06/2020  08:42 AM    <DIR>          .
10/06/2020  08:42 AM    <DIR>          ..
10/06/2020  08:42 AM    <DIR>          20200801
08/05/2020  08:58 AM    <DIR>          20200805
08/07/2020  01:09 PM    <DIR>          20200807
08/12/2020  11:13 AM    <DIR>          20200812
08/13/2020  06:01 PM    <DIR>          20200813

How can the script be updated with a logic to check empty folder and if it is an empty folder then

1. If it is the last folder, end the loop
2. If it it not the last folder, skip it and go to the next folder.

Thank you in advance for the help.

Regards, 

[Vegar · Accepted solution]

You could try to loop through the files within each folder as well as each folder. Try the script below.  

FOR Each vDir in DirList('$(vDataPath)/*')

FOR each vFile in filelist (vDir&'\*.xls' )

Table:

LOAD Distinct
TRIM(Mid(filebasename(), 16)) as Products,
FileTime() as ReportTime,
Date(Floor(FileTime())) as ReportDate,
MonthName((Floor(FileTime()))) as MonthYear,
Year(FileTime())*12+Month(FileTime()) as ReportYearMonthKey,
@13 as SpecID,
@2 as MatlNumber,
@3 as MatlDescription,
@14 as "Spec. Type"
FROM [$(vFile)] (biff, no labels, header is 10 lines)
Where @14='MAT_PART_E';

Next vFile

Next vDir

[A7R3]

Hi Vegar,

Thank you. It works nicely.

Regards, Ernest