Connecting to Data Guide
- Getting Started
- Data Connections
- Tools & Methods in Alteryx Designer
- Input Data Tool
- Directory Tool
- Dynamic Input Tool
- Connect In-DB Tool
- Bringing in Multiple Files
- Data Connectors
- Data Connection Manager (DCM)
- Troubleshooting Common Errors
Getting Started
If you are new to Alteryx Designer, then inputting your data might be the first challenge you face when learning the software.
Thankfully, we have a great place to start—the Inputting Data Into Your Workflow Interactive Lesson.
This lesson explains how to use the Input Data tool (which we will cover in more detail in the next section), and shows three file type examples: .csv, Excel, and .yxdb. These are not your only options though. To see all the supported data sources and file formats, review this documentation. Additionally, Alteryx help has a document you may find helpful about file type support.
Data Connections
If you want to set up connections to databases, then take a look at this help documentation on managing data connections and this one on ODBC and OLEDB Database Connections. There is also documentation on connection setup considerations.
If the data you want to connect to does not have an ODBC driver available, then this advanced blog walks through the technical steps to connect via JDBC.
There is also the Alteryx Data Connection Manager (DCM), which is a centralized, secure way to store connections and credentials in an organization. We will go into more detail about DCM later in the guide.
Tools & Methods in Alteryx Designer
Read on to learn more about the input tools available in Alteryx Designer.
Input Data Tool
You can use the Input Data tool to add data to your workflow by connecting it to a file or database. There are many configuration options for this tool. To learn more, check out the example workflow in Alteryx Designer by clicking on the tool in the tool palette:
To see more examples of the tool in action, you can review the Input Data Tool Mastery article.
A few Input Data options worth mentioning:
- You can use the Input Data Tool with a wildcard (*) to input multiple files of the same schema from a single directory
- You can output the file name as a field to use later in your workflow
- You can set a record limit to only import a certain number of records (and reduce processing time)
- You can use the Input tool like a data connector when you select “Quick Connect” from the Data Sources options page
Directory Tool
You can use the Directory tool to return a list of all the files in the specified directory. The tool returns file names and other information about each file, for example, file size, creation date, last modified date, and more. For some detailed examples of what the tool can do, take a look at the Directory Tool Mastery article.
Example Directory output
The directory tool is useful when you want to import lots of files at once. You can read about a use case for the tool in this article, where four similar (but not identical) Excel files must be brought into Alteryx. The more dynamic solutions presented make use of the Directory tool.
This discussion solution contains an interesting macro that uses the directory tool. The macro allows you to specify a file path and file name (including wildcard characters) and then determine whether that path/name combination matches any existing files.
Dynamic Input Tool
You can use the Dynamic Input tool to dynamically input different data sources—whether that's changing file names, updating database queries, or changing input paths that are built using data from your workflow. If you are reading from a database, Alteryx will read from it at runtime and dynamically choose what records are read in.
If you find that working with the Input Data tool is getting repetitive or too manual, the Dynamic Input tool might be a good solution. You can also right click on an existing Input tool and select Convert to Dynamic Input tool.
You can specify a template data source and then read in as many files as needed that match the structure of the template. Check out the Dynamic Input Tool Mastery article to understand the possible uses of this tool.
Dynamic Input tool configuration
You can also review these discussion solutions for example use cases for the Dynamic Input Tool:
- Dynamically change SQL where clause with user input
- Dynamic Input tool using relative file paths
- Dynamic Input tool - Must specify file name
Connect In-DB Tool
The Connect In-Database tool enables blending and analysis against large sets of data without moving the data out of a database and can provide significant performance improvements over traditional analysis methods.
Once you use the Connect In-Database tool to bring in data, you can use the other In-DB tools to prep and blend your data, and the processing will occur in the database environment. If you want to use regular Designer tools later in the workflow, then you need to use a Data Stream Out tool.
To learn more about In-DB tools overall, check out this overview.
We also have a blog series on the In-Database tools:
- The Ins and Outs of In-DB: Do Something Awe-Inspiring with AWS
- The Ins and Outs of In-Database, Starting with Snowflake
- The Ins and Outs of In-DB: Don’t Make Your Data Leave the Lakehouse
Bringing in Multiple Files
You might know what data you want to get into Alteryx but not know what the best tools to use are. When you have multiple files, the Ultimate Input Data Flowchart can help you decide which tools/methods to use. Below is the flowchart that you can use to determine where to start.
If you find that you need to create a batch macro to read your files, then you can reference this example.
Data Connectors
If your data source isn’t supported by the tools in the previous section, there’s no need to worry! We have a variety of Alteryx-supported data connector tools that you can download from the Alteryx Marketplace.
When you click on one of these tools on Marketplace, you will see a list of features and versions.
If you have Alteryx Server, then you will need to install the tools on the server as well in order to publish workflows with these tools.
Please note that there are some connectors that have been depreciated with the release of certain Alteryx versions. For more information, refer to this knowledge base article.
Data Connection Manager (DCM)
Alteryx Data Connection Manager (DCM) takes the first step towards enterprise management of your Alteryx data connections in a simple and reliable way. With DCM, users can enter and update credentials and re-use their credentials among tools and workflows. DCM is the unified, secure vault to store Credentials, Data Sources, and Connections. Available starting in version 2021.4, use DCM to improve security by moving your credentials outside the workflow and synchronizing them across the Alteryx product suite.
The benefits of using DCM are:
- Create a single connection to cloud data sources, databases, and other data sources, and use that same connection in multiple tools and workflows
- Allow workflow owners to update credentials in a single location and have that update propagate to all tools in all workflows.
- Abstracting credentials utilized within the workflow and storing them securely in DCM.
- Integrations with external vaults.
- Expanded authentication methods, such as Azure AD and SSO, for more secure connections to your data sources.
Some Data Connector tools require a DCM connection in order to use them on the Server. Contact your Server Administrator and request that DCM is enabled in your environment. The following resources are helpful for Server Administrators:
To learn more, check out the following resource:
Troubleshooting Common Errors
When you are inputting data into Alteryx Designer, you might run into errors. It is important to note that if you are connecting to a data source at your company, issues may arise due to internal IT procedures, security, or database setup. Make sure to run the error by your database administrators or IT before posting about it on Community—they may be the best equipped to help solve your issue.
To troubleshoot on your own, you can follow the steps in this knowledge base article to connect to data outside of Alteryx in order to determine whether the connection issue lies with Alteryx, the database, or is on the client side.
If you are getting an error when importing flat files, then refer to these knowledge base articles and discussion threads:
- Too many fields in row x when reading in a .csv file
- Troubleshooting: ERROR: "Input Data (1) CSVFile: A field was missing a closing quote in record"
- Error : "has a different schema than the 1st file in the set and will be skipped"
If you are seeing errors when connecting to databases, check out these knowledge base articles and discussion threads:
- Error opening connect string when publishing to Alteryx Server
- Error: "SSL Security error" after disabling TLS on SQL Server
- Error when converting a DSN-less connection from ODBC to OLEDB
