Dynamic inputs, different schemas, single output with filename

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

GB 01.atype.xlsx

Combined SL actype inputs.xlsx

PE 01.atype.xlsx

GB 03.01.atype.xlsx

ET 01.atype.xlsx

Datasets

Dynamic Processing

Join

Database Connection

Accepted answers

CharlieS

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.

I hope this helps!

20200211-BatchExcel.yxzp

All comments

CharlieS

Hi @Bigmonki

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.

I hope this helps!

20200211-BatchExcel.yxzp

Bigmonki

@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.

Hi2023

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

SYSTEM2.xlsx

SYSTEM1.xlsx

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703