Loop through folders and only load file names

[ben0109]

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

[Mauritz_SA · Accepted solution]

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

[ben0109]

Hi Mauritz

Thanks! It works perfectly!

Cheers