This post will illustrate how to pull a set of files from a directory AND underlying subdirectories as necessary and load all of the files into our QlikView script. Note: The code basis of this post came from a document posted on the Qlik Community by Jagan Mohan.
I get this question all the time:
“Can’t you just pull get QlikView to Load All Files from this directory? “
There are times when we might want to pull several files into QlikView. Some typical examples:
- A selection of related qvds, like if a qvd is created for every month and stored.
- A series Log files with a .txt or .log extension.
- .csv or .xls files that are separated for one reason or another.
- a series of xml files retieved from a file store or maybe even from the web.
The goal of this post is to illustrate a quick trick to pull any set of tabular files into a QlikView app. The seemingly difficult part is that often times, files can be distributed into many folders and subfolders. For example, if we look at the QlikView Distribution Logs, you will find that the document logs are split daily and then further separated by document name and time. So although the logs are contained in one root, they are split into several levels of subdirectories.
Creating manual paths for every subdirectory would not be practical. Instead we will create a QlikView File Loop: a subroutine that creates the loops needed to fish down to every level of that root directory and load the files into our QlikView data model.
The Solution: QlikView File Loop
You can retrieve a complete copy of this script here: Looping Files Example.qvw
First, create a variable to store the concatenate function. We require this because we want it basically inserted into the load statement after the first time it is run. That way, we ensure that subsequent files get concatenated to the original table.
Next, we create a subroutine that will pull all the files out of a single directory. There are a few “for each” loops in this. The first one serves to identify the extension (or even part of the file name if needed) so that if we want .txt files, we are only pulling those and not other file types. Note here, I have used “DocumentLog.txt” because the distribution service logs also include task logs which I do not want to retrieve at this time.
for each Ext in ‘DocumentLog.txt’
The second loop identifies all the files within the specific directory that will be loaded.
for each FoundFile in filelist(Root & ‘\*’ & Ext)
Then, we add the load statement for the files. Obviously the “from” statement would be adjusted here to match the type of file you are loading. Here it is set up to pull QlikView DocumentLog files. Also note that because we are adding in the “concatenate” variable, the QlikView syntax checker goes off. No need to worry, the script will run fine (provided no other issues).
load * from [$(FoundFile)](txt, utf8, no labels, delimiter is ‘\t’, msq, no eof);
set Concatenate = concatenate;
Now we call the subroutine from within the subroutine itself to cycle through each subdirectory it finds. The key function here is “dirlist” which gives a list of all the subdirectories below the root.
for each SubDirectory in dirlist(Root & ‘\*’)
Finally, it is time to actually run this subroutine in the script. This line will execute the subroutine for the specified root. All the folders under this will be checked for DocumentLog.txt files.
Call ScanFolder(‘C:\QlikView Application Folders\DistributionService\1\Log’);
You can find the complete script here.
When the script is run, it will find any document ending in DocumentLog.txt and load into QlikView regardless of where it lies in the directory structure under the root.
Obviously, we haven’t gone into the parsing of this data to make it usable within QlikView. We can save that for another day.
Here are some other great articles that deal with QlikView File Loops:
Your comments and feedback are welcome.