Looping thru directory adding sheet to each file

[D3Baker]

Hi

I am new to alteryx. I need to Loop thru several excel files in a directory, create a new sheet  called Settle on each file in the directory. I have created a workflow where I can pull in one file and join to my other data to populate a new sheet called settle. But I need this to be done for all the files in a directory. Can anyone assist?

[Hannah_Lissaman · Accepted solution]

Hi @D3Baker 

You can achieve this with a very simple batch macro. A batch macro basically means 'do the thing inside the macro for each row in this input', so we can give it your list of files and it will repeat an action for each.

I have attached an example. If you would like to see what is happening inside the macro, right click and select 'Open macro'.

The key steps are as follows:

• In a blank workflow, build a very simple workflow with an input data (the data for the sheet you want to add) and an output which adds a new sheet named 'Settle' to one of your target Excel files
• Right click your input and select 'Convert to macro input'
• Add a Control Parameter tool and change the name to 'File Path'
• Click and drag from the ? anchor on the bottom of the Control Parameter to the lightning bolt symbol on top of your Output tool. This will automatically add an Action tool in between.
• Configure your action tool to 'Update value', and select the file path
• Tick the 'Replace a specific string' option at the bottom, and remove the bit at the end of the file path where it says '|||'Settle$' - this stops it from overwriting the sheet name.

• It should now look like this:

• Save your new macro!
• Open a new Alteryx workflow, and add the data for your 'Settle' sheet as an input
• Add a Directory tool and select the folder where your files are stored
• Connect the Directory tool to the ? input on the macro (this is the Control Parameter), and connect your Settle data to the other input
• In the macro configuration, do not select a 'Group by' field and in the second tab, select 'Full Path' for your File Path dropdown. 
• Run and this should add the sheet to each of your files!

I recommend trying this out on a folder of test files before you use it on your real ones, and to keep a backup just in case.

You can find out more about Batch Macros here: https://community.alteryx.com/t5/Videos/Build-Your-First-Batch-Macro/td-p/52900

[D3Baker]

ok thank you. I will give it a try

[Hannah_Lissaman]

Good luck! If it works for you, please can you mark as a solution so others can find it?

[D3Baker · Accepted solution]

thank you this is helping me get my feet wet in the areas of working with Macros. My final goal really is to add this settle sheet to every file, but I need to join to another file to populate the settle sheet. so I would have to join on each file in directory with another file  and populate the settle sheet with the appropriate data.

hope that makes sense

Again this has been very helpful

Debbie