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

[ibesmond]

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.

I want to give a shout out to @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/...

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.

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.

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%20ABC_CurrentAc...

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/

I have seen a number of posts thanks to responses from @chris_love and @jdunkerley79 to name a few.

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Can-Alteryx-unzip-a-file-as-part-of-th...

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%203.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+P...

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. 

Thank you.

[AbhilashR · Accepted solution]

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:

1. Use download tool to pull data from web inside Alteryx, and save it to the local machine using the blob tool
2. 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.

[ibesmond]

Hi @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?

[AbhilashR · Accepted solution]

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

[ibesmond]

Works perfectly.  Thank you.  

[ibesmond]

I wasn't sure if I should start a new thread, but I figured keep it all in one place.

I have a couple more challenges with pulling data from URLs. I'll direct it back at you @AbhilaashR since you were so helpful.

First off I can't figure out the issue with the Oregon URL. (File attached)

Secondly,  I have some other URL sites that require some options and button you have to select before the file starts downloading.

Is Alteryx capable of these functions?  I've listed a few URLs.

https://www.comapps.ohio.gov/liqr/liqr_apps/PermitLookup/PermitHolder.aspx  - This particular one I want to filter for Permit classes A1, A1A, A1C, A3, A3A & A4 and it has a image code, so I'm guessing it isn't possible.

https://apps.nd.gov/tax/tap/_/#4 - This one you have to click on "Alcohol License Lookup", then you can do a search leaving the fields blank. Or can add in a search field to filter the data.  Once the results come back an export button appears in which you can download an ods file.

https://abc.nc.gov/Search/Permit - This one you can add filters and then run it.  Or you can just run it without filters.

[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