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

[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.

[tseo]

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!

[AkimasaKajitani]

When the schema is different at each sheets as you said, you have to branch by Filter tool by each sheet before load excel files.

Please refer attached file.

If there are too many sheets and it's difficult to increase the number of branches, you'll need a different logic.

[AkimasaKajitani · Accepted solution]

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.