Import water data from a public API into an Alteryx workflow

[jlange3932]

I'm trying to import data from this api endpoint: http://data.chesapeakebay.net/api.csv/WaterQuality - I know the normal workflow uses the text to columns tool where I add that URL in the url column and then have the download tool configured correctly I believe (Basic tab is looking at the url column and payload tab is doing a GET with the Compose Query String/Body field selected). When I add a browse tool at the end of it, I get an error in the two columns that are created (DownloadData and DownloadHeaders) like it's not even reading the API. If I add a json parse tool betweeen the download and browse tools, the workflow is run but the fields output null data in the columns. I'm wondering what I'm doing wrong or the right process for importing data from this API is. Any help is much appreciated! Thank you!

I attached the simple workflow I started with the json parse tool so all the fields are nulled out in the Browse tool.

[DavidP · Accepted solution]

It looks like your missing some details from the URL.

Have a look at this one for instance

http://data.chesapeakebay.net/api.JSON/WaterQuality/Station/HUC8/3

Best is to try the urls in a web browser first and once you're happy with the result, paste the url into your text input tool

[jlange3932]

Oh wow yeah you are right. I thought something was up since that endpoint didn't give any response when entered just in the web url. When I gave it a valid url (like the one you suggested), got a valid 200 with the data needed. Now just to add some text to column tools to clean it! Thanks!

[DavidP]

You're welcome!

I assume you changed the JSON Parse tool's input to DownloadedData, right?

You can use Text To Columns to split the resulting Name field, then us a crosstab tool with the 2nd part of the Name field as name and the Valuestring as Value if you want to flip the data around

[jlange3932]

I actually just removed the json parse tool to see if the data was getting output first. I can add in those clean up tools after the fact, my main concern was why the data wasn't being input into the workflow! Was going to look at this site a bit as well to help https://www.thedataschool.co.uk/philip-mannering/typical-api-alteryx-workflow/. Again, thanks for the help.