We’ve extended Inspire Early Bird Pricing until March 1. Register now and enjoy 20% off conference passes and 10% off training passes. P.S. Don’t forget to bring friends! When you sign up for five or more tickets, you get an extra 20% discount on conference passes. Learn more now.

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

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

SeidlaSiggi
6 - Meteoroid

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. 

4 REPLIES 4
ChrisTX
15 - Aurora

Are you writing out to Excel?  If yes:

 

I use the attached macro, which includes an option for Delete File.  

ChrisTX_1-1669649503433.png

 

 

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:

ChrisTX_0-1669649343554.png

 

 

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
6 - Meteoroid

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
15 - Aurora

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 toolOutput anchorInput for this toolInput anchor
1

Text Input tool (dummy file)

 

under the Questions tab, check the box for Delete File

the single output anchorfirst copy of the macrotop input anchor
2the specific tool that contains the data for your first Sheetthe single output anchorfirst PBUD tool#1 input
3first copy of the macrothe single output anchorfirst PBUD tool#2 input
4first PBUD tool#1 outputfirst copy of the macrobottom input anchor
5first PBUD tool#2 outputsecond copy of the macrotop input anchor
6the specific tool that contains the data for your second Sheetthe single output anchorsecond PBUD tool#1 input
7second copy of the macrothe single output anchorsecond PBUD tool#2 input
8second PBUD tool#1 outputsecond copy of the macrobottom input anchor
9second PBUD tool#2 outputthird copy of the macrotop input anchor
10the specific tool that contains the data for your third Sheetthe single output anchorthird PBUD tool#1 input
11third copy of the macrothe single output anchorthird PBUD tool#2 input
12third PBUD tool#1 outputthird copy of the macrobottom input anchor
13third PBUD tool#2 outputfourth copy of the macrotop 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
6 - Meteoroid

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. 

 

SeidlaSiggi_0-1674134395377.png

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.

 

 

Labels