9 Replies Latest reply: Aug 1, 2015 3:07 PM by Digvijay Singh RSS

    Load script taking ages

    Martijn Noorda

      Dear Qlikviewers,

       

      I have written a load script that should load all filenames in all the subfolders. I believe there should be approximately 600 csv files in all of the subfolders. I believe this script should do the job. However, the load script seems to be running for ages. The script has been running for an hour and is still going. Is there anything about my script that could be altered to make it run more efficiently?

       

      SET ThousandSep='.';

      SET DecimalSep=',';

      SET MoneyThousandSep='.';

      SET MoneyDecimalSep=',';

      SET MoneyFormat='€ #.##0,00;€ -#.##0,00';

      SET TimeFormat='h:mm:ss';

      SET DateFormat='D-M-YYYY';

      SET TimestampFormat='D-M-YYYY h:mm:ss[.fff]';

      SET MonthNames='jan;feb;mrt;apr;mei;jun;jul;aug;sep;okt;nov;dec';

      SET DayNames='ma;di;wo;do;vr;za;zo';

       

       

      sub GetCSVFIleNames(TRD)

          for each FoundFile in filelist(root & '*.csv')

              FileList:

                  Load

                      filename()          as [FilenameWithPath]

                       Autogenerate(1);

          next FoundFile

          for each SubDirectory in dirlist(root & '\*' )

              call GetCSVFIleNames(SubDirectory)

          next SubDirectory

      end sub

       

      Call GetCSVFileNames('L:\my\file\location\');

        • Re: Load script taking ages
          Jordi Arenas

          Hi,

           

          At first time don't forget to insert it in a qvd.

           

          Regards

          • Re: Load script taking ages
            kushal chawda

            sub GetCSVFIleNames(TRD)

                for each FoundFile in filelist(root & '*.csv')

                    FileList:

                      First 1  Load

                            filename()          as [FilenameWithPath]

                             Autogenerate(1);

                next FoundFile

                for each SubDirectory in dirlist(root & '\*' )

                    call GetCSVFIleNames(SubDirectory)

                next SubDirectory

            end sub

             

            Call GetCSVFileNames('L:\my\file\location\');

            • Re: Load script taking ages
              Puttemans Johan

              This looks ok. Beware that you not only fetch data, but also rename variables in the same go. This will take somewhat longer.

               

              If it can help you, I sometimes have QV-routines running for 3 days. These then involve the lecture & selection of variables within up to 80.000 txt files.

              • Re: Load script taking ages
                Ruben Marin

                Hi Martijn, maybe changing "root" to "TRD" (the parameter)

                sub GetCSVFIleNames(TRD)

                    for each FoundFile in filelist(TRD& '*.csv')

                        FileList:

                            Load

                                filename()          as [FilenameWithPath]

                                 Autogenerate(1);

                    next FoundFile

                    for each SubDirectory in dirlist(TRD & '\*' )

                        call GetCSVFIleNames(SubDirectory)

                    next SubDirectory

                end sub

                 

                Call GetCSVFileNames('L:\my\file\location\');

                • Re: Load script taking ages
                  Sasidhar Parupudi

                  Hi your input variable differs

                  sub GetCSVFIleNames(root)

                      for each FoundFile in filelist(root & '*.csv')

                          FileList:

                              Load

                                  filename()          as [FilenameWithPath]

                                   Autogenerate(1);

                      next FoundFile

                      for each SubDirectory in dirlist(root & '\*' )

                          call GetCSVFIleNames(SubDirectory)

                      next SubDirectory

                  end sub

                  • Re: Load script taking ages
                    kushal chawda

                    Are all CSVs having Same name & Same Field name in sheet?

                    • Re: Load script taking ages
                      Sasidhar Parupudi

                      sub GetCSVFIleNames(TRD)

                       

                          for each FoundFile in filelist(TRD & '*.csv')

                       

                              FileList:

                       

                                

                                  Load

                                      '$(FoundFile)'         as [FilenameWithPath]

                       

                                       Autogenerate(1);

                       

                          next FoundFile

                       

                          for each SubDirectory in dirlist(TRD & '\*' )

                       

                              call GetCSVFIleNames(SubDirectory)

                       

                          next SubDirectory

                       

                      end sub

                       

                       

                       

                      Call GetCSVFileNames('L:\my\file\location\');

                      • Re: Load script taking ages
                        kushal chawda

                        any luck?

                        • Re: Load script taking ages
                          Digvijay Singh

                          Below script also works perfectly, you can modify as per your need. This one loads all files and subfolder information with Q extension.Source - QV developer cookbook.

                           

                          Sub GetFiles(vPath)

                           

                           

                          // Get a list of files with Q extensions

                          For each vFile in FileList('$(vPath)\*.Q*')

                            Files:

                            Load

                            '$(vPath)' as Folder,

                            '$(vFile)' as File,

                            FileSize('$(vFile)') as FileSize,

                            FileTime('$(vFile)') as FileTime

                            AutoGenerate(1);

                          Next

                           

                           

                          End Sub

                           

                          Sub GetSubFolders(vPath)

                           

                           

                          For each vDir in DirList('$(vPath)\*')

                           

                            Folders:

                            Load '$(vDir)' as Folder

                            AutoGenerate(1);

                           

                            Call GetFiles('$(vDir)');

                            //Recurse to get sub folders;

                            Call GetSubFolders('$(vDir)');

                           

                           

                          Next

                          End Sub

                           

                           

                          Call GetFiles ('C:\Program Files\QlikView');

                          Call GetSubFolders('C:\Program Files\QlikView');