topic Re: How to regroup multiple excel files by sheet names? in Alteryx Designer Desktop Discussions

How to regroup multiple excel files by sheet names?

tseo — Wed, 11 Nov 2020 09:59:29 GMT

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

Re: How to regroup multiple excel files by sheet names?

AkimasaKajitani — Wed, 11 Nov 2020 10:41:48 GMT

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.

Re: How to regroup multiple excel files by sheet names?

tseo — Wed, 11 Nov 2020 12:43:38 GMT

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!

Re: How to regroup multiple excel files by sheet names?

AkimasaKajitani — Wed, 11 Nov 2020 13:00:27 GMT

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.

Re: How to regroup multiple excel files by sheet names?

AkimasaKajitani — Wed, 11 Nov 2020 13:52:15 GMT

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.