16 Replies Latest reply: Jan 28, 2016 3:30 AM by Settu Periyasasamy RSS

    loops in script

      Hi All,


      Today while referring to Henric post on Loops in the Script, I came to know that we can load data from excel file whose names are listed in a Tables using below script.


      For vFileNo = 1 to NoOfRows('FileListTable')

            Let vFileName = Peek('FileName',vFileNo-1,'FileListTable');

            Load *,

               '$(vFileName)' as FileName

            From [$(vFileName)];

         Next vFileNo


      Now, In may case I want to load data from those excel files whose names are not listed in the table.


      Where do I need to do the changes to achieve output as per my requirement.


      Thanks in advance




        • Re: loops in script
          Marcus Sommer



          For vFileNo = 1 to NoOfRows('FileListTable')

               trace $(vFileNo);

                Let vFileName = Peek('FileName',$(vFileNo)-1,'FileListTable');

               trace '$(vFileName )'; // checking in log-file and process-window

                Load *,

                   '$(vFileName)' as FileName

                From [$(vFileName)] (ooxml, embedded labels, table is [YourTable]); // could be also biff-format

             Next vFileNo


          - Marcus

          • Re: loops in script
            Jonathan Dienst

            >>load data from those excel files whose names are not listed in the table

            So how do you know which files to load? Perhaps you mean all the files in a folder except those listed? You could use something like this:

            For Each vFile in FileList('e:\data\*.xlsx')

                 Let vFileName = SubField(vFile, '\', -1);

                 If Not(Exists(FileName, vFileName)) Then

                      ...perform load

                 End If

            Next //vFile

            (adapt for the correct file path and name mask in FileList)

            • Re: loops in script
              Naresh Guntur

              You create a mapping table with the list of files you don't want to load.


              Mapping load * inline [


              A1.txt, 0

              A2,txt, 0

              A3.txt, 0



              And you load all the files with a where condition at the end.



              where applymap('FilterFiles',FileName,1)=1;

              • Re: loops in script
                Tamil arasu

                I guess, the file extension causing the problem, Try something like below.


                Load distinct FileName, Now() as LoadDate
                Resident Volume;
                For Each vFile in FileList('*.csv')
                Let vFileName = Replace(SubField(vFile, '\', -1),'.csv','');
                If Not Exists(FileName,vFileName) Then
                Load *,
                '$(vFile)' as FileName,
                Date(Mid('$(vFileName)',Index('$(vFileName)','y')+2,Index('$(vFileName)','.')-4-Index('$(vFileName)','y')+2)) as Date
                From [$(vFileName)];
                End If
                Next vFile