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

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

Thu, 15 Nov 2012 11:33:53 GMT

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.

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

Gysbert_Wassenaar — Thu, 15 Nov 2012 11:55:01 GMT

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\') ;

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

Sun, 18 Nov 2012 23:10:52 GMT

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

Gysbert_Wassenaar — Mon, 19 Nov 2012 07:51:05 GMT

try:

filename('$(FoundFile)') as FileName

filedir('$(FoundFile)') as FileDir

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

Wed, 21 Nov 2012 00:25:58 GMT

Hi Gysbert,

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

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

Gysbert_Wassenaar — Wed, 21 Nov 2012 08:21:49 GMT

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);

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

joey_lutes — Thu, 17 Nov 2016 22:42:42 GMT

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?)

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!

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

thabo2g5 — Mon, 13 Nov 2017 09:13:19 GMT

hi ,

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

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

patricesalem — Thu, 27 Jan 2022 14:21:35 GMT

Hello

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

thanks