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!

Engine Works

Under the hood of Alteryx: tips, tricks and how-tos.
TashaA
Alteryx Alumni (Retired)

Connecting to a REST API from within Alteryx is a great skill to have. However, API documentation can be difficult to navigate, and figuring out where to place the required headers, query strings, and payloads can become confusing. To show how simple it can be, we will attempt to retrieve some stock data from Quandl, an online resource for free and premium datasets, in 5 minutes or less. We will be using the Quick Start guide for the API, set your timer…

 

1. Environment Setup

Open a new workflow, add a Text Input tool with a column titled 'url' and connect a Download Tool to its output.

base_workflow.png 

2. Request URL

The example in the quick start guide shows us what a URL should look like for a request for Facebook stock data, so we will add that as the value of the 'url' field in the Text Input tool. 

 

https://www.quandl.com/api/v3/datasets/WIKI/FB/data.csv

This will work for now, but in the future we may consider splitting the distinct pieces of the request url into unique fields, and compiling the string using the Formula tool, so that the same workflow may be used to download many different datasets. You may also notice the .csv at the end of the request string, this indicates that we want the request for data  to be returned in a .csv format, but we could also specify .json or .xml based on the Quandl documentation. 

 

3. Authentication

Based on the quick start guide, requests should be authenticated with an API key. Go ahead and sign up for a key. Add another field to the Text Input tool, name the field api_key and add your key as the value for that field. The Text Input tool configuration should look something like this:

 

 text_input.png

 

4. Configuring the Download Tool

Basic Tab

Select your url field from the dropdown for URL, all other Basic configurations can remain the same.

 

Basic_tab.png

 

Headers

Default Settings

 

Payload

The HTTP method will be the default selection,  'GET (or FTP)'. The radio button for 'Compose Query String' should be selected. A query string is what comes after the question mark in the URL path. From the list of fields available, select the field containing your API key.

 

payload_tab.png

 

Connection

Default Settings

 

5. Workflow Execution

Add a browse tool to the end, or add an Output Data tool if you would like to save the dataset. Run the workflow and the download tool will add two fields to your workflow, Download Headers and Download Data. The Download Data field will have the Facebook stock data in csv format. Realizing that this may not be the easiest to read, you can add a Text to Columns tool after this to parse the data into a more readable format. 

 

executionResults.png

 

Coming soon, I will take a deeper dive into the multiple Download Tool configurations, as well as what to look for in API documentation. Look forward to coverage on other advanced topics such as  batching your request data, paging for large sets of results, and other authentication methods.

 

What questions do you have about the Download Tool? 

Tasha Alfano

Tasha loves working with Alteryx products, building time-saving tools, and talking to customers! When she isn't at the Colorado Alteryx office, she's usually running or biking on a trail nearby. Twitter: @Tasha_Alfano

Tasha loves working with Alteryx products, building time-saving tools, and talking to customers! When she isn't at the Colorado Alteryx office, she's usually running or biking on a trail nearby. Twitter: @Tasha_Alfano

Comments
TashaA
Alteryx Alumni (Retired)

Hi @mattamidus

 

Did you include the custom token (step 1 on the docs?) 

 

Could you include some screenshots of your configuration or a workflow? 

 

Best, 

 

Tasha

Deb_dup_88
6 - Meteoroid

Hi Tasha,

 

Have you tested out an API with Microsoft Graph at all?  I need to connect to our Office 365 tenant to obtain data from Outlook & Skype initially, but then other O365 apps down the road.

 

Appreciate any pointers you can provide.

 

thanks,

 

renanpinheiro
7 - Meteor

Hi @TashaA
you showed an example of Facebook. Are you looking for LinkedIn information?

Hakimipous
10 - Fireball

Would love to see more articles about API

NeilR
Alteryx Alumni (Retired)

@Hakimipous here's a discussion happening right now, and don't miss the Knowledge Base series.

Hakimipous
10 - Fireball

@NeilR Thanks for sharing !

Karthickskumar
5 - Atom

Hi , 

 

I would like to recreate the following command using a Download tool. I am able to do the same using the command tool, but I need to use a cURL utility for that, wondering If I can use the download tool to achieve the same.

 

The command I am executing in the cURL 

-k --negotiate -u : <URL> -o <.OutputJsonFileLocation>

 

How will I recreate this using a download utility's query builder or any other feature? 

if I only use the url in the download tool. I am getting an authentication error

 

Thanks,

Karthick

KaneG
Alteryx Alumni (Retired)

Hi @Karthickskumar,

 

You are better off asking this question in the main forum, as then everyone will see it, whereas on this post, only the people who've previously replied will get a notification (if they have those notifications turned on, that is).

 

In this case, I suspect that the -k is your issue as the download tool doesn't have a place to refer to a file unless it's a full query string, which this is not. I'm also not sure how you would specify --negotiate, I think you would want to specify the negotiation method instead.

 

Try separating the query string out from the file specified after -k and put each element into the headers or payload depending on what it is and if that doesn't work, post a question on the above tagged forum with some more detail on which api it is or what the tags are inside the file.

 

Kane

mattamidus
7 - Meteor

Hi @TashaA

 

Have you ever been able to use the http://webhose.io API withing alteryx?

Im having trouble getting it to work when I include filters.  After some troubleshooting Im kind of running out ideas.  Any of your insight would be most appreciated.  

 

 

TashaA
Alteryx Alumni (Retired)

@mattamidus,

 

Which API set from webhose are you using?

 

The blog search, news search, monitoring, web, etc.

 

Thanks for your question!

 

Also, where are you putting the filter parameters? You could try 3 approaches: 

 

  1. Build the entire query string in the formula tool, and just configure the url field within the download tool
  2. add key value pairs in the payload tab of the download tool
  3. build the query string within the download tool

-Tasha

TashaA
Alteryx Alumni (Retired)

One other note @mattamidus, I noticed the blurb about escaped characters on the API reference page:

 

image.png

 

You may need to try some different configurations for the escaping. The download tool automatically encodes your request, so you may want to try toggling that off.

 

If all else fails, I would examine the traffic in Fiddler, or perfect my request in Postman prior to translating it to the Download Tool.

 

Best of luck!

JohnBell
8 - Asteroid

Hi Tasha,

 

Great article...concise...showed the configurations in detail...

 

Here's my error ...after the URL and api_key fields, I'm getting the following...

 

Thanks...

 

image.png

cjameson
6 - Meteoroid

I am brushing up on my skills with Alteryx and after completing this simulation wanted to get the data into a usable data table out of a single column. I used the text to columns tool 2 times, then a dynamic rename. Hopefully this is helpful to some newer users out there like me :) 2018-07-20 11_23_22-Chelsea vPC ‎- Remote Desktop.png

 

JohnBell
8 - Asteroid

@cjameson

 

Thank You!!!  I'm learning about APIs and then being able to consume the information efficiently.

 

I appreciate your very clear and precise visual above; I was able to replicate.

 

Thanks again!

John

 

Brujan14
5 - Atom

Hi -

 

great article - just want to add a comment about the "text to columns" to make the data usable...  would be curious if others were able to do this differently - but I needed to use two "text to column" tools, the first to separate the rows, and then a 2nd to separate the columns.  

 

in the first, the delimiter is "\n" - so that you are separating into rows based on the carriage return at the end of each record (versus the default comma, which would result in a new row for every value between commas).

 

Picture1.png

 

the second tool then split to columns, using the comma as the delimiter:

 

Picture2.png

 

this then returns the 1473 records that match what I get when I download the CSV from the url, and open in Excel...