Amazon S3 Zip file download issue

I have some reports being sent to my S3 bucket in a specific folder. The files have names that also includes the date, are zipped files that have a single csv file each.

I would like to use Alteryx to download the most recent file daily and unzip the file, read the csv, clean up the data and append to a table on my Snowflake server.

I have tried the use the Amazon S3 Download tool, as well as the Input data tool to do this. I tried setting up a macro using text input. However, everything I have tried has failed to get the data into Alteryx.

I have an existing Amazon Upload tool macro that works, with no issues.

Has anyone else had issues and found a clear way to do what I need? Does anyone have an existing workflow that can be “cleaned up” so that I can recycle and use to get my downloads in motion?

I would appreciate any assistance you can provide! Thanks!!

Macros

Amazon S3

Batch Macro

Download

Accepted answers

All comments

OllieClarke

Hi @DBarenburg

What didn't work with the S3 download tool?

DBarenburg

I can see and "grab" a single file, but I need it to be more automated so that it can go and get the file on a daily basis. Just selecting the file within the tool manually allows me access, but it doesnt solve the problem of choosing the correct daily file when the workflow is running on the server by itself on a schedule.

OllieClarke

@DBarenburg

the AWS CLI is the way to go here. There's a KB article about using it to do a 'wildcard input' with a batch macro.
https://knowledge.alteryx.com/index/s/article/Amazon-S3-Download-Use-Wildcards-to-Select-a-Single-or-Multiple-Objects-1583460182981

This command

cd\ cd program files\amazon\awscli aws s3 ls s3://alteryxtest > c:\users\\awslist.txt

changes directory to the folder of the awscli, then uses the ls command to list the contents of the s3 bucket, and writes that to a text file. This includes the timestamp of the files/buckets and so you can sort by this and only keep the most recent file.

the ls command is documented here: https://docs.aws.amazon.com/cli/latest/reference/s3/ls.html

Hope that helps,

Ollie

apathetichell

@DBarenburg --- Hey--- you are trying to do a few things which do not have out of the box support:

1) wildcard/.zip downloads from an S3 bucket.

2) wildcard/.zip extraction.

you will need to code this. It can be in python or as a .bat file --- but you will need to code this.,

apathetichell

your control parameter and action tool are set up wrong. don't use [#1] ---> use it in replace specific string mode. replace what you need -> keep what you need to be static by not including it in the replace specific string box.

DBarenburg

I tried both ways...let me ask this...if in my workflow, I get my file name from the S3, then push that file name into the macro as the object, then want the Action Tool to update the Object field with that file name value, so that it may update the Object name field in the S3 download tool, what would I do? It appears that I am doing something wrong, but not sure exactly what.

apathetichell

Can you post some screenshots of your config or like your .yxzp ---> this is pretty hard to do via your descriptions. if you are using an action tool to edit the alteryx standard s3 output tool you'd modify the object name in the action tool... you'd create a dummy name like MYFILE.XLSX ---> run it. test it. confirm it works in the macro with your dummy file. then run as a macro.. Note ---> this assumes static file types/bucket etc.

DBarenburg

Below are the screenshots of the workflow (with notes in the comment lines), then the macro, and the details in each tool for the macro.

S3 Snapshot.jpg

apathetichell

Hey--- reiterating ---> if you are using an action tool to edit the alteryx standard s3 output tool you'd modify the object name in the action tool... you'd create a dummy name like MYFILE.XLSX ---> run it. test it.

you did not follow this instruction. you have to have a value in the object field in the S3 tool (or modify the xml which is a universe you are not exploring right now) in order to edit it in the action tool. you need to do this. Object -> dummy object. action tool -> replace dummy object-> save. rerun.

DBarenburg

I guess that's where I am lost. I am trying to bring in a daily file that changes names daily based on the date. I will still have hundreds of prior files in the folder and need to create 4 different workflows from that folder, based on name and file date. If you are saying create an xlsx file, I am not sure what that will do, when the files are in CSV, and I need to access them in their ZIP files in the S3 folder. I would hope to NOT download the data and just access the data from the S3. It is a very considerable size for each file (between 5 and 10 GB) per file.

If using the xlsx file is just to somehow recall or utilize the filename, then please explain that. If you are expecting me to use that process to download the data into a local file, that I cannot do at this time.

Can you offer any suggestions or more assistance for my next steps?

apathetichell

step 1:

create a value in the object field... if your final results are .zip --> put something called "test.zip" -preferably put something you can set up.

step 2:

configure action tool to replace this value. action tool is going to replace the value in object -> but object needs to have a value first. otherwise it will do nothing-> except give you that error.

step 3 and on ---> figure out your external (.zip) and internal (.csv) filename logic. I mentioned from the get go there are lot of parts here which are not supported out of the box. This is going to be a slog. I would use aws cli/python/lambda functions. First things first -> get your batch macro working and to do that you need a value in object so your action tool can replace it.

DBarenburg

I am not sure what the issue is, but I still cant get this to work. The batch macro doesnt pull in anything. Ugh....I really dont want to push this off to IT, and I am limited to what I can do with Python due to system security constraints.

apathetichell

Screen shot of the config of your action tool?

the common issue here is the logic you are passing from your action tool is wrong.

DBarenburg

Action Tool.jpg

apathetichell

Hey ---> so without seeing what your editing/what your replacing and without knowing what the file name looks like this is pretty much impossible to troubleshoot. TBH -> I don't even know where your issue is. It could be that you download your file -> but you can't access the underlying .csv. It could be that what's in the .zip isn't a csv. The most likely case is that you are replacing part of the S3 tool incorrectly and not specifying the correct object to download via your dynamic formula -> or not replacing a second key part of the S3 tool which needs to be replaced. I mentioned from the beginning that you are looking at a few things which may be complicated.

DBarenburg

Sorry, but our security is a bit tight, and I cant put certain things out there, so I have to black them out. Its the bucket name, the Access Key and Secret Key for the month part. I left the file naming portion visible in both cases. It is a zip file, and yes, it is a csv in the zip file. I can log in to the S3 and download manually and see the file. Just automating that part. I can see in my query that the Object name is being created properly and pushed into the batch macro. But that object doesnt seem to be getting placed into the Action Tool to get updated into the Object field. Not sure where that is going wrong.

apathetichell

right - but I need to see the replace a specific string part on the bottom. Ideally I'd just like to see what's being fed into the control paramter from the outer workflow. What's in the value in the action tool. and what's in the replace a specific string.

totally get security. I can't even auth into AWS CLI from my laptop. (can access some systems via browser -> if you tell that to your security team they'll look at you like you are crazy -> there are some mitigating factors).

DBarenburg

Action Tool.jpg

apathetichell

And this works when run as a macro but doesn't update the value when run in a workflow?

DBarenburg

It doesn't work as a macro. I've been trying and tweaking things since November, with no success.

apathetichell

OH -> When I say as a macro -> I mean can you run with the template/base data. You need to get that working first - and then add the variables/dynamic component.

what kind of error are you seeing?

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703