Use Output Tool to Create a File Only If File Does Not Exist

[mkeiffer]

Hi there,

I am trying to find a way to build a workflow where my data is output to either an Excel or an Alteryx database but only if the file does not exist.  Is there any way to conditionally execute the Output Data tool, but only if the file does not exist.

I have tried using the Directory tool and the FileExists function, but I am still not getting my desired results.

Ultimately, I am trying to append data to the same output file.  Ideally, I would like to use an Alteryx database for this but you cannot append to an Alteryx database.  I am trying the approach of using a SQLite file type (you can append to this) but if you try to configure the output using SQLite to Append Existing, you will get an error if the table does not currently exist. 

Hopefully, this is not too confusing but if anyone has a solution of how to create a file using the Output Tool, but only if the file does not exist, that would be greatly appreciated!

Thanks in advance!

[caltang]

If the file doesn't exist, then data does not pass through. So without any data passing through you'll hit a stoppage point. Perhaps you can add a Count Records tool after your input & formulas, if there is no file the count will return 0. You can add a filter to say that if it is 0, then output this file. 

Try and see.

[ChrisTX]

Alternative to a true "append" for an Alteryx YXDB file:

  Read in the existing YXDB file, use a Union tool to combine the current YXDB records and your new records, then a Block Until Done tool, then use an Output tool to overwrite the same YXDB file.

Chris

[mkeiffer]

@ChrisTX  Hey Chris, great to see you in the Alteryx User Community (Maveryx).  I have seen others make the suggestion of reading in the existing YXDB file and then using the Union Tool to combine it with the current YXDB records.  My challenge, however, is on the very first run of my workflow, the existing YXDB file does not yet exist.  I am going to be using this workflow as part of a batch macro and I have not found a viable solution to have the existing YXDB file be created so I can use it with the Union tool.  My workflow is going to be to send the output of my first result to create the initial YXDB file and then append my subsequent results with this file.  

Do you have any suggestions on how I can create this initial file?  I have tried a couple of different approaches with Control Containers and the Directory Tool but so far, I have not found a viable solution.  

Any additional help or suggestions would be appreciated.  Thanks in advance for your help! 

[ChrisTX · Accepted solution]

Hi Michael.  Didn't notice your user name earlier.

So you basically need to create an empty YXDB if the file doesn't already exist.  If it does exist, append to it.

I had a few notes from earlier posts about how to do this.  But trying to get it to work completely was quite a challenge.  There has to be a better/easier way to do what you're asking.  But this is the best I could come up with.

See attached workflow.

Chris

[mkeiffer]

@ChrisTX  Thanks again for the quick response and for sending this workflow.  After I responded to your post yesterday, I did come up with a fairly easy solution to create the empty .YXDB file and everything seemed to work okay.  I will definitely check out your workflow as well and see how it compares to mind.  I hope at some point in the future, Alteryx will add an Append Option to the output of date to an .YXDB file.  Seems like that request comes up a lot.

Also, I found this other related article that also addresses the problem and it is a bit more straightforward and less steps.  You can also actually send the output of results to an SQLite file and there is an append option with this data type.  The only tricky part is  you have to manually code (in SQL) the table layout structure as there is an option to create the table initially, if it does not exist the first time you run it.  I think SQLite files are bigger than .YXDB files, but another good approach.  Here is the link to the article:

http://community.alteryx.com/t5/Alteryx-Knowledge-Base/Append-to-YXDB/ta-p/10610

Thanks so much for your help, Chris!

[ChrisTX]

Can you share your fairly easy solution to create the empty .YXDB file?

[mkeiffer · Accepted solution]

@ChrisTX  Hi Chris, here is my workflow that I came up with.  I didn't attach any practice date files with it but I added a comment and some notes in the annotation for how to use the workflow.  Let me know how it works for you and if you have any questions about it.

[ChrisTX]

@mkeiffer Thanks for sharing.

I have an older Alteryx version, so I can't see the tools inside the Control Containers.  I'm guessing your second Control Container is set to execute when there are zero records found in the stream originating from your Directory tool?

That's a huge benefit of Control Containers, being able to run tools when upstream tools don't have any data.  My more complex logic seemed necessary to ensure we always had at least one record continuing down the stream, to prevent the entire workflow from essentially "stopping" when a single tool doesn't have any output records.

Chris

[mkeiffer]

@ChrisTX Hi Chris,

Here is an updated version of the workflow will all of the tools in regular containers.  I would run the container on the left first, and then disable and run the second container.  Definitely will be curious for your thoughts regarding my approach.

Also, I would definitely recommend upgrading to the latest version of Alteryx and to start incorporating Control Containers in your workflows.  They are definitely a game-changer!  Hope you have a great weekend!

Mike