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

    loops in script

    Priyanka Pandey

      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

       

      Regards

      Priyanka

        • Re: loops in script
          Marcus Sommer

          Try:

           

          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.

              FilterFiles:

              Mapping load * inline [

              File,Flag

              A1.txt, 0

              A2,txt, 0

              A3.txt, 0

              ];

               

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

              Load....

              .....

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

              • Re: loops in script
                Tamil Nagaraj

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

                 

                ExcelNames:
                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