Read in multiple excel sheets (different schema) without union-ing them

Question

Hi. I have a use case which requires the workflow to read in multiple sheets from the same excel file. Specific fields from these respective sheets then need to be converted/transformed via the Formula tool before being joined to form a new sheet. Most of the solutions with regards to this issue requires building a batch macro which will eventually require unioning these sheets even though they have different schemas. Is there a way to:

1) Save on the use of multiple input tools

2) Not union these sheets and be able to read them in separately (eg. filter by sheet name after the batch macro)

Thank you so much!

Qiu · Answer

@mzbz90 
as @apathetichell suggested, I believe we can achieve you intend here.

We can also out the sheet inside the macro.

I will be helpful if we can have a set of sample intputs and output.

apathetichell · Answer

Can you explain why you don't want to use the batch macro to read in the sheets and union? It seems from your description that the easiest way is to:

1) read in file/sheetnames.

2) send to batch macro with schema updater/sheet updater.

3) drop all the fields you don't want.

4) add a field for sheetname.

union and exit.

You can (and should in your case) drop the fields you don't need inside the batch macro. Otherwise Python? I don't know of a better way to accomplish this in Alteryx.