FileName_trouble

[datanibbler]

Hi,

I generally have the file_paths to the data_files underlying my QlikView_apps in variables, loaded from qvs_files.

My issue now is: In some cases - as in this specific instance - I have used an * in the file_name because it encompasses a date_stamp that changes once in a while - the list is replaced by a new one which, however, looks exactly the same.

Now I have two files in the same location which have the same file_name but for the very last part before the ending.

<=> Due to the *, that diffference is not considered by QlikView.

I am just trying to parse that directory in a FOR EACH loop and find out exactly which files are in there. That, however, showed me another problem: The files are sometimes saved as .xls and sometimes as .xlsx - so I cannot clearly define the LOAD.

=> Is there any way to flexibilize the LOAD so that it can work on the older .xls files as well as on the newer .xlsx files?

Thanks a lot!

Best regards,

DataNibbler

[Peter_Cammaert]

Do you have examples of the main categories of file names you are trying to discern?

In the meantime, just a few tips:

• For xlsx and xls, you'll need two different LOAD statements, due to the difference in options.
• If you embed dates in file names, fill them out at all times, including leading zeroes. Programmatically, it's much easier to parse dates like 01_01_2015 and 12_04_2015, than stuff like 1022015 (is it the tenth or the first of february?)
• If you can, do use ?-wildcards instead of *-wildcards. The *-wildcard character is often too greedy, and I guess that's exactly what you are dealing with right now.

Best,

Peter

[Gysbert_Wassenaar]

Is there any way to flexibilize the LOAD so that it can work on the older .xls files as well as on the newer .xlsx files?

Yes, you can add a for each loop for fileextensions. Unfortunately the * wildcard is too greedy so another check is needed. Something like this:

SET vSheet = 'Sheet1';

sub ScanFolder(Root)

     for each FileExtension in 'xls', 'xlsx'

          switch  '$(FileExtension)'
               case 'xls'
                    SET vType = 'biff';
                    SET vSheetSuffix = '$';
               case 'xlsx'
                    SET vType = 'ooxml';
                    SET vSheetSuffix = '';
               default
                    exit script;
           end switch

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

               if  ('$(FileExtension)'='xls' and right('$(FoundFile)',1) = 's') or '$(FileExtension)'='xlsx' then
                    MyTable:
                    LOAD *, '$(FoundFile)' as SourceFile
                    FROM [$(FoundFile)] ($(vType), embedded labels, table is [$(vSheet)$(vSheetSuffix)]);
              end if

          next FoundFile

     next FileExtension

end sub

call ScanFolder('d:\qvdata\temp\comm156242');

SET vType ='';
SET vSheetSuffix =;