QlikView Automation Series – Unzip & Load Excel Files:

sinanozdemir — Wed, 18 Nov 2015 16:00:07 GMT

We often receive zipped folders with Excel files in them and since QlikView has this awesome capability of implementing VBScript/Macros, why not take advantage of this feature.

The below scripts and attached documents will take you through the steps of automating unzipping and loading Excel files. Firstly, we need to bring the Edit Module by either pressing Ctrl and M or clicking Tools -> Edit Module:

In the Edit Module, we need to type the below VBScript that unzips the zipped folder. Function takes two parameters, Folder and Destination. Folder is the location of the zipped file and Destination is the location where we want to unzip the folder to. One important point is that we are using a function here so that we can return the destination path. The script Unzip = Destination returns the destination folder path. I also had to change the Requested Module Security to System Access and Current Local Security to Allow System Access in order to make the script work:

After this, we also need to handle reading and loading files from the unzipped folder. In the below script, we are calling the Unzip() function and assigning to a variable so that we can use the destination name to read all the files. In the Unzip() function, we are providing two variables, one for the location of the zipped folder and the another one is for the path for unzipped folder. The other two variables; vTempName and vTable are to extract file names and to use them as table names:

To make this more user friendly, we create a dashboard and provide two input boxes so that users can enter the locations of both the zipped and unzipped folders:

Lastly, we assign the reload action to the Unzip & Reload button.

I am attaching the zipped Example file which has two excel files, Locations and Sales as well as the qvw.

Hope this helps everyone.

Thanks.

topic QlikView Automation Series – Unzip & Load Excel Files: in Scalability

QlikView Automation Series – Unzip & Load Excel Files: