We recently wrote a blog outlining a method that allows you to take your input source, and write it to the same file as your output.
In this post we are going to show you the opposite, how we can write our output data to the same file as our input data, and it's quite simple!
First things first, as Excel is the only output type that supports 'multiple tables' in a single file, then this will only work if your input source is an Excel file.
With our input data source within our process, we must change one of the configuration options which will allow us to get the the full filepath of our input as an actual data item in our process.
This can be done simply by adjusting option '5' to be 'Full Path', which will result in an additional column being added to the far right of your dataset.
After your input source you should add a block until done tool before any further transformation processes, and most importantly your output data tool. The reason for this is that you don't want Alteryx to attempt to start writing data to a file it is reading from, as this will result in an error and your workflow will stop running.
What you must do is configure your subsequent transformation processes to ensure that this 'FileName' field flows through the entire process to the point at which you want to generate your output (what this means is don't deselect it, and if you are aggregating your data make sure you include it as a 'Group By' field.
Once we are at the point where we are ready to output our data back to the input file, instead of soley using an Output data tool, we must use firstly a formula tool and then an output data tool.
The purpose of the formula is to build the filepath of where we want to output our data too. This is of course the 'FullName' field, but with an alternative sheet name, so the purpose of the formula too is to replace the input data sheet name with our desired output sheet name. In this example we are replacing the sheet name '`Sheet1$`' with 'Output'.
REPLACE([FileName],'`Sheet1$`','Output')
Once we've applied our formula tool it is then a case of configuring our Output data tool so that it writes the data to the path given in the formula rather than the path given in the 'Write to a File or Database' box.
This can be done by checking the option available at the bottom of the output data tool which allows you to 'Take File/Table Name from Field'.
Here we must choose 'Change Entire File Path' and then select the field that we created/updated using the formula tool at the previous step. You can also choose whether to keep the FileName field in your output (you probably don't).
The other thing of importance is the 'Output Options' within the output data tool. We must choose 'Overwrite Sheet'. This method will prevent Alteryx from deleting the entire file (including the input data sheet) before generating the output, instead this will simple delete out 'output' sheet should it already exist and then recreate it.
We hope this is useful and you can download the example used here.
Ben