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

Alteryx Designer Knowledge Base

Definitive answers from Designer experts.

How To: Import Multiple Excel Sheets or a Specific Excel Range into a Workflow

Moderator
Moderator
Created on

In this article, we demonstrate how to import multiple worksheets from one Excel file, how to import multiple worksheets from multiple Excel files, and how to import a specific range of cells from an Excel sheet

 

How To: Import multiple worksheets from one Excel file

 

In the case where you have one Excel workbook with several sheets, you can use a Dynamic Input tool to import the sheets instead of an individual Input tool for each sheet.

 

Note: for this method to work, the sheets of the Excel file need to have the same schema.

 

 

2019-04-12_13-18-31.jpg

 

 

 

 

 

 

  1.  Use an  Input Tool to select Sheet Names.


    2019-04-12_13-24-35.jpg


  2. Connect a Dynamic Input tool to the Input tool.


    2019-04-04_12-21-49.jpg


  3. Configure the Dynamic Input tool to read a list of Data Sources from the Sheet Names field provided by the Input tool.

    2019-04-12_13-30-52.jpg


  4. The workflow is now configured to import the Excel sheets in a single input stream (you can see the Regions South and West from the two different Excel sheets). 

    2019-04-12_13-34-27.jpg

 

How To: Import multiple worksheets from multiple Excel files

 

When you need to import multiple sheets from different excel files, you can modify the above method to work by turning it into a Batch Macro.

 

  1. Start with the same workflow from the previous example.

  2. Add a Control Parameter tool and a Macro Output tool to the canvas.

  3. Connect the Control Parameter tool to the top of the Dynamic Output and Input tools. You should see two Action tools being automatically added to the canvas between the interface tools and the standard tools.

  4. Connect the Macro Output tool to the output stream of the Dynamic Input tool.

    Your canvas should now look like this:

    2019-04-12_13-38-27.jpg



  5. Now, we need to configure the Action Tools.

    The action type should be set to "Update Value". We need only to update the file name without changing the sheet name. Therefore, for both Action Tools "Replace a specific string" should be enabled. Please note that this string should contain the path to the file to input without the extension for the sheet. 

    2019-04-12_13-40-27.jpg



  6. If the Excel files have different schemas, in the interface designer we can set the macro to Auto Configure by Name or Position so that our workflow does not error out. Note: sheets within the same file will have to be the same schema.
    1. By default, the Interface Designer window will be displayed in the left-hand side of the Designer window. 

      2019-04-22_10-03-25.jpg


    2. Click on the Cog icon in the left bar to access the Properties tab.

    3. Select the Output fields change based on macro's configuration or data input option. 

      2019-04-22_8-34-47.png


  7. You now created a Macro. In order to use it, you will need to add it to a workflow.

    1. Save the Macro as a Designer Macro (*.yxmc). Do not close the window with the Macro yet.

    2. Right-click the new workflow canvas and select Insert > Macro. 

      2019-04-22_10-20-08.jpg

    3. Any Macro that is currently open in Designer can be added this way to the workflow. Note that it is also possible to create a Macro repository. This will enable to save Macros in one designated place and easily use them in workflows. See this Help Doc article

  8. Finally, add a Directory tool, and connect it to the Macro's input.

    2019-04-12_13-43-13.jpg


  9. With this configuration, you can import the Excel sheets of multiple XLSX files in the given input directory (you can see Regions North and South from the two Excel files).

    2019-04-12_13-46-00.jpg

 

Common Issues

 

Spoiler
The file is being used by another process

2019-04-04_12-51-37.jpg

Unable to open file for read: FILEPATH. The process cannot access the file because it is being used by another process.

To resolve, close any other applications (typically Microsoft Excel) that are accessing the file

Some of the columns are not imported, but in Excel the file looks fine

Alteryx uses the drivers that come with Microsoft Excel to import the files. Sometimes 3rd party software does not write data in the correct Excel format. 

To resolve, re-save the file in Microsoft Excel. It should now be correctly imported in Designer.

 

How To: Input a Specific Excel Range

Another functionality of Alteryx Designer is the ability to input a data subset of an Excel file. This comes handy when working with large data.

 

The procedure to input an Excel range would depend on the Designer version.

 

Prior to 2018.1

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

 

im1.png 

 

  1. Bring 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.

    im2.png


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

    in3.png


  3. Click the SQL Editor button and change the range to

    SELECT * FROM 'Sheet1$B5:D7'

    in4.png


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

 

 

 

2018.1 and later

 

 

  1. In versions more recent than 2018.1, the button with three dots () on the right side of Option #3 cannot be used anymore.

    img1.png

    Instead, click into the file path field, and edit it. 

    2019-04-22_11-10-31.jpg


  2. Use the following syntax filepath.xlsx|||'SheetName$RangeCell1:RangeCell2'.


    For example:
    \sample.xlsx|||'Sheet1$B5:D7'


    imports the Excel file sample.xlsx from Sheet1, and the range B5 : D7. Note that the value of 3. Table or Query has changed.


    2019-04-10_16-48-35.jpg


Additional Resources

 

Fun Fact

 Bananas grow on plants that are officially considered a herb since the stem does not contain woody tissue.

Attachments
Comments
ACE Emeritus
ACE Emeritus

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..

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!

Meteoroid

Is there anything that expands on this in terms of importing specific cells not just cell ranges? In the section 'How to: Input a specific cell range' - could this just be applied to a single cell?