community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Include Excel Sheet name in output dataset using Dynamic Input tool

Meteor

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

Alteryx
Alteryx

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

 

dynamic-input.png

 

 

Meteor

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

Atom

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."

 

 

Alteryx Partner

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

Alteryx Partner

@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"

 

 

 

 

 

image.png

 

 

 

how to do this when we have muliple sheets 

 

Alteryx
Alteryx

@PoojaSingh88 

 

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

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.

Alteryx
Alteryx

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?

Labels