0 Replies Latest reply: Feb 2, 2016 7:06 AM by Revathy Shanmugam RSS

    Check on the file names

      Hi,

       

      Many a time we get requirements in QlikView to read the contents of a folder, loop through a folder or  process only files that match certain pattern. It might be a little tricky, but can be achieved with the help of variables and functions. Here, I am using a simple function to read the file names in a folder. This function can be further amended to add more functionality.

       

      Function to loop through a folder :

       

      sub ScanFilename(FilePath)

             for each FoundFile in filelist(FilePath& '\*.' & FileExtension)

       

                       FileNames:

                       LOAD

                       distinct filename('$(FoundFile)') as Filename

                       FROM [$(FoundFile)] (ooxml, embedded labels);

       

           next FoundFile

       

      end sub

       

      Call ScanFilename('..\..\data\');


      The variable 'FileExtension' will contain the file extension types that you want. You can add/remove a file extension from this list. Only files with extensions in the list will be read. The variable 'FoundFile' will contain the file name.


      If I want to process files that have a certain pattern in the file name, say a date appended at the end. I now want to read those files that have a valid date. E.g., If the filename has a pattern like xxx MMDDYYYY.xlsx. I use the same function with a where clause. The subfield() function returns the substring that is before the '.xlsx'. The Date#() function is used to check if the substring returned is a valid date.


      Function to check the file name pattern :

       

      sub ScanFilename(FilePath)

             for each FoundFile in filelist(FilePath& '\*.' & FileExtension)

       

                       FileNames:

                       LOAD

                       distinct filename('$(FoundFile)') as Filename

                       FROM [$(FoundFile)] (ooxml, embedded labels)

                        where isnull(Num(Date#(right(subfield(filename('$(FoundFile)'), '.xlsx',1),8),'MMDDYYYY')))=0;

       

           next FoundFile

       

      end sub

       

      Call ScanFilename('..\..\data\');


      Similarly, more conditions can be included in the where clause to check for pattern match.


      Now, we have a list of file names to be processed after all the checks. The next step will be to load these file names into a mapping table.


        Files:

        Mapping

        Load

        Filename,

        Filename as Fromfile

        Resident FileNames;

       

      We create another function that loops through all the files in the folder and only process the files in the mapping table.

      sub ScanFilename(FilePath)

             for each FoundFile in filelist(FilePath& '\*.' & FileExtension)

       

                  Check:

                  Load

                  filename('$(FoundFile)') as fromfile

                  FROM [$(FoundFile)] (ooxml, embedded labels);

       

      A variable is used to store the file name. Using the match() function the read file name is checked with the file names in the mapping table(contains a list of all file names that have passed the initial checks), if there is a match, the file is processed.

       

        Let vFilename = peek('fromfile',0,'Check');

       

      If(match('$(vFilename)',ApplyMap('Filenames','$(vFilename)','missing'))) then

      .

      .

      //Load the tables

      .

       

      End If

       

      Thank you.