Input columns from multiples sheets

[VMR]

Hi - I am just trying to figure out the tool that helps me to pick specific columns from multiple sheets from 1 excel file and give me 1 output file consolidating that specific column. ex: A file contains sheet A, B & C have many columns. Those few columns have customer ID and Customer name but in different positions. I have to find a way to select ID and name from all three sheets and consolidate and provide one output. It is difficult for me as the sheets are not in the same format. Any help is appreciated, thanks!

Added example file and output sheet to see the expected output.

[afv2688]

Hello @VMR ,

Could you share with us an example file with some mock data? Just to have an idea about the whole problem.

Regards

[VMR]

Hi - added one example file. 3 sheet and one expected output sheet.

[rakshabhat]

Hi @VMR,

Normally this would work well if you had the same schema for all the worksheets you use. 

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/How-To-Import-Multiple-Excel-Sheets...

Since you dont, I would suggest you to take a look at the workflow I did. It gets you the output you desire, but it involves you selecting each worksheet one by one. 

Hope that helps. 

Thanks,

Raksha

[VMR]

Hi @  rakshabhat

Thank you! Yeah, I know this select and union step but the difficult part is I have nearly 20+ sheets in one excel file. 
Is there a way to do in standard macro or Batch macro ??!!

[afv2688]

Hello @VMR ,

I have prepared a macro that merges all the sheets together.

Regards

[afv2688]

I'm adding also this just in case you want to merge more than 1 file (on the same path).

Regards

[VMR]

Thanks, ACEAFV2688 - 

This could help a lot. Can you give me a short video of creating this macro to help is my other works too. Thanks in advance!

[afv2688]

Hello @VMR ,

Rather than a video I prefer to explain it here.

1. The macro reads the list of sheet names for each file.
2. Using regex I replace the <List of sheet names> parameter with the actual sheet
3. With the help of a batch macro, I replace the templates for the dynamic input and the file location to read it.
4. A select tool is placed then on the end to get the columns needed.
5. The formula tool within the macro adds the sheet name.
6. After the process, data is then merged together (like in an union) using the names for it (should the names be different then it would not have worked). To configure this parameter you need to acces the interface designer (Cntl + Shift + d)

Hope this helps

Regards