02-10-2023 08:59 AM - edited 05-31-2024 08:13 AM
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.
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.
Read on to learn more about the input tools available in Alteryx Designer.
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 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.
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:
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:
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.
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.
The Data Connector tools are available to download for free on Alteryx Gallery. When you click on one of these tools on the Gallery, you will see a link to the help page, which has more information on the tool.
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.
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:
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:
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:
If you are seeing errors when connecting to databases, check out these knowledge base articles and discussion threads:
Do you have a discussion forum thread, blog, or other content on Community that has helped you when connecting to data? Drop it in the comments below.
Insightful..!
Thanks @MeganBowers for another great article 👏