Looping through files in a folder


After quite some (busy) months, here is again a new TM1 article. It will show an example of the WildcardFileSearch function in Turbo Integrator.

Loops in the file system

Looping through files in a folder or through folders is sometimes needed in programming applications. TM1 is capable to do so if we use the WildcardFileSearch function in a TI process. The result within an While … End loop will be a unique list of file names in a folder, matching a certain pattern if needed. For instance, we can loop through the files in the database directory of a TM1 model and by doing this, get a list of all processes. Now, one can ask the benefit of doing this kind of exercise. Here are a number of usages:

  • looping through subsets or views, since there are no TM1 control objects storing subsets or views.
  • executing (generic) processes for a bunch of input files that we collect in a folder. IT might export files on a daily basis, of which the file names are unknown or even the number of files might be unknown.
  • clean up purposes or backup purposes

Sample TI code

Copy these lines of code in the Prolog or Epilog tab of a TI process:

vFile = WildcardFileSearch('*.pro', '');

While( vFile @<> '');
     AsciiOutput('processes.cma', vFile);
     vFile = WildcardFileSearch('*.pro', vFile);

I like this version of the WildcardFileSearch code the best, for a number of reasons (including, the mask is written only once in the code, just as the WildcardFileSearch construct):

vFile = '';
While( 0 < 1 );
  vFile = WildcardFileSearch( '*.pro', vFile );
  If( vFile @= '' ); Break; EndIf;
  AsciiOutput( 'processes.cma', vFile );

If you save and execute the process, the result will be a list of all process files that reside in the database directory. WildcardFileSearch uses an iterative approach to go by each file, and in the case above we limit ourselves to process files (*.pro). You can also use another path to loop through, which could also be part of a system variables cube.

Within the loop, you can then do whatever you want with the file names. It could be view names or subset names or even rules files. We could make some views public whenever they are private. And I am sure the reader will find other interesting usages of the code. Add if statements to skip certain file names if needed. Also, take a safe approach and use the ViewExists or SubsetExists function in TI. Or, you could have a loop over input files, and ExecuteProcess to a second process that loads the file (use DataSourceNameForServer to adjust the data source in a dynamic way).


Section contents

About Wim

Wim Gielis is a Business Intelligence consultant and Excel expert

Other links