Output multiple files with multiple sheets based on columns

[pratap19]

Hi experts,

I am looking for a help.

I am doing one project and struct with the below issue.

The Data: 

Grouping Complex	Location	Pay Scale Group	Pay Scale Level	Rate	Increase Amt	Increase Percent	New Rate
Van Buren Plant	Location A	BG01	01	16.04	0.46	0.028678	16.50
Van Buren Plant	Location A	BG01	35	16.14	0.46	0.028501	16.60
Van Buren Plant	Location B	BG01	01	16.24	0.46	0.028325	16.70
Van Buren Plant	Location B	BG01	35	16.85	0.46	0.028325	16.70
East Texas Live	Location D	BG01	01	15.9	0.5	0.031447	16.40
East Texas Live	Location E	BG01	35	16.1	0.5	0.031056	16.60
East Texas Live	Location F	BG01	40	16.2	0.5	0.030864	16.70

The requirement:

The flow needs to create multiple workbooks based on column "Grouping Complex" and each such work book should contain multiple sheets based on column "Location".

In this case for example, there will be 2 workbooks.

1) Van Buren Plant   -   contains 2 sheets

                                     1. Location A - 2 records

                                     2. Location B - 2 records

2) East Texas Live   -   contains 3 sheets

                                      1. Location D - 1 record

                                      2. Location E - 1 record

                                      3. Location F - 1 record

PFA data file. Thanks in advance.

[Felipe_Ribeir0 · Accepted solution]

Hi @pratap19 

Please see the attached workflow. The secret here is to build a output filename with the formula and take this filename to use as location on the output tool. With this logic you will be able to specify exactly which excel file/sheet name each record must be written.

If the provided answer helped you to solve the problem/was correct, please accept it as a solution :)

Thanks.

[Emmanuel_G]

@pratap19 

Find attached the way of doing that.

Please do not hesitate to mark this answer as solution if it worked.

[pratap19]

Hi,

I am getting below error while running the flow.

Could you  please help me with this?

[Felipe_Ribeir0]

Hi @pratap19 

1)You must see if the path that the formula tool is building is making sense. It must be always like that: FolderPath\Filename.xlsx|||SheetName.

2)You must see if the configuration of the output tool is correct.

Take a look again at my or @Emmanuel_G workflow, once you get the idea it will be easy to replicate :).

[pratap19]

Hi Felipe,

Thanks for your help. I am able to update the path and create the output. But I found out that output files are not in excel format. PFB screenshot.

Output tool configuration:

 Please help me on this.

Thanks,

Pratap

[Emmanuel_G]

@pratap19 

Did you put the extension .xlsx and specify the sheet after the ||| in the Output field which will be used as the file path in the output tool?

[pratap19]

Its resolved finally. Thank you