community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx Knowledge Base

Definitive answers from Designer experts.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE

Input an Excel range

Alteryx_KB
Import
Created on

Alteryx allows you to input a selected range of values from your Excel spreadsheet. This can be handy when working with large Excel files where you only need a small subset of data, thus providing you an opportunity to optimize the processing time of your module.

 

In Alteryx versions prior to 18.1, the following example illustrates how to do this.

In this example our data starts in row 5 and column B and ends in row 7 and column D.



Begin by bringing the Input tool into your module, then browse to the particular sheet in your Excel file that you wish to pull data from. It will look like the following image.  Notice that Option #3, Table or Query, points to ‘Sheet1$’, we will modify this to point to our data range.




To edit the Table or Query, click on the button with three dots () on the right side of Option #3.



Next, click the SQL Editor button and change the range from

 

Sheet1$ to Sheet1$B5Robot Very Happy7






Click OK. Now click on the Update Sample link In the Input Tool properties window to see the new range.

Attachments
Comments
Alteryx Partner

You can do this from the Excel end as well by setting a range of your spreadsheet as a "Named Range" (highlight part of your spreadsheet>right-click>Define Name...). The named range then shows up in the "Table or Query" selection list in Alteryx. Print Areas work as well, but Named Ranges are more flexible. Using Named Ranges is great because you can change the range definition in Excel (use the Name Manager on the Formulas ribbon) and your Alteryx workflow still works.

Alteryx Certified Partner

Great tips!! These methods can be a lot quicker way to choose specific ranges from messy Excel files than using a combination of Select records tool with Select tool etc..

Asteroid

I have found that the pulling and writing to defined names only works with the xls format or xlsx legacy format. the New microsoft xlsx format and xlsm format, IMO, doesn't allow for the defined names or keeping data formated in excel and just replacing the data. If you run into any problems, I would suggest downloading the legacy drivers from Microsoft.

I am doing this with the Input Data tool. The cell that I am reading in to the workflow has more that 255 characters and the input data tool is reading in only the first 255 characters. Any ideas? Thanks.

Moderator
Moderator

Hi @geofcanuck,

 

Can you post a sample of your data and workflow?

 

Thanks,

Thanks for asking. I have created a workflow. How do I add attachments?

Moderator
Moderator

Since this is a KB article, I think the attachments are disabled.  I'll follow up with you directly by email and we can post the solution here

 

Thanks,

Inactive User
Not applicable

Is the SQL editor for Excel still available?

I would like to read a specific range in a sheet from the Input tool, I have the latest version of Alteryx and it seems unavailable.

Asteroid
Asteroid

Sorry to highjack the tread but my alteryx comes up like so when I click on the "..." in the input tool:

Table-Query selection.PNG

 

 

Is this a because my company makes me run an non-elevated version of alteryx? Could it be that my company has disabled the query tool from alteryx?

 

I also can not enter a command directly into the line on the input tool.

 

@D12monkey  see the comments on this product idea. They removed/changed the way you can query select cells from excel. You'll just need to always type into the file input box something like this:

C:\Data\File.xlsx|||`Really Long Tab Name$A1:A2`
Asteroid

@patrick_digan Awesome, forgot about hardcoding into the file input line. Nice way to bypass the limitations I have. THANKS!