Input Tool (2018.1) Excel Range Feature Removed

[MarqueeCrew]

A recent post solution (https://community.alteryx.com/t5/Data-Preparation-Blending/Can-somebody-tell-me-where-is-quot-Choose...) by @patrick_digan alerted me to a loss of functionality of the Input Tool.  In order to define a range of data via SQL to Excel (e.g. Sheet3$A1:C10) you need to know a work-around instead of just modifying the SQL.  The work-around is to modify the XML.  I would like to see that functionality returned to the Input tool.

Cheers,

Mark

[ARich]

@patrick_digan - makes sense. I'll investigate and try to understand why that was removed....

[ARich]

I've opened this idea back up for review. We're moving away from edits to the connection string, so we'll look at a better way for users to quickly specify/modify a custom range.

[rafalucia]

@AlexP  Any update? I am interested too.

Thanks

[Annika]

@AlexP

How can I select specific columns from Excel that are not next to each other eg A1:A20;C1:C20 (excluding column B) in the input tool?

thanks!

[james204]

@Annika wrote:

@AlexP

How can I select specific columns from Excel that are not next to each other eg A1:A20;C1:C20 (excluding column B) in the input tool?

thanks!

I would also like to know this!

---

[cmcclellan]

I've just found this thread (& still recovering from the shock that this feature has been removed).  This will be really bad news for one of my clients, they use this feature a lot (they also only upgrade once per year so they haven't found out about it yet)

[manion72]

Hi @patrick_digan, @ARich, Hopefully to help other newbies having an issue with the exact syntax of this workaround, for me at least one vertical pipe ( | ) seems to be required between filename and sheet name, with single quotes around sheet name. Clicking on "Refresh" in Input Data tool will then update box 3. Ex: File1.xlsx|'Sheet1$A2:B2' Thanks :) Michael M.

[ARich]

Great tip, @manion72! Thanks so much for posting.

[tsmit593]

To add to what @manion72 shared, if you need a query instead of just a range, the following format worked for me. This was a very simple query, but that is how my customer wanted it, and I imagine it would work with more complex queries as well.

C:\FilePath\Example 2019-1.xlsx|SELECT * FROM 'Example 2019-1$C12:F25'

Regardless of how hard it was to find, this post saved myself and my customers tons of time!

Thanks

[jpoz]

Thank you for suggesting this! The ability to specify a range will be a part of the Excel input/output dialog in an upcoming release. Until then, you can continue to use this functionality by typing it in manually as described in previous comments.