import data from two sheets of multiple excel files to one excel file

Question

hello team, please advise how to achieve this

there are 9 excel files, and each excel file has sheets "ABCD" and "1234".  All the column headers in sheet "ABCD" are same in all the excel files and similarly same header names in sheet "1234" in all excel files.

Need to import all those 9 excel files in to one file with two sheets "ABCD" and "1234".

Was successful with "union" tool but was able to import only sheet "ABCD". Didn't know how to get sheet "1234" too in one  go.

For the input we have to specify sheet, so specified sheet "ABCD" so sheet "1234" was left out from all files.

thank you

2021-12-16_23-32-54.png

ueni · Answer

thank you guys - had meeting with Tony and he helped  me fix.

mceleavey · Answer

Hi @ueni ,

I built a couple of tools to do this, as with Excel it may look like the sheets are the same, but they won't be...

I've attached the workflow, basically the first tool reads the sheet names, and the second loads them in. Apply a filter after the first tool to only take the sheets you need.

I hope this helps,

M.

Multi Sheet Import Example 2.yxzp

messi007 · Answer

@ueni,

In this case you can use a dynamic input for the first sheet and another dynamic input for the second one and then create a file with two sheets.

Please take a look on those posts :

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/The-Ultimate-Input-Data-Flowchart/ta-p/20480

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/How-To-Import-Multiple-Excel-Sheets-or-a-Specific-Excel-Range/ta-p/398220

Hope this helps !

Regards