Read data from multiple excel with different sheet name and schema

[Pandey19]

Hi,

I've a folder where i've around 20 excel files with different names. And the tab/sheet name inside the excel is also different in all files.

Some file has same schema while some are different. Some file has 1 tab while some has multiple tab (again with different name)

Is there any generic way to read all these files combined?

I've followed multiple post in Community but i'm not able to get my solution.

[ChrisTX]

Since some schema are different, you'll need to use a batch macro.  See this Knowledge Base article:

The Ultimate Input Data Flowchart
Community > Designer > Designer Knowledge > The Ultimate Input Data Flowchart
https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/The-Ultimate-Input-Data-Flowchart/t...

Chris

[Pandey19]

@ChrisTX 

So i followed the link and created the same workflow and macro. Its not giving me all the records.

But if i use the workflow attached in the link, its giving me correct output

Do you know what is going wrong in my macro(new.yxmc)?

[binuacs · Accepted solution]

@Pandey19 I think you need two batch macros The first one is to extract all the sheet names from the existing excel and the second one is to combine one into one sheet. Can you try the attached macro 

[zhengtianxiang]

Are two macros totally same?

[zhengtianxiang]

Hi,

Thanks for your sharing. It really helps a lot. I tried your workflow, but found that the fields are combined together if they have same name and in different sheets. I want to aviod this. Do you know how to solve this?

[binuacs]

@zhengtianxiang The above macro gives you the output; it does not write the output on different sheets. Can you explain what you are exactly looking for?