Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Maveryx Community Resources

Get up-and-running on the Community and with Alteryx in record time.

Connecting to Data Guide

MeganDibble
Alteryx Community Team
Alteryx Community Team
Created

Banner Connecting to Data.png

 

Heading 1 (3).pngGetting 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. 

 

Screen Shot 2022-12-19 at 3.05.59 PM.png

 

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. 

 

Heading 2 (1).pngTools & Methods in Alteryx Designer 

 

Read on to learn more about the input tools available in Alteryx Designer. 

 

Input Data Tool 

 

DbFileInput.png

 

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: 

 

Screen Shot 2023-01-27 at 12.06.45 PM.png

  

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 

 

Directory.png

  

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. 

 

8.png

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 

 

DynamicInput.png

 

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. 

 

3 (2).png

Dynamic Input tool configuration 

 

You can also review these discussion solutions for example use cases for the Dynamic Input Tool: 

 

Connect In-DB Tool 

 

LockInInput.png

 

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: 

 

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. 

 

flowchart.PNG

 

If you find that you need to create a batch macro to read your files, then you can reference this example. 

 

Heading 3 (1).pngData 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. 

 

Screen Shot 2023-01-24 at 9.20.02 AM.png

 

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. 

 

Heading 4 (1).pngData 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: 

 

Heading 5 (1).png

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: 

 

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.  

Comments
Raj
13 - Pulsar

Insightful..!

Wojtek_Dz
8 - Asteroid

Thanks @MeganDibble  for another great article  👏