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.
Use an Input Tool to select Sheet Names.
Connect a Dynamic Input tool to the Input tool.
Configure the Dynamic Input tool to read a list of Data Sources from the Sheet Names field provided by the Input tool.
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).
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.
Start with the same workflow from the previous example.
Add a Control Parameter tool and a Macro Output tool to the canvas.
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.
Connect the Macro Output tool to the output stream of the Dynamic Input tool. Your canvas should now look like this:
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.
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.
By default, the Interface Designer window will be displayed in the left-hand side of the Designer window.
Click on the Cog icon in the left bar to access the Properties tab.
Select the Output fields change based on macro's configuration or data input option.
You now created a Macro. In order to use it, you will need to add it to a workflow.
Save the Macro as a Designer Macro (*.yxmc). Do not close the window with the Macro yet.
Right-click the new workflow canvas and select Insert > Macro.
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 .
Finally, add a Directory tool, and connect it to the Macro's input.
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).
Common Issues
The file is being used by another process 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.
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.
To edit the Table or Query , click on the button with three dots ( … ) on the right side of Option #3.
Click the SQL Editor button and change the range to SELECT * FROM 'Sheet1$B5:D7'
Click OK. Now click on the Update Sample link In the Input Tool properties window to see the new range.
2018.1 and later
In versions more recent than 2018.1, the button with three dots (…) on the right side of Option #3 cannot be used anymore. Instead, click into the file path field, and edit it.
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.
Additional Resources
The Dynamic Input Tool Mastery article contains valuable insights on practical use cases of this powerful tool.
A more general take on inputting data can be found in this insightful Knowledge Base article.
Fun Fact
Bananas grow on plants that are officially considered a herb since the stem does not contain woody tissue.
View full article