How to regroup multiple excel files by sheet names?

tseo

Hello,

I'm quite new to Alteryx and have a problem dealing with multiple excel files.

File 1 : Company A.xlsx - sheet1, sheet2, sheet3.... sheet10

File 2 : Company B.xlsx - sheet1, sheet2, sheet3.... sheet10

File 3 : Company C.xlsx - sheet1, sheet2, sheet3.... sheet10

File 4 : Company D.xlsx - sheet1, sheet2, sheet3.... sheet10

File 5 : Company E.xlsx - sheet1, sheet2, sheet3.... sheet10

And I would like to regroup sheets into like;

File1 : Sheet1.xlsx - Company A, Company B, Company C, Company D, Company E

File2 : Sheet2.xlsx - Company A, Company B, Company C, Company D, Company E

File3 : Sheet3.xlsx - Company A, Company B, Company C, Company D, Company E

File4 : Sheet4.xlsx - Company A, Company B, Company C, Company D, Company E

File5 : Sheet5.xlsx - Company A, Company B, Company C, Company D, Company E

File6 : Sheet6.xlsx - Company A, Company B, Company C, Company D, Company E

File7 : Sheet7.xlsx - Company A, Company B, Company C, Company D, Company E

File8 : Sheet8.xlsx - Company A, Company B, Company C, Company D, Company E

File9 : Sheet9.xlsx - Company A, Company B, Company C, Company D, Company E

File10 : Sheet10.xlsx - Company A, Company B, Company C, Company D, Company E

Any tips or ideas for this kind of task????

Input

Common Use Cases

Dynamic Processing

Preparation

Iterative Macro

Accepted answers

AkimasaKajitani

I fixed the Excel Load macro that output the schema information at each sheets.

And I created an Excel save macro that saves only the fields you need by giving the schema information.
It may not work well with field names that contain hyphen.

In Excel Save Macro the Field that each sheet have only pass to output tool by Dynamic Select.

The function is the below.

Contains("_a_b_c_","_"+[Name]+"_") or [Name]="OutputPath"

Action tool replace "_a_b_c" by control parameter input.

Sample_different_schema2.yxzp

All comments

AkimasaKajitani

Do the all sheets have the same schema?

If the all sheets have the same schema, you can read the all data by using one batch macro.

And at output tool, you can save the required excel files by using "Take File/Table Name From Field" option.

Please refer attached file.

If the schema is different each sheets, I recommend to branch out by sheet name in before the macro.

Sheets with same number are same schema, for example;

Sheet1s from Company A~E have same schema, Sheet2s from Company A~E have same schema and so on...

Would your workflow work for such case???

Thanks a lot for help!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703