Dynamic input tool

[jonathanogrady]

Hello fellow Alteryx fans!

I'm trying to use the dynamic input tool to upload hundreds of text files, but I'm hitting a brick wall for some reason.

Each of the text files is located behind an individual zip file. So the typical format would be:

Directory\file_1.zip -> file_1.txt
Directory\file_2.zip -> file_2.txt
Directory\file_3.zip -> file_3.txt

I am using the directory input tool to target the specific folder. How should I configure the dynamic input tool to download and join each of the files which are in exactly the same format?

As always, community help very much appreciated!

Best wishes,

Jonathan

[DavidP · Accepted solution]

Ok, so here's how I do it:

From the Directory tool output, use a Select tool to remove everything except [Fullpath] and [Filename] - this step is not strictly necessary.

You now have to update [Fullpath] to look like below. I did it by replacing the 'zip' in [Filename] with 'txt' and updating [Fullpath] with the formula:

[FullPath]+'|||'+[FileName]

Now you can use [Fullpath] as the input field for the dynamic input tool.

See workflow attached.

[jonathanogrady]

Your genius! Sometimes it's the simple things that give us the most trouble!

Very much appreciated! Jonathan

[jonathanogrady]

David,

A quick supplemental question for you… How do you handle it if the text file is inside a zipped file, which is itself part of a larger zipped file?

It seems that simply targeting include subdirectories won't handle this…

Best wishes,

Jonathan

[DavidP]

I don't know that Alteryx can handle this - I certainly think it's beyond the dynamic Input tool's capabilities. 

All I can think of is to use a Run Batch Command tool to unzip the larger zip file with a dos command and then use the workflow above to extract the data from the remaining zip files.

With the Run Batch tool, you can construct a DOS command in a formula tool that can be executed by the Run Batch Tool

Perhaps someone cleverer than me will have a better suggestion.

[jonathanogrady]

David,

Many thanks for giving this your consideration. I'll find some work around!

Best wishes,

Jonathan

[DavidP]

Ok, so I played around with it a bit, and came up with this:

I created a zip file containing 3 zip files that contain 1 txt file each - zip.zip, which in my case is stored here: C:\users\davidp\downloads\zip\zip.zip

The Text input file defines the location of zip.zip and the start of the dos command that will unzip zip.zip using 7-zip, which I have installed.

In the formula tool I then construct the full dos command which first changes directory to the Alteryx temp folder by using [Engine.TempFilePath] and then calls the 7-zip command

This command is then executed with the Run Batch Command Tool.

I then used a block until done and join tool to hold the workflow until the files are unzipped.

After this, I call a simple batch macro that uses the directory tool to read the .zip files in the temp folder.

Then, as before, I use the dynamic input tool to extract the data from the txt files in the individual zip files.

I hope this is useful to you, but I had fun playing with it anyway!

[JohnR]

Thank you!

[shnbrb]

Hi David - Thanks for providing this, I found it really helpful in my workflow, however I did find that it would error if i had non-alphanumeric characters in the filenames in the .zip.

I found that replacing the second line in the R code with the below fixed that.

out<- paste0("'",unzip(file.path(FilePathIN$FullPath), list = TRUE),"'")