Skip to end of metadata
Go to start of metadata

Sometimes the task is not that easy. The pattern might change, e.g. read all files matching the pattern "plan_[BOBJ:todays date].txt", or you want to rename the files after successful load. There is no direct support for that but a workaround - you can use parameters as filename as well.
So the idea would be to

  • use a function to get the first filename
  • while a filename exists loop
  • within the loop call the dataflow with that filename to load and afterwards rename the file and find the next filename - if there is one

The first step is to create a Workflow to hold the entire logic. Inside that workflow a variable is created called $filename to hold the next filename to be processed.


In a first script we use the DI 11.7 function wait_for_file() to set the variable $filename to the first file matching our pattern or NULL if there is none. As the function does either use '\' or '/' a path separator char with the latter being the default setting in the DSConfig.txt, we use a replace_substr() function to change it back to backslash. The DI internal functions can deal with either character as path separator, but we will use exec() to run a msdos command later.


And now we use a while loop to call a dataflow reading the file repetitive unless there are no more files ("$filename is not null").


In the dataflow - a "procedure" of its own - we want to use the value of the local variable of the workflow as well. So we need to define a parameter for the dataflow by clicking on the variable window when viewing the dataflow and define it. For clarity I called it $df_filename.


Now the dataflow requires a parameter and everywhere you call that dataflow "procedure" you need to apply a value. Where do we call it? In the workflow. So go back to the workflow, open the calls tab of the variable window and provide a value - the value of $filename.


Then we can use the source file name $df_filename in the dataflow's objects, in our case as a filename. But watchout, the variable is used in the file reader call, not in the file reader class. So open the object inside the dataflow to set the variable, not form the object library -> file formats as there you define the object itself. Not the call properties. Obviously you could have used global variables to avoid that passing along of variables but...


Last object is another script for the post processing. If a dataflow did load a file successfully, we want to rename the file into something else. Today there are still no native functions in DI so we have to go via the exec function.

exec('cmd.exe', 'move [$filename] [$filename].old', 0);

And finally we reset $filename to the next available filename inside the while loop.


The function wait_for_file() has the syntax wait_for_file(filename pattern, timeout, poll interval, how many file names should be returned, a variable holding the returned filename list, returned size of list, list separator char).
In our case we do not need the last two optional parameters. We want the function to return immediately (timeout=0), as we will not poll multiple times the poll interval can be anything, we want just one file to be returned and into the variable $filename.

wait_for_file('c:\\temp\\plan_[year(sysdate())]*.txt', 0, 0, 1, $filename);

Note: This entire chapter is based on the DI 11.7 function wait_for_file(). But you can get the same result by setting the variable using

$filename='c:\\temp\\' || word(exec('cmd.exe', 'dir /b c:\\temp\\plan*.txt', 0), 1);

  • No labels