Create a QlikView File Loop to Load all Files

LivingQlikView Post Featured Image - QlikView File Loop - a stack of physical files

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 Problem

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.

FileSystem
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.

set Concatenate=;

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.

sub ScanFolder(Root)

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

FileList: 

$(Concatenate)

load *  from [$(FoundFile)](txt, utf8, no labels, delimiter is ‘\t’, msq, no eof); 

set Concatenate = concatenate;

next FoundFile

next Ext

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 & ‘\*’)

call ScanFolder(SubDirectory)

next SubDirectory

end Sub

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.

 

Conclusion

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.

Resulting Looped Table

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:

QlikView Maven – Loading All of the Files from a Folder

Qlik Show – Loading all QVD’s in a folder

Your comments and feedback are welcome.

5 comments… add one
  • Reply Steve Dark July 15, 2014, 7:17 pm

    Hi Aaron,

    Thanks for this, I like the idea of putting the task into a Sub routine.

    You may want to see my post on looping through CSVs, and creating a QVD copy of each so that on subsequent runs only new files need to be parsed.

    http://www.quickintelligence.co.uk/convert-drop-folder-files-qvd/

    Steve

  • Reply Aaron Couron July 16, 2014, 2:59 pm

    Combine these two concepts of 1. Looping through directory structure and 2. Using qvd to speed efficiency/incremental load and you’ve got a killer app. Thanks for the comment, Steve.
    Aaron

  • Reply Jafreisy January 20, 2015, 4:55 pm

    Great information. What happens when the txt files have different parameters?
    from [$(FoundFile)](txt, utf8, no labels, delimiter is ‘\t’, msq, no eof); // parameters here might change depending on the files you are loading

  • Reply Thabo Moeketsi November 16, 2017, 9:22 am

    hi informative blog you have I’ve learned a lot on this blog. i wanted to know if this
    “Obviously, we haven’t gone into the parsing of this data to make it usable within QlikView. We can save that for another day.” has been done yet. i’m struggling with this.

    thank you in advance

Leave a Comment

Join us at Living QlikView

Never miss a post.

We Never Spam. NEVER.

Show Buttons
Hide Buttons