2 Replies Latest reply: May 2, 2017 6:35 AM by Jonathan Dienst RSS

    Add data from a folder

    Martin Yranzo

      Hello everyone!! A few days ago I have joined this community to which, every day, I am surprised more and more.

       

      Wanting to do a test with my data I have found that I can not overcome this question that I tell you below.

       

      I have a network disk folder a series of files, XLS, CSV and TXT that always have the same name and in case of being updated, they are replaced by a file with the same name. These data are used as dimensions.

        

      But I have a folder where month to month files "report_2017_01" for January, "report_2017_02" for February and so on each month.

       

      These files always have the same column structure and are therefore added one after another.

        

      So far I have not found a way for Qlik to add me the data without having to touch the Data Model by hand.

       

      Qlik can not solve it?

        

      For the doubts I tell you that the way I could solve it was by using the PowerQuery (Excel add-in) which, if I told you to take data from a folder and find files that have the same structure, join them all in a single table . Got this what I do is for Qlik to read that table. :-( but I do not like anything.

       

      A big greeting for the whole community.

      Martin.






       

        • Re: Add data from a folder
          Rahul Pawar

          Hello Martin,

           

          You can refer below given sample script:

           

          //This will load all the files which begins with report_ name
          Data:
          LOAD Id,
          ...
          report_*.xlsx
          (ooxml, embedded labels, table is [Sheet1]);
          

           

          Hope this will be helpful.

           

          Regards!

          Rahul

          • Re: Add data from a folder
            Jonathan Dienst

            You can use a wildcard load or a loop to load and concatenate multiple files with a similar structure. So for your monthly data files, you could load like this:

             

            Fact:

            LOAD 0 as Dummy Autogenerate 0;

             

            Concatenate(Fact)

            LOAD *,

              FileBaseName() as Source

            FROM ...\*.xls

             

            DROP Field Dummy;

             

            If the date is not in the file, then you could parse the file name (in FileBaseName()) for the date; something like:


            Date(Date#(Mid(FileBaseName(), 8, 7), 'yyyy_MM')) as Date,