Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Loop through root directory (including subfolders) and list filenames of *.csv

Hi all,

I have learned how to point qlikview to a certain folder and have it list the filenames of all the CSV files found therein.

LOAD filename() as Filename

FROM

(txt, codepage is 1252, no labels, delimiter is ';', msq, header is 1 lines);

However is it possible that qlikview loops through all the csv files not only in the parent folder but also in all subfolders within the parent folder?

I tried:

FROM

But apparently it is incorrect syntax.

Thanks in advance.

1 Solution

Accepted Solutions

Re: Loop through root directory (including subfolders) and list filenames of *.csv

Try something like this:

sub GetCSVFIleNames(Root)

    for each FoundFile in filelist( Root & '\*.csv')

        FileList:

            Load

                '$(FoundFile)'          as [FilenameWithPath]

                 Autogenerate(1);

    next FoundFile

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

        call GetCSVFIleNames(SubDirectory)

    next SubDirectory

end sub

Call GetCSVFIleNames('W:\Root\ParentFolder\') ;


talk is cheap, supply exceeds demand
7 Replies

Re: Loop through root directory (including subfolders) and list filenames of *.csv

Try something like this:

sub GetCSVFIleNames(Root)

    for each FoundFile in filelist( Root & '\*.csv')

        FileList:

            Load

                '$(FoundFile)'          as [FilenameWithPath]

                 Autogenerate(1);

    next FoundFile

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

        call GetCSVFIleNames(SubDirectory)

    next SubDirectory

end sub

Call GetCSVFIleNames('W:\Root\ParentFolder\') ;


talk is cheap, supply exceeds demand
Not applicable

Re: Loop through root directory (including subfolders) and list filenames of *.csv

Worked like a charm - thanks man.

Just a follow up, would you know how to list the file path from the filename when listing in qlikview?

I.e.

Instead of W:\Root\Parent Folder\File1.csv

I would like W:\Root\Parent Folder\ and File1.csv listed separately in different columns:

File1.csv

Thanks again

Re: Loop through root directory (including subfolders) and list filenames of *.csv

try:

filename('$(FoundFile)') as FileName

filedir('$(FoundFile)') as FileDir


talk is cheap, supply exceeds demand
Not applicable

Re: Loop through root directory (including subfolders) and list filenames of *.csv

Hi Gysbert,

Based on your above post, I now have the below codes:

Code.JPG

When I reload it, I get error message pointing to the 2 line that you suggested.

Any idea?

Thanks again

Re: Loop through root directory (including subfolders) and list filenames of *.csv

Ah, yeah. Fields in a load statement need to be separated by a comma. So this should work:

LOAD

    filename('$(FoundFile)') as FileName,

    filedir('$(FoundFile)') as FileDir

Autogenerate(1);


talk is cheap, supply exceeds demand
joey_lutes
Contributor

Re: Loop through root directory (including subfolders) and list filenames of *.csv

I'm trying to do a very similar thing -

I've entered the script and run it - seems to run fine with no errors - just not sure where it manifests or how to make use of the data?  There is no table produced in Qlik Sense - Data Model Viewer.

My data structure is specific:

  

   C:\datafiles\20161101

          file1_20161101.csv

          file2_20161101.csv

    C:\datafiles\20161102

          file1_20161102.csv

          file2_20161102.csv

etc.

Once I have a filelist I need to do a few things with it:

For each FoundFile

1)  Extract the date.

2)  if filedate > LastLoadedFileDate,

3)  Load *, FileDate as FileDate

4)  Update vLastLoadedFileDate (set command?)

Next

I need to do that for each file in each directory.

Here's where I am in the loop:

sub GetCSVFIleNames(Root)

    for each FoundFile in filelist( Root & '\*.csv')

        FileList:

            LOAD

    Date(right(FileBaseName(),8), YYYYMMDD) as FileDate, (hoping this works)

                filebasename('$(FoundFile)') as FileBaseName,

                filename('$(FoundFile)') as FileName,

    filedir('$(FoundFile)') as FileDir

  Autogenerate(1);

    next FoundFile

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

        call GetCSVFIleNames(SubDirectory)

    next SubDirectory

end sub

Call GetCSVFIleNames('C:\datafiles') ;

Thoughts?  Thank you very much!

thabo2g5
New Contributor III

Re: Loop through root directory (including subfolders) and list filenames of *.csv

hi ,

i'm trying to loop through log files would the method you described here work ?

Community Browser