2 Replies Latest reply: Nov 18, 2013 1:41 PM by Jerome GODARD RSS

    How to LOAD data from a variable File Name ?

      Hello

       

      I need too determine the data source file from a repository in wich there will be many source file with the same filename pattern. The only part of the name wich change is a block with the date yyyymm > fileNamePrefix_yyyymm_fileNameSuffix.ext

       

      I think it could work load the file information with a loop

       

      FOR each File IN filelist '../FOLDER/fileNamePrefix_*_fileNameSuffix.ext'

      fileTable:

      LOAD FileName('$(File)') as Filename,

           FileBasename() as FileBasename,

           FileExtension() as FileExtension,

           FileDir() as FileDir,

           //FilePath() as FilePath,

           FileSize('$(File)') as FileSize,

           FileTime('$(File)') as FileTime

           FROM '$(File)';

      next File

       

      then I wanted to determine the most recent file with a variable:

       

      SET mySourceFile = MaxString(Filename);

       

      data:

      LOAD *

      FROM [../FOLDER/$(mySourceFile)]

       

       

      Wich doesn't work... even if I can get the right value of the variable mySourceFile displayed in a text Box.

       

      I guess there is a way to evaluate the variable that I didn't understand... I tried a lot of option but didn't find the solution.

      May someone help me to fix this little issue ???

       

      best regards

      Jerome

        • Re: How to LOAD data from a variable File Name ?
          Stefan Wühl

          Unfortunately, you can't just use an aggregation function on a field like this.

           

          You would need to use a LOAD statement and (most of the time) a GROUP BY clause.

          Or you can sort your table descending and pick the first record:

           

          FOR each File IN filelist '../FOLDER/fileNamePrefix_*_fileNameSuffix.ext'

          fileTable:

          LOAD FileName('$(File)') as Filename,

               FileBasename() as FileBasename,

               FileExtension() as FileExtension,

               FileDir() as FileDir,

               //FilePath() as FilePath,

               FileSize('$(File)') as FileSize,

               FileTime('$(File)') as FileTime

               FROM '$(File)';

          next File


          TMP:

          LOAD Filename RESIDENT fileTable ORDER BY Filename desc;

           

                  LET mySourceFile = peek('Filename',0,'TMP');

           

                 DROP TABLE TMP;

           

          data:

          LOAD *

          FROM [../FOLDER/$(mySourceFile)]