input tool enhancement to check file exists and allow iterative attempts to open if locked

[fharper]

Please enhance the input tool to have a feature you could select to test if the file is there and another to allow the workflow to pause for a definable period if the input file is locked by another user, then retry opening.  The pause time-frame would be definable for N seconds and the number of iterations it would cycle through should be definable so you can limit how many attempts to open a file it would try.

File presence should be something we could use to control workflow processing.  

A use case would be a process that runs periodically and looks to see if a file is there and if so opens and processes it.  But if the file is not there then goes to sleep for a definable period before trying again or simply ends processing of the workflow without attempting to work any downstream tools that might otherwise result in "errors" trying to process a null stream.

An extension of this idea and the use case would be to have a separate tool that could evaluate a condition like a null stream or field content or file not found condition and terminate the process without causing an error indicator, or perhaps be configurable so you could cause an error to occur or choose not to cause an error to occur.

Using this latter idea we have an enhanced input tool that can pass a value downstream or generate a null data stream to the next tool, then this next tool can evaluate a condition, like a filter tool, which may be a null stream or file not found indicator or other condition and terminate processing per the configuration, either without a failure indicated or with a failure indicated, according to the wishes of the user.  I have had times when a file was not there and I just want the workflow to stop without throwing errors, other times I may want it to error out to cause me to investigate, other scenarios or while processing my data goes through a filter or two and the result is no data passes the last filter and downstream tools still run and generally cause a failure as they have no data to act on and I don't want that, it may be perfectly valid that on a Sunday or holiday no data passes the filters.

Having meandered through this I sum up with the ideal being to enhance the input tool to be able to test file presence and pass that info on to another tool that can evaluate that and control the workflow run accordingly, but as a separate tool it could be applied to a wider variety of scenarios and test a broader scope of conditions to decide if to proceed or term the workflow.

[fharper]

Additional thought but specific to Dynamic input tool...The above spoke to use of the Input Tool but the Dynamic Input tool can have the same issue in terms of failing for file lock.  However the response for the Dynamic tool should probably be different.  

I have a situation where I want to read process logs which are written by batch files as they run workflows and other programs throughout the day.  

The Alteryx workflow uses the directory to get the list of files, passes the list of files created today into a dynamic input tool which reads them and downstream processes them into a dashboard.  Problem is that sometimes when it runs it tries to open and read a file that is still being written to by the batch file.  When this happens it generates a failure message for the tool and the tool stops processing and then all downstream tools activate accordingly.  I get a failed workflow and incomplete data, there might have been 80 more files down the input list to read at the time the dynamic input tool failed.

In this scenario having an iterative read attempt, like described above for the Input Tool, would be nice but unlike the Input Tool, which reads one input, the Dynamic Input tool may have a list of hundreds of files to read and be stopping at the 5th file.  For this tool and this situation passing a null stream and or message or indicator to a downstream tool is not what I need.  I would need the option to have the tool issue a warning instead of an error and skip the file and move on to the next in the list and keep going.  This way one locked file won't prevent me from reading all the other files and having meaningful output.

While some tools are unlocked, i.e we can open the macro and edit them to create our own version, the Dynamic Input Tool is not.  I expect it is a macro under the covers but I can't tell.  In any case I think the same basic approach for the Input tool can work for the Dynamic Input tool but it needs a little modification to allow one to influence the open read close logic embedded within the Dynamic Input tool rather than waiting for a downstream tool to evaluate as discussed for the Input Tool.  An option to continue processing, or in other terms skip the offending file and continue reading from the list, without generating a failure but warning instead.

Contact me if you have questions but these features are more and more desired the longer I use Alteryx.

[mix_pix]

I fully endorse both of these ideas.  I sometimes run into a situation where a workflow is trying to access a shared yxdb that happens to be in "write" mode (i.e. another workflow is writing to it at the same time as my workflow is trying to read it) and the whole workflow fails because it can't read in that yxdb.  I'd love the ability to have the workflow wait for x period of time and try the file again before failing.

[ARich]

Hi All,

Thanks for the idea- please continue to upvote if you'd like to see this feature.

Best,

Alex

[DavidAngevine]

I like both of these features.  In addition, I would really like a feature on the Input tool that would only use "grant select access" (Obviously, showing my SQL background) and open a file in select or the latest saved version.   This would take the data as is, without any of the current changes that another user was making.   If this could be done, with the input tool, then the workflow would move forward with no issues.   This allows the workflow to run, even with another user.  This works for Excel, when you answer the Pop-up question when you are reading a file that someone else is editing.  

It is better to read the file, for my purposes, and note that time / date stamp with the Timestamp tool than to have the workflow stop.   

[fharper]

not sure what "grant select access" is.  

Never had a problem in SQL because all our work is read only so it is like specifying UR or "Uncommitted Read". and even if we had update I would expect the control is in the hands of the DB and not an Alteryx issue.  my comments were all more targeted toward facilitating a workflow accessing various flat file types, excel and CSV in my case mostly, in a manner as to not cause failure. 

I would like 2 open options here,

1. to be able to specify "Read" access to read a file as it exists on last save, which may be what David is saying, without having to wait for the file lock release because I may not care what is being written to the file concurrently.
2. to be able to pause and retry a file open in a configurable way, N seconds/minutes pause time between attempts and N attempts before fail.  the idea here is I need to write to it and don't want my data wiped out by another process that may close after my update and or I may need the info writing to the file right now and need to wait for it to finisih.  

[DavidAngevine]

Grant Select is some SQL syntax.   Either way, fharper is correct.  I would like Option 1 above.  I had not thought of # 2, but this would be helpful also.

Thanks for clarifying my comment into a clearer request.

[blyons]

In the project I am doing, I am not so concerned about the retry option. I just need to check and see if any new files came in today, and import them if there are any. If not, it should just return an empty recordset.

In my current application, I have an Input tool, configured with a wildcard for a particular "incoming" folder, returning the "<List of Sheet Names>". But, when the folder is empty, it returns a "File not found" error, which I would like to ignore.

[fharper]

An option for blyons is to use a directory tool to get a list of files in the folder that you can analyze to see if there are new files and send the filtered list to a dynamic input or an input within a macro.  You can capture the input filename in the dynamic input tool and use it downstream in an output tool to control where the data is written to keep data segregated into appropriate output files.  If no files no error.  if the input files vary in schema you need to create a simple macro with an input tool and feed the file list to that macro.  this is an easy way to side step dynamically reading multiple files with differing schema.

 

read a file macro example

You can write additional logic to address no data scenarios

[blyons]

Thank's @fharper, yes, that is exactly what I had already decided to do. I just voted for this enhancement because it would have saved considerable time.

[fharper]

cool