Write Output into Input file

[LinhNguyen]

Hello,

Is there a way I can write my output into the file I use as Input?

For example, I'm using an excel file as Input and would like to write my output into the same file in a different sheet, do I need to manually choose that file or is there a way I can just update my input file automatically? Will there be an error because I'm using it in the same workflow?

Thanks!

[MarqueeCrew · Accepted solution]

@LinhNguyen ,

You can use a BLOCK UNTIL DONE tool just before your output or just after the input.  Now everything is ready for a read and write.

Cheers,

Mark

[LinhNguyen]

Hi Mark!

Thanks so much for the quick respond! That will solve my problem when I write the output. 

Is there any way to automatically write my output in whichever file i use as an input, without having to specify that file?

Thank you!

[rafalolbert · Accepted solution]

Hi LinhNguyen,

Building on MarqueeCrew's spot-on solution, a small addition from me to answer your latest question is in attached workflow file.

You can derive file name or the entire path by selecting 'Output File Name as Field' - options are: 'Full Path', 'File Name Only' or default: 'No'.

You can use Formula tool to manipulate the value to point to a new sheet, please notice the specific format for Excel output as: file name with extension followed by "|||" and sheet name like: "input.xlsx|||Sheet1"

Have fun and all the best!

Thanks,

Rafal

#Excuse me, do you speak Alteryx?

[LinhNguyen]

That was awesome, Rafal!

What if I do not know the name of the input sheet? With this workflow I will need a fixed input sheet name ('sheet 1' for example) in order for the formula to work, but is there a way to write that output in the correct path without knowing the input sheet name?

Thank you!

[rafalolbert · Accepted solution]

Hi LinhNguyen,

Updated workflows are attached.

I've used Regex tool to dynamically get the input tab name: .+\|\|\|(.+) - this is extracting anything that follows 3x pipes (this is a reserved characted and thus needs to be escaped).

Following this a Formula tool is replacing the above value with new tab name.

All this should be achievable within a single Formula tool and one of the Regex function, but I always prefer to use dedicated tool as it's more explicit.  

Thanks,

Rafal

[ColinG]

My coworker tried using a block until done when using a dynamic input from a file, that he then wants to write the output to. Unfortunately, it doesn't seem to work in connection with a dynamic input, probably because of the iterative nature of a dynamic input. He's looking at other options. Right now he's thinking of writing the output file to a different file name and then using a batch script to delete the old file and rename the new file. But we were wondering if there was another more elegant solution. Control containers can do it I believe, but we aren't able to upgrade to the Alteryx version that has control containers for another month.