Dynamic Input with Excel Ranges: tool always reads whole worksheets, not ranges

[Rikard112]

Greetings. Apologies if I am not being 100% precise with terminologies because it's my first time asking for help here. 

I have encountered what appears to be a bug with Dynamic Input tool when I try to read in multiple ranges from different workbooks. In the worksheet named "IFRS IS", a list of accounts with Line Number  exists in range P9:Q350, and that is how I set up the tool in the "Table or Query" field. I then clicked  the "Update Sample" below and it seems to work fine. (See the blue circles in the screenshots). 

However when I actually run this workflow, the tool magically then switch to read the whole worksheet, despite that the input I set up specifies it to read only the range. As seen in the red circles in the screenshot below (all the null columns are things I don't want in). 

I have attached my Workflow with this post along with a sample workbook (original one is actually .xlsm format; including only relevant tabs; other ones are exact same schema). 

I am using Alteryx version 10.6.8.17850

THank you very much!

[jrgo · Accepted solution]

@Rikard112,

The issue you described is persistent on my end as well (v11.5). However, I don’t believe it’s an issue with the your approach or the tools configuration.

Using a normal input tool and configuring it to read in the range on one of the sheets with the problem, it fails there also so there’s something on that sheet that is not allowing Alteryx to specify the range.

That said, 90% of the time I attempt to create a process that will dynamically read in multiple Excel files/sheets at once, it fails (100% with formatted sheets) due to schema variations from one table to the next.

My recommendation would be to create a batch macro where it’ll process each file separately. Set it up to read the entire table and use filters and select tools to limit it the rows/columns you need.

if you’re new to Batch macros, do a search here and you’ll find plenty of posts, training and KB articles about this subject.

Hope this helps!

Jimmy

[Rikard112]

Hello @jrgo Thank you very much for your reply to this matter!

I have been forced to utilize a different approach and use only one dynamic input per worksheet and then use multiple sets of filter&select combos to cut out the parts I want. Less transparent/flexible approach indeed but it works for now. 

My department is new to Alteryx and I already started loving the tool, thus here providing some details about why it bends.

In order to investigate why, I completely recreated the exactly same macro from scatch. It works at the beginning, and then it starts to have this problem when their are >3 dynamic input tools within the same macro. 

Also, for smaller ranges the tool seems to work fine (for example the AB4:AU4 range seems to work). However, when the # of cells becomes large it stops working normally and starts reading everything to the left of the range in question. 

Hope it helps you debug in a future version. 

Thank you!