Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Gysbert_Wassenaar

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

View solution in original post

8 Replies
Gysbert_Wassenaar

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
Author

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

Gysbert_Wassenaar

try:

filename('$(FoundFile)') as FileName

filedir('$(FoundFile)') as FileDir


talk is cheap, supply exceeds demand
Not applicable
Author

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

Gysbert_Wassenaar

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
Partner - Creator
Partner - Creator

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

hi ,

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

patricesalem
Creator II
Creator II

Hello

 

same question for me - have you finally found a way round ?

 

thanks