This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
It's the most wonderful time of the year - Santalytics 2020 is here! This year, Santa's workshop needs the help of the Alteryx Community to help get back on track, so head over to the Group Hub for all the info to get started!
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).
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'.
imports the Excel file sample.xlsx from Sheet1, and the range B5 : D7. Note that the value of 3. Table or Query has changed.