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!

Budget Template IS With Statistics Rightward v3.yxmc

Budg2018_MT_EQ16_TPLIC_IUL -SAMPLE.xlsx

jrgo

@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