Splitting a big excel into multiple files based on sheet names.

Question

I have an excel file which has 40 sheet, 10 sheets for sales, 10 sheets for revenue , 10 sheets for Profit 10 other sheets.

the sheets are named as sales1,sales2,sales3.... revenue1,revenue2,revenue3... and profit1,profit2,profit 3... with lots of excel formatting.

I want to split this big excel file and separate all the sales sheet into a separate file which should be named as sales20240723.xlsx ( filename= sales + Workflow rundate) like the same i need separate all the revenue & profit sheets into two other excel named profit20240723.xlsx & revenue20240723.xlsx respectively. Is this doable in alteryx.

Important note : at any cost the source formatting needs to be carried to the files that are getting split.

Experts help me out in this.

apathetichell · Answer

My python code is there. you would need t omake a slight modification.

1) this takes in a full path, original sheet name, new sheet name.

2) you would need to include fullpath2 (new file).

3) you would follow how I create a unique DF - and create a final unique df for fullpath2

4) you would create new active.spreadsheets for each file in fullpath2

5) instead of copying the sheet to the original file - you'd copy it to fullpath2.

6) you'd point the extra sheet test logic with the remove to the new file.

Alteryxexpert · Answer

Will this work if all the sheets have different formats? which code i need to use precisely to achieve the above requirement?

apathetichell · Answer

You cannot do this natively in Alteryx.

Try my python based sheet copier macro?

https://github.com/apathetichell/2024_AlteryxMacros