Include Excel Sheet name in output dataset using Dynamic Input tool

[MaverickVZ]

I may be overlooking something obvious but this is the first time I'm using the Dynamic input tool and I need to add the name of the Excel Worksheet (Table) to the dataset. My Excel Workbook has 43 tabs each one named by month. "Dec-14", "Jan-15", "Feb-15" etc. Using the Dynamic Input tool I am able to Union all the data into one output but I have no way of telling which sheet it came from for a summary view. I need to bring the Worksheet Name into the data so I can summarize it by month. I don't want to manually add it to each sheet and then import it. Any help would be greatly appreciated.

Thanks,

Pete

[MichalM · Accepted solution]

Hi Pete

You can use configuration of the Dynamic Input tool to append Full Path of the file and sheet as a column to your dataset. When selecting the Input Data Source template, make sure you select 'Full Path' in the 'Output File Name as a Field' option - row 5 in the configuration. You can then extract the sheet name only easily using the Text-to-column tool.

Hope that helps.

Michal

[MaverickVZ]

Michal,

Thank you for the fast response. I was making the mistake of trying to do that in the first step when getting the list of Sheet Names in the Input tool. This makes sense now, thanks again for your help!!

[Bisu]

Hi i am trying to append the sheet name. I used Directory then i connect to Dynamic Input tool. In the input tool i picked <list of sheet Name>  . Could you please suggest me . Sheet Name not showing in Field drop down list. It gives me error msg

"Error: Dynamic Input (11): C_Desktop_Day-1_Day2_ALTERYX TRAINING_Copy of 902-10 NTTEA 2Q Estimate WP 2018 - NEW.xlsx does not match a sheet or named range in C:\Desktop\Day-1\Day2\ALTERYX TRAINING\Copy of 902-10 NTTEA 2Q Estimate WP 2018 - NEW.xlsx or the Excel file is corrupt."

[Derangedvisions]

@Bisu I am experiencing the same issue, did you ever figure it out?

[Derangedvisions]

@Bisu you need to add a formula tool before the dynamic input tool! this fixed it for me (luckily i only have one sheet in all my worksheets...)

Column name: FullPath

[FullPath] + "|||Sheet1"

[PoojaSingh88]

how to do this when we have muliple sheets 

[MichalM]

@PoojaSingh88 

You can use the formula tool to define the sheet name dynamically as well. 

[PoojaSingh88]

I tried @MichalM

[FullPath] + "|||<List of Sheet Names>" for full path before the dynamic input tool.

The tool is reading the sheet names instead of the data within the sheets.

[MichalM]

Could you please explain in more detail what you're trying to achieve here? What's your input and what do you want the output to be?