Clear the output file before running a macro and setting up an order

[SeidlaSiggi]

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. 

[ChrisTX]

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

[SeidlaSiggi]

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

[ChrisTX]

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

[SeidlaSiggi · Accepted solution]

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.