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

Alteryx Designer Knowledge Base

Definitive answers from Designer experts.
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.
View full article
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
View full article
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
View full article
"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)
View full article
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)  
View full article
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  
View full article
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  
View full article
Having trouble reading or writing Microsoft Access files? No worries - Client Services is here to help!
View full article
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.  
View full article
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.
View full article
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 .
View full article
Connecting to Hadoop HDFS/Hive/Impala/Spark with Alteryx Designer.
View full article
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
  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.
View full article
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.
View full article
A user posted on the Forum that the ampersand character (&) was causing an error when importing a series of KML files. The fix? Have a prior Alteryx process replace this character with the word 'and' within all KML files without ruining the rest of the file. To accomplish: Pull in an Input Tool and set the file type to .csv with no delimiter (designation is \0). In the Input Data Source portion, use the wildcard (*) to pull in all of your text files (using KML in the example). For the option Output File Name as Field, select File Name Only. Use a Formula Tool to update the text within the files. Output each file individually with one Output Tool, making sure to set the output to dynamically update based on your file name field. The process is fairly straightforward and should help resolve any text or character issues that may be an issue. For an example, please see the attached workflow.
View full article
  Here in Customer Support, we like to make sure our workflows run as smoothly as possible. Sometimes you may ask yourself “Why can’t my excel file just open right after I output it?”   Where there is a will, there’s a way.   Ladies and Gentleman, I introduce to you the beautiful functionality of the Run Command Tool.   With this trick, you never have to go into the directory to manually open the saved file again.   Note: There are multiple ways of doing this with the run command. This is just a simple example.       For this trick, you’ll only need 5 additional tools.   Before your output tool, you’ll need a Block Until Done Tool. This is for quality control as Excel doesn’t like to open when you are writing into it at the same time.   This prevents Alteryx from writing and opening at the same time.   Next, you’ll need a Formula Tool, which will just add another field with your command. When making this dynamic, you’ll want to make sure you update both the path of your output and formula tool.       Whether you do that in a macro or app, it’s entirely up to you. After the formula tool, you’ll want a sample tool so that way we only select tool to deselect all the fields except for your command. Now just make sure your run command is set up properly. In this example, it’s set to write out the command as a bat file and be read back in as a command.       To learn more about how to configure the run command, I would read through this helpful article.   After you have this set, watch as your workflow has made your life even more automatic.
View full article
Some users have reported a problem when importing and exporting macros within the Alteryx Designer when the Regional Settings for the machine are not set to English (United Kingdom) or English (United States) (see screenshots below). You may also be unable to see personalized Macro folders within the Designer. They encounter the following error: "Input string was not in a correct format.”   The workaround for this is to switch the Regional Settings to the regions named above and you will then be able to import, export macros and see personalized macro folders within the designer categories.   The screenshots below demonstrate what the users will see when they try to import and export workflows with other Regional Settings than English (United Kingdom) or English (United States).               To find Region and Language settings in Windows 7, please navigate to the Control Panel>>>Clock, Language and Region>>Region and Language. For more information on Localization please see this link: http://community.alteryx.com/t5/Analytics-Blog/Alteryx-Commences-Localization-Endeavors/ba-p/11765.        
View full article
This article is part of the CS Macro Development Series. The goal of this series is to communicate tips, tricks, and the thought process that goes into developing good, dynamic macros. In this part, we demonstrate how to read in multiple files with different schemas using a Batch Macro.
View full article
Question Can I read in an Excel file located in a zipped archive file from Amazon S3? Answer Unfortunately, this is not an option within the Amazon S3 Download Tool, as it only allows you to choose between CSV, DBF and YXDB files. However, this is possible within Alteryx with the use of a simple workflow utilizing a three line batch file, the Run Command Tool (master it here), and the AWS Command Line Interface (CLI).   In order to use the CLI, you must first download it and configure its settings. Please visit this page for information on how to do that. Once that is setup, you simply need to setup the batch file and configure the Run Command Tool.   Step 1 In the first step, you will use a Text Input Tool to write the batch file code. This code will use the CLI to copy the ZIP file from the S3 bucket to a locally accessible drive. Configure the Text Input Tool as follows:     Important: Make sure that line 2 points to where your CLI is installed. In line 3, replace "alteryxtest" with the name of your bucket, "ExcelTest.zip" with the name of your ZIP file and enter in the correct location to copy the file to.   Step 2 In the second step, you will use the Run Command Tool to do the following: Write out the batch file ("Write Source") Run the batch file created in the previous step ("Run External Program") Read the file into the workflow ("Read Results")   Important:  When entering in the "Read Results" section, your ZIP file will not exist at this point so you cannot simply navigate to and select the file. So, you have two options: Click on the "Input" button and enter in the full path of where you are copying the ZIP file (found on line 3 of the Text Input tool) along with the file name, a pipe character, and then in brackets, the sheet name. For Example: C:\Users\dchapman\ExcelTest.zip|ExcelZIP.xlsx[sheet1] Run the workflow once without the "Read Results" section completed in order to copy the ZIP file from the S3 bucket. Then, click on the "Read Results" button and navigate to the ZIP file and choose the Archive file to read it.    This same workflow can be used to read other archived files as well. However, you will have to make slight adjustments to the "Read Results" section of the Run Command tool. For example, if reading in a CSV file, you would simply include the archived file name. Since a CSV file does not have "sheets", the bracketed sheet name is not needed.   I plan to create a simple macro with a user interface that will do the same thing. Once complete, I will post it in the reply section.   Thanks for reading!
View full article