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

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

A7R3 — Sat, 16 Nov 2024 18:00:50 GMT

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';

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

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

Thank you in advance for the help.

Regards,

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

Vegar — Tue, 06 Oct 2020 03:38:56 GMT

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

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

A7R3 — Wed, 07 Oct 2020 06:13:30 GMT

Hi Vegar,

Thank you. It works nicely.

Regards, Ernest