Data Input SPECIFIC table name from Excel?

[MCoy]

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.

[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

[AmyD]

Hi @MCoy, 

Below is an example of how I set my named ranges in Excel. Highlight the preferred range and give it the desired name. 

After the file is saved and closed. Drag an input tool down to the canvas. When selecting your file, the newly created named range should show up when the prompt asks for a sheet. 

 Please let me know if you are still having issues. 

Thanks!

[MCoy]

Hi Amy,

Thanks for posting. I'm happy with how to create Named Ranges in Excel but they're not the same as Tables in Excel. A table Excel can grow in size whereas a Named Range has to be redefined every time a row is added. I have a series of tables maintained manually and used as inputs to add context to my data.

Tables in Excel are pure tabulated data - the perfect feed into Alteryx; so there must be a way of naming the table in the SQL query rather than having to rely on Named Ranges? Maybe it's the same method / syntax? I just need someone to provide me with an example of the SQL query (my weakness...)

Thanks

M

[AmyD · Accepted solution]

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!

[pmaddox015]

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:

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

Field	Value
Name	Sample_Named_Range
Scope	Workbook
Refers to	=Sample_Table_Name