Skip to main content

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
QlikWorld 2023, a live, in-person thrill ride. April 17 - 20, 2023, in Las Vegas! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
A7R3
Contributor III
Contributor III

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

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, 

Labels (4)
1 Solution

Accepted Solutions
Vegar
MVP
MVP

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

 

View solution in original post

2 Replies
Vegar
MVP
MVP

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

Hi Vegar,

Thank you. It works nicely.

Regards, Ernest