2 Replies Latest reply: Oct 26, 2011 4:33 PM by kfreakkk RSS

    Load Data from multiple files and different folders

      Hi there, I`m new in QlikView;

      Does anyone know how can I load data from a paradox database splitted in many files into different folders.

      All the transactions files are splitted in months. Each month is represented by one folder. For example for one fiscal year i have

      12 subfolders and 12 invoice files in each subfolder. With the below script I can load transactions from multiple files but i must define in "set folderscope"

      statement the path for each subfolder. In all the cases the number of subfolders cannot be defined and I can`t use the script in current form.

       

      Also i need to read the name of the subfolder. I cannot use the filedir() function inside the SQL statement. Any ideas?

       

       

      ODBC CONNECT TO Paradox;

       

      directory 'D:\DATA\company\';

       

      set FolderScope = 'D:\DATA\Company\2011_05','D:\DATA\Company\2011_06';

       

      FOR each FilePath in $(FolderScope) 

       

                   LOAD *;

       

              SQL SELECT *

       

              FROM $(FilePath)\invoices.db;

       

                            NEXT FilePath;

       

       

      thks in advance

        • Load Data from multiple files and different folders
          Fernando Suzuki

          Hi,

           

          try using a loop with the DirList function:

           

          for each Dir in dirlist ('c:')

           

               'do something here

          next Dir

           

          (found this in the help, using the 'For each..next' keyword)

           

           

          Hope this helps you

          Regards

          Fernando

            • Re: Load Data from multiple files and different folders

              Thx Fernando for the hint

              It is working like this:

               

              FOR each Dir in dirlist ('D:\path\*')

               

                            LOAD *;

                                  SQL select

                                                                *

                                                      FROM $(Dir)\table.db;

               

               

               

                        NEXT Dir

               

              I still have problemes with reading the name of the subfolder...

               

              I tried to use in the load statement filedir() function but it shows me nothing

               

              Any ideas for this issue?

               

              Best Regards,

              Mihai