Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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\') ;
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\') ;
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
try:
filename('$(FoundFile)') as FileName
filedir('$(FoundFile)') as FileDir
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
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);
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!
hi ,
i'm trying to loop through log files would the method you described here work ?
Hello
same question for me - have you finally found a way round ?
thanks