Dynamic Input and excel - what am I doing wrong?

[FDJ]

Hi, I am a beginner to Alteryx. Can someone help identify what is wrong with my workflow? 

Background:

Two excel files within the same directory, same tab name, same schema.

Goal:

Explore usage of directory tool to combine the data of both files

Problem:

Only a single file gets imported into the "Browse" tool. I was expecting both of them to appear.

Workflow:

1. Dir tool, filtering by "*.xl*"
2. Formula, where I specify name of sheet "Report Tab"
3. Dynamic Input: 
   1. If I give the template of file A, the output browser shows the content of this file
   2. If I give the template of file B, output browser shows content of this file
   3. I want the output browser to show the content of both files (not each individually)

[JosephSerpis · Accepted solution]

Hi @FDJ I mocked up a workflow that showcases how to get the dynamic input to bring in multiple files. Let me know what you think?

[markcurry]

Hi @FDJ 

With your formula tool, try appending the Sheet name to the [FullPath] field with the formula..

[FullPath] + "|||`Results$`"

This will create something like C:\Temp\File1.xlxs|||`Results$`  which you need for Excel files.

And then in your Dynamic Input tool use the [FullPath] field.

Hope that helps.

[FDJ]

Hi, this doesn't work as it tries to find a sheet name with the full path (and will not find one). It throws an error.

[FDJ]

This solution works well, I noticed you changed the "Dynamic Input" Action parameter from "Change File/Table Name" to "Change Entire File Path". 

[JosephSerpis]

Hi @FDJ you need to feed into the dynamic input tool the file names and sheets so that it reads in the two excel files. I amended my workflow so that it will dynamically pick up the sheet names and append it to the file name and then feed that into the dynamic input.

[srikant96]

[fullpath] + "|||`<worksheet> $`"

space important followed by worksheet and $ at the end  ( enclosed within singles quotes ). III important ( 3 pipes )