Hi everyone,
I have the following problem:
I have a macro which reads several input files, transforms them and appends the results to an output file. Before running this macro, I would like to clear the output file to avoid duplicates. This can be easily done be reading the output file, using a sample tool and output it again. The latter is an independent workflow which is not connected to the macro as it should run only in the beginning, not after every batch. My question would be, how can I define the order of these two processes? When I click on "run", the batch macro starts to work, afterwards the output file is cleared. It should be the other way round.
I would appreciate any kind of help.
Thanks in advance.
PS: Simply "overwrite the sheet" in the output tool is not an option as the macro processes one file after another rather than all the files together.
Solved! Go to Solution.
Are you writing out to Excel? If yes:
I use the attached macro, which includes an option for Delete File.
If you're writing to Excel sheets and want the sheets in a specific order, you can add the CReW macro Parallel Block Until Done.
Here's an example:
Related Idea, if you'd care to add a Like: https://community.alteryx.com/t5/Alteryx-Designer-Ideas/Enhance-options-for-Excel-output/idi-p/84326...
Chris
Hi Chris,
thanks for your fast reply. I had a look on it and I'm not quite sure, how to apply it. Could you describe what to do here a little bit more in detail, please? How exactly can I order the two processes?
Thanks,
Yannik
Sure. The first screenshot I posted just shows that there is a Delete File option under the macro Questions tab. Use that option only once, when you write out the first sheet.
In the second screenshot:
To write out 3 separate Sheets to one Excel file, in order:
- the data must be in 3 separate data streams. Each data stream must contain a field like "FullPath", to specify the full path to the Sheet, and the data in each row of the FullPath field must have the same value, and must be a value like this: \\my folder 1\my subfolder 2\my file name.xlsx|||My Sheet Name. When you configure each macro, choose your FullPath field for the macro Question drop-down box for "Choose Field: OutputFullPath".
- take the macro I posted above, and add 3 copies of the macro to your canvas
- add 3 copies of the CReW Parallel Block Until Done macro to your canvas
- add one Text Input tool to your canvas (a dummy file, with one field and one row), to feed into the first macro call. I named the field "Field1" and the value in the first row is "Just need one record here". You can name the field anything, and the first row can have any value. Just make sure you have at least one data row in the dummy file.
abbreviation for below:
PBUD = a single copy of the CReW Parallel Block Until Done macro
"copy of the macro" = a single copy of the macro "Excel Output different formats to multiple Sheets in one file.yxmc", posted above
Linking the tools together on your canvas:
Step # | Output from this tool | Output anchor | Input for this tool | Input anchor |
1 | Text Input tool (dummy file)
under the Questions tab, check the box for Delete File | the single output anchor | first copy of the macro | top input anchor |
2 | the specific tool that contains the data for your first Sheet | the single output anchor | first PBUD tool | #1 input |
3 | first copy of the macro | the single output anchor | first PBUD tool | #2 input |
4 | first PBUD tool | #1 output | first copy of the macro | bottom input anchor |
5 | first PBUD tool | #2 output | second copy of the macro | top input anchor |
6 | the specific tool that contains the data for your second Sheet | the single output anchor | second PBUD tool | #1 input |
7 | second copy of the macro | the single output anchor | second PBUD tool | #2 input |
8 | second PBUD tool | #1 output | second copy of the macro | bottom input anchor |
9 | second PBUD tool | #2 output | third copy of the macro | top input anchor |
10 | the specific tool that contains the data for your third Sheet | the single output anchor | third PBUD tool | #1 input |
11 | third copy of the macro | the single output anchor | third PBUD tool | #2 input |
12 | third PBUD tool | #1 output | third copy of the macro | bottom input anchor |
13 | third PBUD tool | #2 output | fourth copy of the macro | top input anchor |
This task shouldn't be this hard. Can you click the Like button for this Idea, to encourage Alteryx to provide an easier solution:
Enhance options for Excel output - Alteryx Community
Chris
Hi Chris,
first of all I'm very sorry for not replying earlier. Thank you very much for this detailed description.
In fact, I have found a very simple solution to my particular problem. However, not by defining an order, as I had requested above. Here is a brief explanation:
The input tool is the beginning of my macro, one file at a time is read in. The select tool can be ignored. In the formula tool, assign the name for the sheet into which the output is to be written. In the filter tool, filter according to the FileName of the first file which is read in. The additionally generated column in the formula tool prevents both output tools from being executed.
An additional column remains in the dataset, but this is not a problem for me.
If there is a more elegant solution, feel free to share it.