Trouble pulling data (csv, xls, slsx, .zip) from URL/Website Address

Hello,

First off I want to say thank you to everyone's contributions. I am new to Alteryx, having been working with the software for about 3 days now.

I'm having trouble connecting to some URLs, and was hoping someone with more experience might be able to shed light on these.

I have looked through the community extensively, and haven't seen exactly what I'm caught up on.

jamielaird for his response to another tread, that has been very helpful.

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Downloading-Excel-File-from-a-URL/m-p/398897#M73300

If works for most of the formats I have used in excel so far. I just modify the file name extension - csv, xls, xlsx on the text import and dynamic input data source template. .temp\csv or .\temp.xls or .\temp.xlsx depending on which extension the URL comes in.

Text Input - csv.png Dynamic Input - csv.png

Most seem to work very well. Sometimes if I get an error, I get rid of the File Name column from the Text Input tool, and I change the Download tool section "To a File" and select "Temporary File" and change the Dynamic Input data source template.

Dynamic Input - csv2.png

For this method I do not have to create templates in a directory which is nice.

I wanted to include that, so it wasn't repeated, and show you the methodology for how I am trying to import data.

1. The first problem I am having is connecting to this URL xlsx file hosted here.

https://www.commerce.alaska.gov/web/Portals/9/pub/ABC/OtherAlcoholResources/03182020 ABC_CurrentActiveLicenses.xlsx

https://www.commerce.alaska.gov/web/amco/OtherAlcoholResources.aspx

I trouble shot this with Alteryx technical support without success.

2. The second problem I am having is connecting to this URL zip file hosted here.

https://www.abc.ca.gov/wp-content/uploads/WeeklyExport_CSV.zip

https://www.abc.ca.gov/licensing/licensing-reports/licenses-by-statewide/

chris_love jdunkerley79 to name a few.

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Can-Alteryx-unzip-a-file-as-part-of-the-workflow/td-p/10604

Can't seem to get it to work and fell like there may be an easier solution.

3. The third problem I am having is connecting to this URL xls file hosted here.

https://www.colorado.gov/pacific/sites/default/files/LiquorLicenses 3.2.2020_0.xls

https://www.colorado.gov/pacific/enforcement/liquor-license-lists

The problem I am having here is the data starts on the third line, and unlike xlsx files that you can specific what line the data starts, xls files don't have that option in the dynamic input data source template. Looking for the easiest work-around.

4. The fourth problem I am having is connecting directly a file on the URL hosted here.

https://isp.idaho.gov/AbcLicenseSearch/searchLicenses.html?licenseId=&dba=&address=&addOns=Brewers+Pub&addOns=Brewery&startDate=&city=&issueDate=&zip=#

Is there anyway to get to the file automatically. The URL gets me to the page with all my filters intact, but not to the file.

Link to cvs link.png

Thank you.

Datasets

Input

Tips and Tricks

Accepted answers

AbhilashR

Hi @ibesmond,

Thank you for providing us detailed specifics of what you were looking for! Attached workflow fetches data from the web url's you provided. I took a two step approach in fetching these files:

Use download tool to pull data from web inside Alteryx, and save it to the local machine using the blob tool
Read the saved file using Dynamic Input tool and process it further in Alteryx

Each download is treated independent to the other given each has their own specific file extension (xls, csv, zip & xlsx).

There might be areas where my approach can be optimized but should give you a good starting point.

Hope this helps! Please let us know if it doesn't work at your end.

sampleDownloads.yxzp

AbhilashR

Hi @ibesmond, I have tweaked the California solution to accommodate for changing date. Let me know if this gives you trouble.

california.yxmd

All comments

AbhilashR

Hi @ibesmond,

Thank you for providing us detailed specifics of what you were looking for! Attached workflow fetches data from the web url's you provided. I took a two step approach in fetching these files:

Use download tool to pull data from web inside Alteryx, and save it to the local machine using the blob tool
Read the saved file using Dynamic Input tool and process it further in Alteryx

Each download is treated independent to the other given each has their own specific file extension (xls, csv, zip & xlsx).

There might be areas where my approach can be optimized but should give you a good starting point.

Hope this helps! Please let us know if it doesn't work at your end.

sampleDownloads.yxzp

ibesmond

AbhilashR ,

The first, third and fourth work flow performs perfectly. Thank you!

The second workflow is has an issue with the Text to Columns tools because of a date/time stamp that refreshing daily in the first line of the worksheet.

"Updated Wednesday 1st of April 2020 11:00:06 AM" for example - Any thoughts? I can re-run it and manually change the date and deselect the first row on the Multi-Field Formula tool and the work flow performs great. I also added in a couple more tools, and almost every time I run the workflow it crashes and closes out the program. Any ideas why this might be happening or ways I can simplify it?

California.yxmd

AbhilashR

Hi @ibesmond, I have tweaked the California solution to accommodate for changing date. Let me know if this gives you trouble.

california.yxmd

Raj_007

Hi, Thank you so much. I was going the same thing and in one approach where we call it as "local save" - workflow runs fine on my designer but when i upload the workflow into alteryx servr it is failing... which method should we use so that the workflow will run smoothly on the alteryx server

koushik320

Hi kindly help me to get the sample data that is used for training purpose

\Alteryx\Samples\data\SampleData

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703