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

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!

Workflow

Tips and Tricks

Preparation

Accepted answers

ChrisTX

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

Append to existing YXDB file and Create file if Not Exists on first run.yxmd

mkeiffer

@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.

Workflow to Append to Existing Alteryx Database.yxmd

All comments

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

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.

See attached workflow.

Chris

Append to existing YXDB file and Create file if Not Exists on first run.yxmd

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

Workflow to Append to Existing Alteryx Database.yxmd

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

Workflow to Append to Existing Alteryx Database V2.yxmd

mkeiffer

@ChrisTX Were you able to access and download my updated workflows? Let me know your thoughts on the approach and also if the workflows worked for you. Thanks!

ChrisTX

Hi @mkeiffer

Thanks for posting a workflow without control containers. One of the goals for my workflow was an automated process. If you're ok with some manual work, your workflow is definitely easier.

One comment for the second container where you're reading from and writing to the same YXDB file: you may run into write-contention issues. The Union tool is defined as "conditional blocking", so Alteryx "may" try to write to the output file before the input file is completely read.

Check out The Periodic Table of Alteryx tools
https://community.alteryx.com/t5/Engine-Works/The-Periodic-Table-of-Alteryx-tools/ba-p/64120

I noticed your YXDB file name included "BKPF_BSEG_APPEND". I'm working on a project now to expose our SAP data to Alteryx. Which tools are you using?

We are using IICS (Informatica Intelligent Cloud Services), reading our SAP database directly. I can describe a few of our challenges with this tool, if you're interested.

Chris

mkeiffer

@ChrisTX Thanks so much for the comments and for posting the link to the Periodic Table of Alteryx Tools. I had never seen that before, so that will be very helpful.

The solution that I developed for appending the Alteryx tables actually is fully automated and does not require any manual intervention. In the actual solution where i am using what I posted, I am actually running one workflow as an analytical app and another workflow as a batch macro. The part where the empty .YXDB file is created in the analytical app and the part where the actual appending takes place is in the separate batch macro. I was also a bit concerned about not using the Block Until Done tool, but unfortunately, that tool cannot be used inside a Control Container. So far, I have not had any issues with it not working with the Union Tool but that is something I am keeping an eye on.

One of the biggest challenges that I am facing is I am using Theobald and Xtract Universal to get to the SAP data, but unfortunately, with the Theobald software, you cannot join the BSEG and BKPF tables because BSEG is a cluster table in SAP. Because of how big the BSEG table is, I had to come up with another creative approach to get the BSEG data but also manage the large sizes of the file.

I would be very interested to hear of the challenges you are facing with the IICS tool and any other interesting Alteryx tips you might want to share. Thanks, Chris!

ChrisTX

We looked at the two available tools to obtain SAP data (DVW and HVR), but chose IICS. The two main challenges I've seen with IICS are:

the data type in our data lake sometimes doesn't match the original SAP data type (Date in SAP may be translated to DateTime, etc)
IICS is missing functionality to correctly place the decimal point. Example for BSAK_PSWBT: SAP "displayed" value = 530,405.00. Value stored in our data lake = 5,304.05. But this "error" does not happen for every row. It only happens if the related currency code is a foreign currency. The old ACL Direct Link tool handled the decimal placement correctly. But our new IICS tool does not. And Informatica doesn't have any plans to implement the functionality.

Chris

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703