Unable to open archive for unzipping in Dynamic Input

[Seffana_]

Hello, 

I am trying to download and clean all the files in this link.

Only interested in the tabs that have DRL0102 and DRL0132 in them. 

I have downloaded the data using the Download tool to a Temporary File. Then I used the Dynamic Input tool with the following configuration. For some reason this errors out with Unable to open archive for unzipping ... message.

Does anyone know why this is? Is there an easier way to get all of the files with the sheets that I want?

Thanks in advance.

[AbhilashR]

Hi @Seffana_, I modified your workflow to make it download the individual files onto your machine as .xlsx file. You can use the dynamic input tool from here on and read in the data into Alteryx. Does this help?

I also unchecked the Encode URL Text to make it work.

[Seffana_]

Hi @AbhilashR, 

Thank you for responding so quickly!

I still have an issue. I cannot read in the data properly yet. I should add that I have changed the Blob Output configuration to the image below. This is so that it can go to the file path that I want rather than my Downloads folder.

Here's the configuration for the Dynamic Input. 

Some of the data from that URL link is an .xsl as well as .xslx file type. Annoyingly, not all the sheets are named the same across each file, and I want two specific sheets from each file.

Do you know what the best way is to get to that?

Also, out of curiosity, how come you opted for the Blob option?

[AbhilashR · Accepted solution]

@Seffana_ - thank you for pointing out the different excel formats, totally missed that one.

I have modified my solution to treat .xls and .xlsx files as separate substreams, and introduced macros to read all tabs in one shot. Stream for .xlsx files bring in all tabs, but couldn't find a dynamic way of reading all .xls tabs. You can manually update the Text Input tool  (tool ID 58) and bring in data from relevant .xls tabs.

Hope this helps!

[AbhilashR]

Hi @Seffana_, I was curious to know if last solution I posted on this thread work for you, please let me know. 

[Seffana_]

Hi @AbhilashR, extremely sorry for the delayed response. 

Yes, your method had worked! Thank you so much. 

out of curiosity, how come you made the download output a blob?

[AbhilashR]

Hi @Seffana_. I was looking to explicitly download all the excel files into one folder I could play with, without necessarily reading or changing the file in any way, hence the Blob.