Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Data Input SPECIFIC table name from Excel?

Highlighted
7 - Meteor

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.

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

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

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Highlighted
7 - Meteor

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

Highlighted
Alteryx Alumni (Retired)

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. 

 

NRanges.jpg

 

 

 

 

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. 

 

 

 

 

NRanges2.jpg

 

 

 Please let me know if you are still having issues. 

 

Thanks!

Amy Davies
Community Moderator and Customer Support Engineer
Alteryx
Highlighted
7 - Meteor

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

Alteryx Alumni (Retired)

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!

 

Amy Davies
Community Moderator and Customer Support Engineer
Alteryx
Labels