Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Dynamic inputs, different schemas, single output with filename

Bigmonki
8 - Asteroid

Hello,

 

I am struggling, mainly due to lack of knowledge with Alteryx. The community pages and the people on here are brilliant, so far most of my challenges have been solved through these pages or people helping with a specific issue. I am now stuck and again ask for assistance!

 

I have tried several of the Dynamic links to achieve my output, several workflows and macros, even the CReW Macro for Wildcard XLSX input, they all seem to leave me with an incomplete solution to my needs which is a solution I would need to employ on most of my workflows.

 

Attached are a selection of files that I need to bring in from a directory, combine and output with a Filename so I can identify where the data came from. Basically, I need to take all of the "atype" files as a data input, combine the data (different schema) then output to the "Combined" inputs workbook (this file is an example of what I need to achieve). Everything I have tried either separates out the file with additional columns, adds an additional Header row or just excludes the data. 

 

Any help would be appreciated

4 REPLIES 4
CharlieS
17 - Castor
17 - Castor

Hi @Bigmonki 

 

This project definitely has a few tricky parts, but it is achievable! The first and most important part of this is that the field schema (field layout) is different between the files, a simple wildcard input will not work. In these cases, we can use a batch macro to input each file and make the necessary modifications so it will union with all the others. The output from the batch macro includes union-ing the outputs from each batch.

 

The easy part is to use a Directory tool to find the Excel files that end with "atype.xlsx". Next, we'll add the sheet name "atype" to each path so we're ready to feed these file input values into a batch macro.

 

Now that we're setting up a batch macro, we need to design it in a way that modifies each file so the same process will output exactly what we want. In my batch macro, you'll see the following:

- Determine if the first row need to be skipped by looking at the first value. If it's "TYPE", then using a Dynamic Rename tool to use that first row of data as the field names.

- Use Dynamic Select tools to only allow the fields of interest to be passed.

 

20200211-atypeExcel.png

 

 

 

 

 

 

 

 

 

 

I hope this helps!

Bigmonki
8 - Asteroid

@CharlieS 

 

What can I say, thank you seems so insignificant as a gesture of my appreciation for this solution. 

 

I am really grateful that you took the time to have a look, I really need to work on understanding batch macros. This fix will help me with about another 20 workflows.

 

Once again, thank you.

CharlieS
17 - Castor
17 - Castor

@Bigmonki 

 

I'm happy to help and glad it works for you. We're all here to learn and grow together.

Hi2023
8 - Asteroid

Hi, 

 

I have had the same issue! I tried to download to see how exactly it was done but it wont let me download.

 

I have multiple files that I have to download and combine with the control# and only add Sheets 1, 2, 22, 3 NOT 4. There is diff schema and when I tried wildcard and batch macro it didnt work.

 

 i have provided a few test samples.

 

Any suggestions?

 

thanks

Labels