Data Input SPECIFIC table name from Excel?

I'm more used to Power Query in Excel so finding my way with Alteryx. I'm used to Excel offering me the actual table within an Excel worksheet rather than the whole sheet. As a result, a lot of my "standing data" has comments and other user-orientated "fluff" in the sheet (but OUTSIDE of the actual data table).

The size of the table may change over time so I do not want to incorporate an SQL statement to pick up on a worksheet range.

Is there a way of directing Alteryx to input data from a specific table name in Excel?

Many thanks.

Datasets

Accepted answers

AmyD

Hi @MCoy,

Currently, Alteryx has the ability to input sheets, named ranges and specific ranges; but due to the limitations of the Excel driver, not varying tables. An option would be to bring in the whole sheet and parse out the rows and columns that are not needed for your analysis.

If you would like more help on how to parse the data, please let me know and we can work offline.

Thanks!

All comments

MarqueeCrew

Alteryx can access any named range in Excel. The named range (formula option) should appear as an input option when you configure the input data tool.

Cheers,

Mark

MCoy

Thanks for posting. I cannot see "formulas"; only Tables, Stored Procedures, SQL Editor

I've been trying to use the SQL editor to append the table name to the sheet name but it's not working. Either it's not possible or I'm simply not entering the correct query.

I start by selecting the worksheet (under the "Tables" tab) and get this SELECT * FROM `Floor Conversion$`

What is the correct query if I want to extract data only from the "Floors" named tables in the Floor Conversion worksheet?

Thanks

MadYX

One suggestion as a work around. you can use an offset formula in your range to create a range that is able to 'grow' eliminating the need for a table. example if my range is A1:f20 then my offset would be in the range name manager create a new range and instead of selecting A1:F20 you put this formula in the REFERS TO: =offset(Sheet1!$A$1,0,0,counta($a:$a),counta($1:$1)) What is does is it uses A1 as the anchor and tests for the number of filled cells in the top row and first column.

Melvin_V

I landed on this post with the hope that there's an expected syntax to enter |||'Table Name' at the end of the filename, which in turn will return data that's part of that specific table (assuming you've got random unknown variables across the sheet outside the table and an unknown list of columns within table - to be able to parse out the rest).

If you're solution has the luxury of tweaking your input files at the source, then you could create a named range which is equal to the (dynamic) boundaries of the table name. This way, you can leverage the dynamic boundaries of Excel tables along with the limitation of Alteryx being able to only read data within named ranges

FYI: How to create a dynamic boundary named range within Excel:

Highlight all rows columns within the expected table
Click on Formulas (within Excel Ribbon) --> Define Name --> Define Name
Setup the named range with the configuration below:

Field	Value
Name	Sample_Named_Range
Scope	Workbook
Refers to	=Sample_Table_Name

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703