topic Re: FileName_trouble in QlikView

FileName_trouble

datanibbler — Mon, 16 Mar 2015 14:50:41 GMT

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

Re: FileName_trouble

Peter_Cammaert — Mon, 16 Mar 2015 15:03:42 GMT

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

Re: FileName_trouble

Gysbert_Wassenaar — Mon, 16 Mar 2015 16:07:21 GMT

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 =;