4 Replies Latest reply: Apr 7, 2017 3:30 AM by beck bakytbek RSS

    Q: How to write script to automatically load multiple Excel files in a folder?

    Long Nguyen

      Hi everyone, I'm very new to using QlikSense on Desktop, and would like to learn how to use the data load script. Specifically:

      1. The files I am working with:

      • I have a report that is generated hourly, in .xlsx files. I would like to write the script so that QlikSense can automatically load all the report files (which I will place in a common folder).
      • Each of the original data will looks like this:

      Qli.PNG

      Row 1 = Name of column group. This I may need to get rid of when load the data since I have no use for them.

      Row 2 = Name of field. This will be what I want to load data by.

      Row 3 on ward = Data

      • Each of the original data will have name something along the line of View_[Date]_[Hour], ie: View_20170320_2000. On any given day, I can expect to get 12 files like this (update every 2 hour, data is cumulative), and 5 days per week.

       

      2. What I want to do:

      • Auto load them from a folder.
      • For each file load, I need to include 1 column for year-month, 1 column for date, 1 column for hour, ie: 2017-Mar, 2017-03-20, 22:00. These is taken from each file names.
      • Bonus: Given the large amount of data this can quickly come to, I was told it can be improved by converting the .xlsx file to .qvd, so that each time I open the app and load new data in, each previous data files are already processed.

       

      3. Please note that I'm fairly new to QlikSense, so please "teach me how to fish" - explain to me step by step (ie: Step 1 - Convert data to QVD file; Step 2 - Load data dynamically; Step 3 - How to use the file name to make up new field) and the syntax if possible. I've searched around and see that several others have asked similar question, but I can't understand the answer given, so please "don't just give me the fish, cause I don't know how to cook that fish".  

       

      Your time and help are much, much appreciated!!