Do you use Alteryx in a language other than English? If so, we want to hear from you! Please help us improve the international experience of our products by participating in this 5 minute survey.

We are updating the requirements for Community registration. As of 7/21/21 all users will be required to register a phone number with their My Alteryx accounts. If you have already registered, you will be prompted on your next login to add your phone number.

Alteryx Designer Discussions

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

Loop through files in a directory, select specific columns and then append

JMart2135
8 - Asteroid

I am currently pulling in files using a directory tool with a wildcard on the file  name and then feeding that to a dynamic input tool. An issue that has come up is that the number of overall columns can vary from each xlsx, however the main columns that I want will always exist- those other columns can be dropped.

 

Using the current method, starting at the top of the files in the folder, they will all be brought in unless the schema doesnt match the first file that was read in.

 

I am thinking i need to be able to loop through each file, select the main columns that i know exist in each from that specific sheet, then append them as those columns are extracted.

 

Any ideas on what I might need to do to get this working? The above was just my thoughts on what i think i need to do, please feel free to recommend other options.

 

Thanks for the help!

4 REPLIES 4
Luke_C
13 - Pulsar

Hi @JMart2135 

 

If you turn this into a batch macro you could definitely use a select tool to curate the fields, otherwise in the interface designer of the batch macro there are a few options that can handle this scenario. I personally find myself using the 2nd option frequently, which acts similar to a union tool. 

 

Let me know if you need help with how to make a batch macro

 

Luke_C_0-1622743485214.png

 

mceleavey
16 - Nebula

Hi @JMart2135 ,

 

I've built some tools to do exactly what you want.

I've attached them both long with an example workflow.

The first tool reads in the sheet names of all files in the directory or sub-directory, and then appends them to the filename, so you can filter down the sheets you want in case the humans change them over time. The second tool then dynamically reads them all in and gets around the error you will receive in the Dynamic Input tool if the schema differs. You then simply need to select the columns you want using a select tool.

 

Hope this helps.

 

M.

JMart2135
8 - Asteroid

 @Luke_C attached are the files we discussed

Luke_C
13 - Pulsar

Hi @JMart2135 

 

See attached. I put the steps you use to clean/select the columns into the batch macro. The control parameter is based on the full path input from the directory tool. It dynamically updates the input tool for each file in the directory location. Hope this helps.

 

Luke_C_1-1622763219455.png

 

 

 

 

Luke_C_0-1622763204727.png

 

Labels