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.
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.
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.
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
"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"
Designer with Automation
Windows Operating System
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:
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
Database Connections: Creating an alias and the advantages of using an alias
Database Issues – Working with Alteryx Customer Support Engineers (CSEs)
Occasionally you may see one of these errors from the Join Multiple tool. It is a result of Cartesian joins.
A Cartesian join is when you join every row of one table to every row of another table. You can also get one by joining every row of a table to every row of itself. A Cartesian join is very CPU intensive.
For example, if you have four files and each file has the same ID in it twice, that means it will join 2*2*2*2 times on the ID (the field on which you're joining is the key referenced in the error; in this example, it's Field1, and the duplicated value is 12345). The same can be caused by multiple nulls in each file.
After your data prep and investigation, and when you know your data are correct, your choices on how to handle Cartesian joins include:
Allow multidimensional joins: The multidimensional join will occur with no error or warning reported.
Warn on multidimensional joins of more than 16 records: A warning will be reported in the Results window that a multidimensional join has occurred.
Error on multidimensional joins of more than 16 records: An error will be reported in the Results window that a multidimensional join has occurred and downstream processing will stop.
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.
Product - Alteryx Designer, Gallery, or Server and an Excel file used in Alteryx workflows or applications
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.
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.
Character encoding Microsoft Office XML formats Structure of a spreadsheet
The Email Tool is a tremendously useful shortcut when it comes time to disseminate your analyses and other results straight from your workflow. However, in order to do so, it must communicate using Simple Mail Transfer Protocol (SMTP), which is often restricted by IT infrastructure and firewalls to protect organizations from spam. As a result, many users excited to try the tool get the direct, yet demoralizing, error below (among others):
That’s why we’ve detailed in this article the steps you can take to investigate what, exactly, is giving you trouble:
Manually-entered SMTP server
First make sure a colon and port number are appended to the server name:
Does this SMTP server use SSL/TLS or require username/password authentication?
Unless the SMTP server uses windows authentication you won’t be able to use the Email Tool, as SSL and TLS are not yet supported through the tool. You can, however, look into other approaches to sending emails in the Designer that can accommodate those requirements.
If not, do you have the required ports open in your network firewall? You can check with your IT team for port numbers and statuses, but the default ports you can check yourself are usually 25, 445, 465, 587, and 993:
You can check to see if a server and port are open using the Telnet utility; if you have Telnet installed, open the command prompt and simply type telnet.
If you do not see the second prompt above then you’ll have to install a Telnet/SSH third party client like PuTTY.
From either the Telnet prompt or client, you can open a connection to the server and port to test:
In Telnet, connect to the server and port using the command below.
In PuTTY, opening the port will look like the following.
Either approach will then send you to the following prompt.
Then use these commands ( <CRLF> is the enter key ) to send a test email that, if received, will indicate that your port is open.
HELO <CRLF> mail from:<mail from address> <CRLF> rcpt to:<mail to address> <CRLF> data< CRLF> subject:<email subject here><CRLF> <CRLF> <type the body of the message here> <CRLF> . <CRLF>
To send the email, you must end the body by hitting the enter key (<CRLF>), then period, then enter again (please note that after specifying your subject you must also press the enter key twice – not doing may neglect the message body argument). The test should look something like the below:
If the email sends and the mail to address confirms receipt, then your port is open. Otherwise, you should receive an error that should help your IT team diagnose why the traffic is being blocked.
Use the steps above to determine likely causes for the error and you’ll be able to take steps to get the Email Tool unrestricted in your network. Once that happens, bid adieu to whatever repetitious emails you might have to send in the future!
We are working diligently and intelligently on making connections to databases easier, more secure, and faster in Alteryx. Even when we can make the best possible experience for our users come to fruition, any user may still encounter some issues due to the use of third party drivers and/or the intricate communications between the database, driver, and Alteryx. To help to minimize those issues, Alteryx is tested with an abundance of "supported" databases and drivers that are proven to be compatible but, unfortunately, you just can't test every database and every driver out there. Our technical specifications page found here will show you those databases Alteryx supports, as well as the drivers we have tested and support with respect to each database. This does not mean Alteryx will not connect to a database or driver you do not see on this page (or links within the page), however, Alteryx can not support those databases and/or drivers if troubleshooting or setup is required.
‘Alteryx has stopped working’ - APPCRASH
Scenario: You are running a workflow and all of a sudden Alteryx becomes unresponsive and a windows prompt opens and says ‘Alteryx has stopped working’ with options to close the program. Once you click on problem signature properties it references 'Problem Event Name: APPCRASH'.
This error can be due to several factors however there a few troubleshooting steps which have resolved this error for several of our clients in the past.
Are you on the latest version of Alteryx?
If possible being on the latest version of Alteryx can help ensure any known bug fixes have been resolved and the software is up to date. To find the latest version of the Designer please click on this link Downloads
Limit the number of Browse tools
Every time you add a Browse Tool to the canvas it caches the data locally, therefore if you have a large workflow this can sometimes cause memory issues on your machine (dependent upon your machine environment). This can be adjusted by changing the default sort/join memory, however taking advantage of the Browse Everywhere is highly recommended!
Before removing all browses you can also 'Disable All Browse Tools' in the runtime configuration settings to test this theory.
Have you tried the block until done tool?
When Alteryx becomes unresponsive it can be due to constraints on the processing power of the machine. More often than not you may receive an error relating to an ‘inbound pipe error’ (also memory related). The Block Until Done Tool (found in the developer category) allows all the pre-processing to occur before passing data downstream (e.g. writing out through an output tool). This often makes it an easier process when writing out to a database.
Adjust your sort/join memory
The sort/join memory is one of the most underestimated elements of the designer. Because Alteryx is designed to use all the resources it possibly can, it tries to balance and use as much CPU, memory and disk I/O as possible. There are great tips on how to achieve the right balance here: http://community.alteryx.com/t5/Alteryx-Knowledge-Base/What-is-the-Sort-Join-Memory-Setting/ta-p/1302
Again, as always if this error persists after trying these troubleshooting steps, please do not hesitate to reach out at email@example.com
You may be trying to install the Alteryx Designer or Server and receive an error saying: There was an error downloading "AlteryxInstallx64_VersionX.exe...."
This is due to a proxy blocking the data manager from installing the software.
DO NOT FEAR THE ANSWER IS HERE!
All you need to do is to install the Individual Installs of the Designer/Server/Predictive tools to circumvent the data manager GUI and you will be able to download the designer as normal.
Navigate to licenses.alteryx.com
Log on to the downloads portal
Download the relevant installer .exe files.
*****Please remember to download the correct bit and admin/non-admin version for your machine!
Once the download has finished it will prompt you to finish and open the designer. This is when you can enter your license key and use Alteryx!
If this fails to resolve your issues please do not hesitate to reach out to Support
This error may occur when using the AlteryxGraph device. The reason for this is that the AlteryxGraph device creates a pipe between Alteryx and R. If creating graphs in a loop, a pipe has to be created for each loop which slows down the processing. As a result, Alteryx ends up trying to create the next graph before the previous one is completed. To avoid this, it is best practice when creating multiple graphs within a loop to keep the AlteryxGraph device statement outside of the loop.
The day will come. You are on a deadline, you need some data, and it happens: you cannot open your workflow.
What now? Your file has been corrupted somehow, and you need to restore it asap to meet your deadline. Don't fret! Here are two options:
Check your Autosaved files! It's possible you have a nearly complete version saved from the last time you worked on this module: File > Open Workflow > Open Autosaved Files (there's even a timestamp!)
Your second option is a great example of what the .bak files are for (in case you were ever wondering). First, check to see if your .yxmd was indeed corrupted. Right click your corrupted .yxmd in a windows explorer, and open with Notepad (or Notepad++ or a similar program).
At the end of every correct Alteryx workflow will be the tag </AlteryxDocument>
If your module is corrupted, it could end with anything – but the error message will tell you exactly where your file was truncated. If you do not see that tag, here's what to do: right-click the .bak file associated with your workflow, and simply rename the extension to .yxmd
Now, go meet that deadline! Save early, save often, and Happy Alteryx-ing!
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.
Unable to find the dll: "OCI.dll"
Within Alteryx this error will be prompted due to a mismatch between 32/64 bit drivers and the Alteryx bit version. This error may also appear if you do not have the drivers installed.
One way to troubleshoot this is to find out what drivers you have installed!
You can use an input tool in the Alteryx Designer, go to Other Databases and select either ODBC/OleDB (64-bit) or 32-Bit Database Connections -> ODBC/OleDB. In the next pop out window it will populate with the available drivers for those connections. If you have no drivers in this pop out window this means you do not have the correct drivers for 32 bit or 64 bit connection dependent upon which one you chose.
Links to drivers are available from within individual data source pages here. This will take you away from the Alteryx website and onto the driver provider’s pages. Please consult with your IT to find the correct drivers for your environment.
The Alteryx server is unable to run a workflow that runs properly on the user’s local machine where the workflow was created.
[ODBC Driver Manager] Data source name not found and no default driver specified alteryx
This error can be encountered when running an app or workflow on a Gallery:
Or when uploading a workflow during validation:
Users will also receive the following error when opening a shared workflow where the Data Source Name does not exist on the user’s local machine:
Version ≥ 11.0
A workflow that contains a Data Source Name (DSN) on the Input Data tool.
Confirm that the data source name (DSN) specified in the workflow is pointing to an existing DSN in your machine’s ODBC Data Source Administrator:
***You must have the driver installed on the machine prior to using this guide. The download links to many of the drivers can be found here.
On your local machine, search for ODBC Data Sources (64 bit)
The DSN referenced in the input data tool within the workflow must be listed under either User DSN or System DSN tabs.
The workflow is not able to successfully run because the input data tool cannot reach the database in the following scenarios:
The workflow creator sends a workflow with a database connection to a colleague who does not have the same DSN on their computer.
The workflow gets sent to the alteryx server to run on a scheduled frequency where the DSN does not exist on the server machine.
You must have the driver installed on the machine prior to using this guide. The download links to many of the drivers can be found here.
Once confirmed that the appropriate driver is installed, select to add from either the user DSN or System DSN tab. Generally, the User DSN is what most users will have access to and what is commonly used. You can learn more about the difference between User and System DSNs here.
In order to resolve the error at the local level or on the Alteryx Server, you will need to set up the corresponding DSN found in the app/workflow. The following Community article will provide you step by step instructions on how to set up an ODBC connection .
Once you've established the DSN on your machine, you will now be able to successfully re-run the workflow!
In Alteryx Designer 2019.1, after adding Browse tools to all of the output anchors of a tool using Right-click > Add All Browses OR Ctrl + Shift + B, running the workflow returns an Unhandled Exception error. The workflow runs indefinitely until Designer is canceled from Windows Task Manager.
This error is usually caused by two things:
The file you selected is of a different format than what is selected in the File Format drop down in the Configuration window.
A .yxdb file you are using as an input has become corrupt.
Other causes can be found here: https://community.alteryx.com/t5/Alteryx-Knowledge-Base/Error-FileID-does-not-match-in-the-FileHeader/ta-p/19870
Although extremely rare, if a .yxdb file becoming corrupt is the culprit (one cause is a network connection dropping in the middle of writing the file), you may have a quick and simple way to resolve this potentially stomach-dropping issue:
Open up Windows Explorer and navigate to the file.
Right-click on the file and choose Properties.
Click on the Previous Versions tab.
If you're lucky, there is at least one option of the file that you can revert back to you, hopefully allowing you to avert disaster. Obviously, this doesn't just work with Alteryx files, so perhaps this knowledge will come in handy with other files.
Thanks for reading!
When attempting to publish a workflow to your private Gallery which contains the Publish to Tableau Server macro, you may encounter an error similar to this:
“ Cannot find macro "PublishToTableauServer_v1.08.1\Publish to Tableau Server.yxmc"
This is typically due to one of two reasons:
The Publish to Tableau Server macro has not been installed on the Alteryx Server where the Gallery is located.
The macro has been installed but has not been installed in the correct location, or the Gallery cannot access the macro for other reasons.
Please see this link for instructions on correctly installing the Publish to Tableau Server macro on an Alteryx Server.
Even when the macro has been installed properly on the server, this issue can still occur, and if it does, here is a simple workaround to get your workflow published:
Save the workflow to the Gallery: From Designer, click on File > Save As and choose your Gallery.
Add the Tableau macro components as assets of your workflow: Click on Workflow Options > Manage workflow assets.
Select all of Publish to Tableau Server related assets (currently there are four).
Including these assets when publishing should allow the workflow to publish and run successfully in the Gallery.
When importing a .xls file using the Input tool, Alteryx produces NULL values for specific cells, why does this happen?
By design, Alteryx uses either the Jet or ACE drivers when importing .xls files. In short, it’s not really an Alteryx issue but rather a limitation on the Microsoft drivers, but there’s always a workaround available, and we’ll dig into it later.
One of the common reasons this happens is due to the logic both the JET and ACE drivers follow. By design, they look at the first eight rows in your document when identifying the data types and their lengths, when it comes to handling Legacy Excel files. Translation, please? If at least one cell within the first eight rows of your file contains more than 255 characters, then the drivers will set the data type as “memo” (i.e., a length that can hold up to 32,768 characters). Now if the data type doesn’t exceed 255 characters within the first eight rows the driver sets the data type as a string with a 255-character restriction. How does this relate back to you? Well, if any of your cell values that are after the first eight rows do surpass 255 characters, then the driver will assign a NULL value to those cells. Confusing, right? It’s a good thing all is not lost, at least not yet.
The user has the choice to work around this issue or fix it in their registry. The workaround includes the following:
From the Input Data Tool configuration, check option 6: "First row contains data." This will force all fields as a V_String.
Configure the Dynamic Rename Tool to "Take Field Names from First Row of Data."
Use the Auto Field Tool to assign the best field type for each data column correctly.
The fix for this involves changing a setting in your registry. These instructions are being provided as well as documented from Microsoft, as Alteryx cannot be sure that changing this setting will not cause problems with other applications. For more information on this issue: http://support.microsoft.com/kb/189897.
To change the Registry setting***
Go to Start > Run and type "regedit"
In the registry go to HKey Local Machine > Software > Microsoft > Jet > 4.0 > Engines > Excel
Double Click: TypeGuessRows
Change the value to 0 (zero)
Another workaround is to save the .xls file as a .xlsx. It’s well known that Microsoft hasn’t updated the legacy file extension since 1997. If this is an option you desire to exercise, fear not, there is a Macro for that, especially if you need to convert files by the masses.
Quick Shout out to DanJones for creating a fantastic app that takes a directory path and converts all xls files to csv or .xlsx respectively.
The post is located all the way in the bottom.
Disclaimer, when it comes to converting a .xls as .xlsx, please review if your workflow has a lot of formatting enabled, if so, please remember that sometimes this will potentially create inconsistencies with your data. It’s also possible to lose some data in the process.
*** For performance reasons, setting the TypeGuessRows value to zero (0) is not recommended if your Excel table is enormous. When the value is set to zero, Microsoft Excel will scan all records in your table to determine the type of data in each column.
Thanks for reading! If you have any questions, please message away!