How to consolidate input files into different sheets of a single output file?

[Satyaki04]

1. I am performed data modification on few input files.

2. These input files will be consolidated into a single output file but in different sheets.

3. Is it possible to rename the sheets?

[BrandonB]

You can use the option to take file name from field in an output data tool to write the data to different sheets based on the values in a column. Is this the functionality that you are looking for? Alternatively, you can use a block until done tool and separate output data tools specifying different sheets in an xlsx file. 

[Satyaki04]

I will break down the requirement for you.

1. I have 5 input files with a single sheet in all of them.

2. I have performed modifications in each of them.

3. All these modifications will be consolidated into a single output file. So, there will be a single output file for the 5 input files.

4. When this is done, i will rename all the sheets.

Please let me know if there is any query.

[Satyaki04]

I will break down the requirement for you.

1. I have 5 input files with a single sheet in all of them.

2. I have performed modifications in each of them.

3. All these modifications will be consolidated into a single output file. So, there will be a single output file for the 5 input files.

4. When this is done, i will rename all the sheets.

Please let me know if there is any query.

[KylieF · Accepted solution]

@Satyaki04

A potential route you can go to accomplish this would be to place a formula tool at the end of each data stream when you're ready to merge the data for output. This formula tool would create a new column, named the same across all five streams, and just contain a string name like 'Dataset 1', 'Dataset 2', ect. This would allow all the records from that file to have that unique ID in the same column after being joined. Then in the output data you would select an excel data type and check 'Take File/Table Name From Field' and select the Label column, you could also uncheck 'Keep Field In Output' to make sure that Label field doesn't make it into the excel.

This article outlines the output set up.

Hopefully that helps!