Alteryx Designer Knowledge Base

How To: Create a Calgary Database   A Calgary Database is a proprietary Alteryx format that allows users to query against a file of millions of records quickly without having to read in all the data. A Calgary Database is created with the Calgary Loader tool, which allows users to create a database from any type of input while selecting which fields to index. Calgary Databases are useful for running ad-hoc queries against a large dataset, e.g. ConsumnerView data.     Prerequisites   Alteryx Designer (any version)     Procedure   Bring the data to be written to the Calgary Database into Alteryx and transform it until it is in the desired format, keeping in mind opportunities to standardize values to make the indexes work better. For example, are all your ZIP codes properly and consistently formatted? Starting with Alteryx 5.0, Calgary Indexes are not case sensitive anymore, treating "CALIFORNIA" and "California" the same. However, if some of your data uses the full state name and some uses the state abbreviation, and you are planning on using state as an index, you should pick one and use it consistently. You might also want to add flags that other users might find useful for querying data. For example, create a flag to indicate the current month's (or quarter's or year's) data or a custom region such as "NorthEast", "South", "Midwest", etc. In the Calgary Loader tool, map the location of the Calgary Database in the "Root File Name" box. The tool will create a .cydb file (the data file), multiple .cyidx files (the index files), and an __Indexes.xml file that contains the index values. Since it will write out multiple files, a best practice is to have a folder dedicated to your Calgary Database. You cannot append to a Calgary database. To add records, rebuild the Calgary database. Use the "Data" and "Index" columns to select which fields to include as data fields and which to index. Typically, all fields are included as data, but only certain ones are indexed as each index takes time to create. For index fields, the index type can be selected. "High Selectivity" is used for data with many different possible values, such as ZIP codes. Select "Low Selectivity" for data with fewer unique values, such as State or Region. "Low Selectivity" also creates a drop-down option for the Calgary Input Tool. By default, the index type setting is "Auto". In Auto mode, Alteryx looks at the first 1 million rows of data and decides if the index should be high or low selectivity. All fields with more than 550 unique values will be set to high selectivity. If the data changes after the first 1 million rows, Alteryx might select the incorrect index type. This option might also take longer to process since Alteryx has to look at 1 million rows of data for each index in Auto mode. Use the Calgary Input tool to read in data from a Calgary database as described here: Querying a Calgary DB / File to Select and Limit Input Records. Did you know that you can read in the .cydb file the same as a .yxdb file in the regular input tool? However, you won't be able to query any of the indexed fields.     Additional Resources   Using Custom Lists to query Calgary Indexes in Apps and Macros Querying a Calgary DB / File to Select and Limit Input Records Building a Calgary Database with "Searchable" Fields Calgary Databases

How To: Enable Microsoft Excel Binary, Excel Legacy, Access (accdb extension) for In/Out tools   The following formats may not be found in the "File Format" option in the In/Out tools and require a separate installation from Microsoft: Microsoft Excel Binary (.xlsb) Microsoft Excel Legacy (.xlsm) Microsoft Access (.accdb)   Note that these drivers do not come with our installation but from Microsoft Office or separate driver installation.   Prerequisites   Product - Alteryx  Designer   Procedure   To download the driver, go to https://www.microsoft.com/en-us/download/details.aspx?id=13255 Follow the instructions to download the installer.  Run the installer. After installation, you should be able to find the Excel Binary, Excel Legacy and Microsoft Access (.accdb) options in the Input Data tool and the Output Data tool.   

How To: Add Exception Handling to Oracle Pre or Post SQL   Exception handling may be needed so that a scheduled workflow continues even if a pre or post Oracle SQL statement has an error. Since PL/SQL currently is not supported in the SQL Editor, you will need to create a stored procedure for exception handling and call the stored procedure in the pre or post SQL section.   This example shows how to create a stored procedure that handles exceptions for a delete table statement when the table was previously removed and does not exist. (Oracle currently does not have an IF EXISTS option.)   Procedure   Adding a parameter for the table name is recommended so that one stored procedure can be reused for any table.   Note: You will need to create procedure permission on the Oracle database, contact a DBA if you are lacking this permission.    Use this format in your SQL editor for the stored procedure. Notice a space is added at the end of the SQL command ‘DROP TABLE ’ followed by || for concatenation and the parameter name used as a place holder for the table name.      Once the stored procedure exists on the database, you can reference it in the Pre SQL or Post SQL sections of the Input Data or Output Data Tool. Click on the Ellipsis button to open the section.      On the Stored Procedures tab, just highlight the stored procedure name on the left, and then in the Value text box, type in the table name surrounded by single quotes.     Alternately, you can call the stored procedure from the SQL Editor tab, using this format:     Additional Resources   Input Data Tool  Output Data Tool   

The Input Data Tool is where it all starts in the Designer. Sure, you can bring in webscraped or API data with the Download Tool (master it here ) and our prebuilt Connector Tools , but the tool that makes it a breeze to grab data from your most used file formats and databases is the Input Data Tool.

Issue   When using the Salesforce Input tool, the following error is seen at run-time:   400: Bad Request     This may only occur on some tables It fails almost immediately after the workflow starts running. It may also been seen on the Salesforce Credentials screen along with the error: invalid_grant: authentication failure     Environment   Alteryx Designer Version 2018.3+ Salesforce Input tool Version 4.0 or 4.1   Diagnosis   Confirm where you are seeing the error. If it is on the Salesforce Credentials configuration page when you click Connect, and you see "invalid_grant", please see Solution B.   Otherwise, validate the query to get a more verbose error log.   1. Use the Query Builder option (this is the default option) and select the Table and Output Fields you want to read in. 2. Change to the Custom Query option and click the Validate button:   3. If you receive the error: "OPERATION_TOO_LARGE: exceeded 100000 distinct ids", see Solution A.     Solution A   The data you are attempting to pull is too large to be returned in one call based on the Salesforce API limits used by the Salesforce Input Tool. Limit the number of results returned by modifying the WHERE Clause (SOQL) in the tool's configuration:     Please see the Additional Resources section below for a guide on SOQL (Salesforce Object Query Language). Here are some suggestions:   Try limiting date fields by relative dates : CreatedDate = THIS_YEAR Try limiting a string field to a certain value: Name='SFDC Computing' You can also combine conditions with logical operators like AND & OR: Name='SFDC Computing' AND NumberOfEmployees>25 For more advanced comparisons, you can perform fuzzy matches by using the LIKE operator.  For example, you can retrieve all accounts whose names start with SFDC by using this condition:  WHERE Name LIKE 'SFDC%' - The % wildcard character matches any or no character. The _ character in contrast can be used to match just one character.     Solution B   The credentials are incorrect. Please confirm your Username, Password, and Token are correct.     Additional Resources   Salesforce Knowledge Base - Write SOQL Queries Salesforce Knowledge Base - Reset Your Security Token

How To: Build Queries without using the Visual Query Builder   The Visual Query Builder window can take a long time to load because it always loads all column metadata for all tables for all schemas, even if a default database is selected in the database connection. This article will walk through using the In-Database tools as an alternative to using the Visual Query Builder.    Prerequisites   Alteryx A working Database connection This works best for connections supported for In-DB as noted in the Supported Data Sources and File Formats   Procedure   The Tables view loads only tables names and no column names for the default database selected and it caches the list. This allows it to load faster than the Visual Query Builder.   Make the Tables View the default view Drag an Input tool on the canvas and connect to a database (it doesn't have to be the database in question, any database will work) Click the Tables view and click the checkbox next to "Open Tables view by default" at the bottom left of the window  Create an In-DB Connection to your database as described here: How To: Create an In-Database Connection Use a Connect In-DB tool to connect to the database. To build your query: Select the table name in the Tables view. This creates a SELECT * FROM TABLE statement with no criteria to limit the query Use the In-DB tools to build the query. e.g. the Select In-DB tool to select only columns you need or the Filter In-DB to remove rows not needed Alteryx constructs a SQL query based on the tools on the canvas and sends it to the database In the example below, both workflows accomplish the same thing, one through the Visual Query Builder, one using the In-DB tools:   Additional Resources   FAQ: How Do the In-Database tools Work? How To: Create an Alteryx In-DB Connection File Database Issues – Working with Alteryx Customer Support Engineers (CSEs)

Some time ago, there was a nice writeup: The Ultimate Alteryx Holiday gift of 2015: Read ALL Excel Macro: Part 2. This amazing macro allowed me to read any excel file, regardless of the number of tabs.   Until I start working with users that use diacritics on the sheet names.     For example:   If we try to use the mentioned macro, you will receive an error like this: ‘not a valid name’ I decided to approach this as a macro (2 levels) and use the Directory functionality to read all possible xls files within a folder.   Level 1: Read xls Tabs   This macro will read all the tabs for a single xls file. I used an R tool that includes the library readxl.   This library allows us to read xls files. I used the excel_sheets function to extract the sheet name and compile the sheet name with the name file path. You will receive a column per tab that the xls file has. I cleaned these two values and passed them as Path and Tab.     This data is sent to the Read xls file macro.     Level 2: Read xls Files   This macro gives structure to the full path (Path + Tab) using the structure needed in xls files. It uses the Dynamic Input tool to dynamically choose the data and output its content.       Note: Update the XXXX for your corresponding paths Don’t forget to install the R library readxl under your %Program Files% path g. C:\Program Files\Alteryx\R-3.5.3\library

No Lua script was found   When reading in from a database, the following message is seen:   Info: Input Data (n): No Lua script was found for corresponding ODBC driver.   Environment   Alteryx Designer 2018.4+ Windows Operating System ODBC database connection     Cause   This message indicates that you are connecting to a database, and no Lua script was found. This is expected when you are connecting to a database that does not work out of the box with Alteryx. Please note this is a message and not an error or warning; it will in no way prevent your workflows from executing successfully if you are not using a custom Lua script.     Explanation To support new ODBC drivers that Alteryx has not validated/tested, you can create a custom Lua script that will map driver and database data types to Alteryx types.  To learn more about this (and find sample Lua scripts) please see the documentation Customizable ODBC Driver Connections found in: %LOCALAPPDATA%/Alteryx/bin/RuntimeData/ODBC

"Unable to find connection 'x' " when workflow is scheduled   When running a workflow via the Scheduler (for Designer with Automation), the following error is observed:   Unable to find connection "x"   Environment   Alteryx Designer Designer with Automation Windows Operating System In-Database Connection   Diagnosis   1. Confirm that you are using Designer with Automation and not Alteryx Server. You can confirm this by opening the Alteryx System Settings from Designer under Options > Advanced Options > System Settings. On the Setup Type page you will see Designer and Scheduler Only selected.   2. Confirm the In-Database connection is setup as a User connection. You can check this from Designer by navigating to Options > Advanced Options > Manage In-DB Connections and choosing the Data Source and Connection name you are using. It will show next to the name of the Connection:     Alternatively, you can check in the drop-down for the Connection Name Connect In-DB tool or the Data Stream In tool:       Cause   The In-Database connection in use is a User connection; scheduling requires a System connection or a File connection.     Solution A - System Connection   Follow the steps in this article to set up a System connection: How To: Create an In-Database Connection This will require admin permissions. Please see the following article for more information: Unable to create System Connection/System Connection type missing   Solution B-  File Connection   Follow the steps in this article to set up a File connection: How To: Create an Alteryx In-DB Connection File The .indbc file will need to be available to the Run As User or the user running the Alteryx Service. To avoid any issues, you can package the workflow, then schedule as follows: Package the workflow by going to Options > Export Workflow, making sure you check the box to include the .indbc file Go to Options > View Schedules On the Workflows tab, click the + icon Navigate to the .yxzp file you created above and set the schedule frequency     Additional Resources   Database Connections: Creating an alias and the advantages of using an alias Database Issues – Working with Alteryx Customer Support Engineers (CSEs)

How To: Create an In-Database (In-DB) Connection   Apart from making standard database connections, which allow for data to be pulled from the database directly into Alteryx, Designer also has In-Database functionality. The In-Database tools allow for the workflow created to be converted into a query which will then be executed on the database, and not locally in Designer.     Prerequisites   Alteryx Designer Windows Operating System   Procedure   In Designer, drag and drop the Connect In-DB tool (located in the In-Database tool palette) onto the canvas Select the “Manage Connections…” option to establish a new In-DB connection The Manage In-DB Connections window should appear. To configure this window: Select the Data Source/Database type from the Data Source  drop-down. Under Connection Type, select User, System or File connection. For file connections, see this article: How To: Create an Alteryx In-DB Connection File If you do not see the System option available, see this article: Unable to create System Connection/System Connection type missing Under Connections, select New. Under Connection Name, enter a name for this connection. Under Driver, select the method of connection to use. Click the Connection String drop-down. You can choose to use an existing connection or create a new connection by clicking the New database Connection… option. You can also paste in the full connection string if you have it available. Repeat steps 5 and 6 for the Write tab. Click OK. If the connection was successful, the Choose Table or Specify Query window should appear.   The connection will now be stored in the Connection Name drop-down.     Additional Resources   Troubleshooting Database Connections FAQ: How Do the In-Database tools Work? Database Issues – Working with Alteryx Customer Support Engineers (CSEs)  

How to check for encoding or formatting issues with Excel worksheets   When Excel worksheets are used in Alteryx Designer, sometimes Designer has difficulty reading the data. Viewing the worksheets in XML format is a good way to check for encoding or formatting issues as all of the encoding and formatting is shown.   Prerequisites Product - Alteryx Designer, Gallery, or Server and an Excel file used in Alteryx workflows or applications   Procedure To view in XML, open the Excel worksheets in 7-zip or another application used for .zip files. Use the format C:\folder_path_of_the_workbook\Excel_file_name.xlsx\xl\worksheets. Next, click on the sheet name and the XML for the worksheet is shown.          Notice the type of encoding is listed at the top. Followed by the formatting schemas, and then formatting for the worksheet. If needed you can change the extension of the worksheet from .xml to .txt and edit the encoding or formatting of a worksheet to match a working example, and then change the extension back to .xml.    To edit, right click on one of the sheets, select Rename, and change the file extension to .txt. Edit as a text file, and then Rename again to change the extension back to .xml.                                    Common Issues  If the Excel workbook is created automatically, such as being created by a script, information needed for Designer to open the worksheets may be missing. Excel can sometimes add this information when opening the worksheet, but Designer is not able to add default encoding and formatting if it is missing. If you come across an issue, it is a good idea to try adding example data directly in Excel and saving as Excel should automatically add the needed encoding and formatting. Afterward, you can compare this with the broken file, and update it to match the working example.    There are other possible reasons why Designer may not be able to open an Excel spreadsheet. However, checking for encoding and formatting issues in XML view will catch many of the problems with opening Excel data in Designer, so it is a good place to start.    Additional Resources Character encoding Microsoft Office XML formats Structure of a spreadsheet  

Issue    During run time of workflow, the following error is received:    Error: Input Data (1): Error SQLExecute: [Simba][Athena] (1040) An error has been thrown from the AWS Athena client. Athena Error No: 99, HTTP Response Code: 1759505392, Error Message: Unable to connect to endpoint [Execution ID: e7fe279d-f39b-4872-b37d-8ad49d49f3f5]   Environment   Alteryx Designer Amazon Athena Environment Windows Operating System Amazon Athena ODBC Driver (found here)   Cause   In version 1.0.3 and greater of the ODBC Driver, Result Set Streaming is enabled by default which has extra requirements. More info on the requirements here.   Solution - Connection String (DSN-less) If connecting via a Connection String: Add UseResultsetStreaming=0 to your connection string Example string:  odbc:Driver={Simba Athena ODBC Driver};AwsRegion=[Region];S3OutputLocation=[S3Path];AuthenticationType=IAM Profile;AWSProfile=[YourProfileName];UseResultsetStreaming=0 Use this string with your standard tools (Input Data, Output Data) or with a Connect In-DB Tool. Solution B - DSN Connection  If you are connecting to your Amazon Athena environment with a DSN connection (via ODBC Admin): Open up ODBC Data Source Administrator  Find your Athena connection and select it Hit "Configure..." Hit "Advanced Options..." Uncheck the "Use Resultset Streaming" box Hit OK to save the Advanced Options Hit OK again to save your connection  

Having trouble reading or writing Microsoft Access files? No worries - Client Services is here to help!

As users of Alteryx become increasingly international, it is important to support the need to prepare, blend and analyze data in a large number of languages. However, many users with data in non-English languages encounter roadblocks at the first step of their data analysis: inputting the data into Alteryx.    This article will demonstrate two ways of bringing double-byte characters (DBCs) into Alteryx.  These particular types of characters are associated with languages that have many unique characters or symbols, such as Chinese, Japanese and Korean (CJK).  These languages’ graphic characters are stored in two bytes of data rather than just one, which is sufficient for languages like English, French and Spanish (among many others) that can be represented by 256 characters or less.  Chinese, Japanese and Korean languages require a fixed width sequence of two bytes for every character, which allows for about 65,000 characters.  This need to support such an extensive dataset requires an Alteryx user whose data may be represented by CJK characters to consider certain formatting and preparation steps both before and after inputting data into Alteryx.    Pre-Input Considerations Before inputting data into Alteryx, data with CJK characters requires certain encoding and file type considerations.  Encoding is the process of converting data from one format into another specific equivalent code that uses letters, symbols and numbers for storage and processing.  When using CJK characters, encoding your data in a format that supports double-byte characters is important to accommodate the range of characters in your dataset.  Storing the data as Unicode ensures that you do not lose data as you begin your inputting process into Alteryx (Figure 1).   Figure 1: To encode your data from Excel, click “Tools> Web Options” in the bottom right-hand corner of the Save As Window.  Then, select “Encoding” from the file tabs. In the Dropdown menu to Save the Document, select “Unicode”, then click “OK”.   Make sure that your file name does contain any non-English characters, as that can cause issues the Input tool.    The second pre-input consideration for CJK data is the file extension, or type, with respect to how the data is saved.  For example, saving CJK data directly to a Comma Separated Values (CSV) format, especially without encoding, is discouraged.  This is because CSV file formats distort characters other than those supported by the American Standard Code for Information Exchange (ASCII) text.  In the case of CJK characters, that is likely to be all of your data (Figure 2)!  Rather than suffer through the heart-wrenching experience of seeing all your data turned into meaningless question marks, try saving your data as a Unicode text file (.txt) or Excel workbook (.xlsx), both of which are supported by the Alteryx Input Tool (Figure 3).  I have found that using CJK characters in these file formats ease the process of inputting data into Alteryx and even reduce some of the steps you may need to perform in your post-input process.     Figure 2: Rats! CJK characters that are saved directly to CSV format without encoding turn into meaningless question marks.  WHHHYYYYYY????? Figure 3: Rather than lose your precious data, save as Unicode Text or an Excel Workbook (encoded as Unicode as a safeguard).   Post- Input Considerations Because CJK characters are double-byte in size, it is important that their field type is set appropriately to display the data completely.  Generally, importing data stored as Unicode text or a Unicode-encoded Excel workbook will be read in as a variable length wide string format (V_WString) to accommodate these wider types of characters (Figure 4).  As you can see, forcing the field type to a narrow string format (V_String) leads to data loss.  Throughout the data preparation and blending process in Alteryx, you should be sure that fields containing CJK characters have the necessary space requirements to store and transmit the data.  The sudden conversion of CJK characters to question marks may indicate that your field type has changed; should this occur, field types can be easily changed using a Select Tool.   Figure 4: The larger size requirements of CJK characters creates the need for using wide string fields (V_WString).   Datasets that contain a mix of Western Latin character sets and CJK characters may require the conversion of text between Code Pages.   This may be especially necessary if data has been transferred among many users who have different computer language settings or encoding systems.  Figure 5 shows data that has been transferred from a Chinese colleague to an American Alteryx user.  Despite bringing the data in as Unicode Text, the data is still unreadable.  To convert the data to a useable and meaningful format, Alteryx tools with an Edit Formula Box (or Expression builder), such as the Formula or Multi-Field Formula Tools, contain ConvertFromCodePage and ConvertToCodePage functions.  These functions facilitate the conversions between language codes and Unicode.  In the below example, the Multi-Field Formula tool is used to overcome coding issues to convert data from the original Chinese code to Unicode using the ConvertFromCodePage function.  As a result, the data is displayed in a readable and useable way.   Figure 5: Converting the data from Code Page 20936 (Simplified Chinese GB2312) to Unicode using the ConvertFromCodePage function renders the data readable and useable…Hooray!   And voila! Errr… 这里是! Now your CJK characters are brought into Alteryx beautifully!   Have you had experience with inputting non-English language data with Alteryx?  Post any helpful tips or tricks that you’ve used to the Comments section to share with the Alteryx Community.   **Thanks not only to the Alteryx users whose data prep needs have inspired this article, but also to Alteryx’s RodL for his Community post that has provided me with the steps to even know where to begin when troubleshooting data in a variety of languages including Chinese, Japanese, Korean, Thai and Vietnamese.  

To find the full path and filename of a saved file in Excel, you use the =CELL function.       In Alteryx, you use a Field Info Tool to get this information:     The Field Info Tool allows you to see in tabular form the name of fields in a file as well as the field order, field type, and field size.     Name: field names within the file Type: type of data field Size: length of a data field Scale: with respect to fixed decimal data types, scale refers to the digits of precision Source: contains the full path and filename Description: may or may not contain information; you can add a description via the Select tool   We're only interested in the Source field and this information will be the same for each field.       Using a Sample Tool, we select just the first record:       Notice the data in Source begins with 'File:'.  We don't want that in the final output so we'll use a SUBSTRING function in the expression of a Formula tool to clean it up.  Complete the workflow with a Select tool so we only get the Source field:            I'll mention here you can use the Directory Tool to find the full path and filenames in a directory.   Select the directory you want to search.  File Specification has wildcard characters so you can limit your search to files containing specific character patterns or file types.  In the example below, let's set up the File Specification to only return files with the '.xlsx' file extension:      We're only interested in the field 'FullPath' (first column) so we'll use a Select tool to drop the remaining fields.        While the Directory tool returns multiple filenames, it will not contain a worksheet name if the file is an Excel file.  To get that information, you'll need to use the Field Info Tool as we did above.

As of the release of version 11.0, the Alteryx Designer supports reading in single and multiple input files in a zipped archive file (.zip) .  Now, multiple files of the same file type in a zipped archive can be read into the Designer in a standard Input Data Tool .

Connecting to Hadoop HDFS/Hive/Impala/Spark with Alteryx Designer.

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.

  With an Input tool alone, you cannot specify multiple ranges within an Excel sheet to import at once, however, i t is possible to achieve this within Alteryx using a slightly different process!   The tool you want to use to accomplish this task is the Dynamic Input tool. The process is quite simple. Simply enter your Excel ranges into a Text Input Tool, complete with Sheet name and tick marks (Shift + ~), with one range on each line:     Connect your Dynamic Input to the Text Input and select the file you want to pull the ranges from. Set up the input as you would any standard Input tool to read a range. When doing so, be sure to check the option indicating that the First Row Contains Data (we'll rename the headers later). Using this option avoids rows being skipped due to a mismatch in the header fields:     For more information on the specifics of doing this, take a look at this article.   Once this is done, continue configuring the Dynamic Input tool. Choose the option to  Modify the SQL Query and click the 'Add' drop down. Select the option to "replace a specific string", and remove all text except for the sheet name and range:     Once you are finished here, bring down a Dynamic Rename tool, and select the rename option that says "Take Field Names from Frist Row of Data". This option will move the first row of data into your field headers, reversing the effect of choosing the option for First Row Contains Data:     Finally, run the workflow and enjoy your newly unioned file!   The attached sample demonstrates the process using dummy data. You'll just need to repoint the Dynamic Input tool to the sample data spreadsheet.

How to dynamically run the most recent file in a file folder   Sometimes you may have daily, weekly, monthly or yearly data dumps where you want to only run the most recent file. Within Alteryx you can make this process dynamic and seamless through the use of a few tools.   Step 1: Directory Tool The Directory Tool will allow you to browse to a folder and return all the metadata related to the files which exist within that folder. The field of interest in the metadata is the 'Creation Time'.    Step 2: Sort Tool Using the field called ‘Creation Time’ we can use the Sort Tool to sort the date and time values into descending order to get the most recent file at the top of the dataset.   Step 3: Sample Tool After sorting the 'Creation Time' field I now have my most recent file in record 1. Yet, I still have rows of data for the other files within that folder that I need to remove. I can now use the Sample Tool to take the ‘First 1 Record’ and this will result in the latest file information being left.     Step 4: Dynamic Input Currently the field I have in my dataset only shows metadata avaliable for that file such as Full path, Creation Time etc. I now need to read this file and pull in the data by using the Dynamic Input Tool. In the ‘edit’ section select a placeholder file.   Then in the Read a list of data sources ‘Field’ dropdown this will need to be the ‘Full Path’ field coming from the directory Tool. In the Action dropdown this will need to be set to ‘Change Entire File Path’.     Step 5: Run the workflow You can now run the workflow and it will dynamically always pick the latest file from that folder and read the data into Alteryx.   Please find an example workflow attached to this article.