Alteryx Designer Desktop Knowledge Base

One of the best things about Alteryx is the ability to read in multiple files very easily and automatically combine them into a single dataset. This becomes a bit trickier when dealing with files that have different schemas or Excel files with multiple tabs. Adding both multiple excel files with multiple tabs, and having the schema change within each tab takes it to another level.

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.

Dynamic Input threw error: ParseError: Invalid document structure at line 1 and column 1 while processing "AliasFileXml" on the workflow with "Root element is missing." in a popup. Upon clicking OK, a different pop-up comes up saying "Internal error in SRC_GetCosmeticName_Raw"

This error is typically thrown when trying to read a file that does not match the selected File Format, but also in rare cases, from a corrupted file.

Here at Alteryx we believe in working smart, not hard. Building out reports to highlight business-critical metrics is a pretty smart way to track goals. Customizing those reports to everyone in the department, then distributing them as attachments to individual emails? That sounds like a lot of hard work. Scheduling those reports from a refreshing data source each month so you don’t have to remake or rerun the reports yourself - that’s genius. Logging into your work computer to open up Alteryx, then having to check the scheduled results before having any peace of mind those reports were delivered without a hitch? Hard.

Whenever attempting to connect HDFS to a Dynamic Input Tool, users may encounter the error: "Server root URL must be in the form "http://server:port""

How Alteryx Designer and Server use file paths

You may have noticed that the Cross Tab tool will insert underscores in your headers.

How to dynamically generate reports, based on the grouped data?

Troubleshooting the error: XmlParse Error (2): the element Configuration is not properly closed.

Sometimes clients have asked how they can re-input the same excel file that they have just outputted into the same workflow. Normally, their gut instinct would be to open up a new workflow and start fresh with the updated file. However, this can be a bit cumbersome especially if they want to do this multiple times or for those wanting to do some sort of logging process in an app. Luckily, there is a quick and easy trick for this.

Are you tired of your boring old workflow? Just sitting there in your Designer, slacking off?

The Directory Tool gives you a data-stream input that contains information about the files and folders (file name; file date; last modified, etc.) for the location of your choice, which you can then use for more complex interactions with the file system. Basically, the Directory Tool could also finally help me track down my keys - not just where I put the keys in the house, but also how long they've been there, and when they were last moved.

Have you ever wanted to restrict the fields that flow through your workflow based on their data type? What about only allowing fields that begin with the same word or are in a specific position? The Dynamic Select tool is just what you’re looking for!

The Dynamic Replace Tool is an under-utilized tool in the Developer Toolset that is very powerful. It allows for dynamic formulas or conditions to be used in your workflow. It was first introduced in Alteryx 6.1. It’s one of the few tools that is currently multi-threaded which makes is fast.

Imagine this – you’re on vacation. You’re on a sandy beach where the sun has been relentless all day. It’s hot and you need something to cool you off. Ice cream would be perfect! The Find Nearest tool can help!

The Multi-Row Formula Tool functions much like the normal Formula Tool but adds the ability to reference multiple rows of data within one expression. Say, for example, someone was on the ground floor of a house and had a Formula Tool. They would only be able to talk to the people also on the ground floor. If they had a Multi-Row Formula Tool, though, they would also be able to talk to the people upstairs, in the attic, and in the basement as well.

How the 'Group Replacement Value for SQL IN Clause' works when using SQL: Update WHERE Clause in the Dynamic Input Tool

Suppose that your spreadsheet has multiple sheets with the same structure and you would like to read several sheets into your module at once. In this case, the preferred alternative is to use the Dynamic Input tool. 

The Dynamic Replace Tool allows the user to quickly replace data values on a series of fields, based on a condition or expression.

In this article we’ll take a look at how to leverage a batch macro to read in multiple sheets from multiple Excel files using Designer version 10.0. The two Excel files I’ll be using in the example are called Fruits and Vegetables with sheets named Apples, Oranges, Broccoli and Spinach, respectively.   The first step will be to read in the list of sheet names from one of the Excel files using an Input tool. This feature is new to 10.0 and is a great addition when used in conjunction with the Dynamic Input tool.   We can then connect our Input tool from the previous step to a Dynamic Input tool. In our configuration, use the same Excel file as our Input Data Source Template. We can use the Sheet Names field from our Input tool as our List of Data Sources and our action is going to be to Change File/Table Name.   Now we’re ready to add our Interface tools to the canvas. Bring in a Control Parameter tool, 2 Action tools and a Macro Output. Connect the Control Parameter to the 2 Action tools, and then connect on to the Input and Dynamic Input. The Macro output will be connected to the output of the Dynamic Input.   In our Action tool configuration our action type will be Update Value. We want to update the File – value that is being passed on to the Input and Dynamic Input. We will, however, only need to update the portion of the string without the sheet name, which is why we’ll also select Replace a specific string: This will allow us to batch our process with multiple files.   Lastly, if our 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. We’re now ready to save the macro and put it to use!   Now, on a new canvas, we can place a Directory tool (If all your Excel files are in one directory), or, we can use a Text Input to manually enter each individual file path location. After the Directory or Text Input tool, insert your macro (right click on the canvas->insert->macro), then choose the full file path for your question in the macro configuration. Add a browse, hit run and look at the results!     We now are able to read in multiple Excel sheets from multiple Excel files!   The attached workflow package includes a workflow, batch macro and excel files created in Alteryx v.10.0   Tony Moses Client Service Representative