Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Qlik Users, make your voice heard! Participate in Wisdom of Crowds® Market Study: Start Survey
cancel
Showing results for 
Search instead for 
Did you mean: 
ben0109
Contributor III
Contributor III

Loop through folders and only load file names

Hi

Is it possible to only include file names in a load script?

I have data that loads from one source, but then I need to make a history table that loops through folders and get the name of the files relating to each specific field in the data load.

An example may provide more clarity.

[Load_data]:

Load *

From Sales (qvd);

[History table]:

Loop through all folders to get names of files

The folder structure will be something similar to the below:

Root

    Sales

        201910

            Hardware

                File1.xlsx

                File2.xlsx

                [...]

            Software

                File1.xlsx

                File2.xlsx

                [...]

        201911

            Hardware

                File1.xlsx

                File2.xlsx

                [...]

            Software

                File1.xlsx

                File2.xlsx

                [...]

I only need the file names, for example, (201911/Software) File1.xlsx, File2.xlsx, ...

I hope this makes sense...

Thanks for any help.

Ben

1 Solution

Accepted Solutions
Mauritz_SA
Partner
Partner

Hi Ben

You should be able to use the following:

sub ScanFolder(Root)

for each Ext in 'xlsx'

for each vFile in filelist(Root & '\*' & Ext)

let vFileName = Left(mid(vFile, index(vFile, '/', -1) + 1, 99),Len(mid(vFile, index(vFile, '/', -1) + 1, 99)));
let vFilenameWithPath = Replace(vFile, '$(vRoot)/', '');

History:
LOAD
'$(vFileName)' as FileName,
'$(vFilenameWithPath)' as FileNameWithPath
AutoGenerate(1);

next vFile

next Ext

for each SubDirectory in dirlist(Root & '\*')

call ScanFolder(SubDirectory)

next SubDirectory

end Sub

Let vRoot = 'lib://SampleRoot';

Call ScanFolder('$(vRoot)');

Regards,

Mauritz

View solution in original post

2 Replies
Mauritz_SA
Partner
Partner

Hi Ben

You should be able to use the following:

sub ScanFolder(Root)

for each Ext in 'xlsx'

for each vFile in filelist(Root & '\*' & Ext)

let vFileName = Left(mid(vFile, index(vFile, '/', -1) + 1, 99),Len(mid(vFile, index(vFile, '/', -1) + 1, 99)));
let vFilenameWithPath = Replace(vFile, '$(vRoot)/', '');

History:
LOAD
'$(vFileName)' as FileName,
'$(vFilenameWithPath)' as FileNameWithPath
AutoGenerate(1);

next vFile

next Ext

for each SubDirectory in dirlist(Root & '\*')

call ScanFolder(SubDirectory)

next SubDirectory

end Sub

Let vRoot = 'lib://SampleRoot';

Call ScanFolder('$(vRoot)');

Regards,

Mauritz

View solution in original post

ben0109
Contributor III
Contributor III

Hi Mauritz

Thanks! It works perfectly!

Cheers