Read data from multiple excel with different sheet name and schema

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.

Workflow

Accepted answers

binu_acs

@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

Directory (2).yxzp

All comments

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/ta-p/20480

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)?

Batch Macro Workflow.yxmd

Batch Correct.yxmc

new.yxmc

binu_acs

@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

Directory (2).yxzp

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?

binu_acs

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

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703