output to CSV / ZIP / S3 bucket / SFTP macros (attached)

[RyanNewsome]

Hi, I thought I should share the attached macros and hopefully others can make use of them.

All have been tested and productionised in our environment, except for the "toSFTP" macro. This is because it hasnt been fully tested as our AWS/Alteryx environment has FTP traffic disabled, but it should work :)

I was getting an error "the contents of the attachment doesn't match its file type" when trying to attach files using IE11. Using Chrome instead was the workaround.

The following outlines the functionality and dependencies of each tool:

toCSV

Functionality

• Outputs incoming data as a .csv at a selected destination
• Appends _yyyymmddhhmmss to selected filename
• Allows continuation of workflow after data creation of .csv

Dependencies

• none

toCSVtoZIP

Functionality

• Outputs incoming data as a .csv at a selected destination
• Appends _yyyymmddhhmmss to selected filename
• Adds the .csv to a compressed .zip having the same name as the .csv
• Deletes the original .csv created so only the zipped .csv remains
• Allows continuation of workflow after creation of .zip file

Dependencies

• toCSV macro
• Rtools installed on server running workflow - https://cran.r-project.org/bin/windows/Rtools/
• After Rtools install, copy C:\RTools\bin\zip.exe to C:\program files\alteryx\R-3.1.2\bin as well as C:\program files\alteryx\R-3.1.2\bin\x64 and C:\program files\alteryx\R-3.1.2\bin\x86

toCSVtoZIPtoS3 / toCSVtoZIPtoS3andDelete

Functionality

• Outputs incoming data as a .csv at a selected destination
• Appends _yyyymmddhhmmss to selected filename
• Adds the .csv to a compressed .zip file, having the same name as the .csv
• Deletes the original .csv created so only the zipped csv remains
• Uploads zipped csv file to selected Amazon S3 location (based on AWS Access/Secret key and bucketname/foldername)
• (toCSVtoZIPtoS3andDelete version only) - Deletes the zipped csv from the intermediary destination
• Allows continuation of workflow after deletion of intermediary file (or after transfer to S3, if using the toCSVtoZIPtoS3 version)

Dependencies

• toCSVtoZIP macro and all of its dependencies
• Amazon CLI installed on server running the macro (https://aws.amazon.com/cli/)

toCSVtoZIPtoSFTP

 **Make sure to change the text "\\\\servername\\driveletter\\foldername\\WINSCP\\WINSCP.COM" inside the R tool to represent where your WINSCP.com file is located

Functionality

• Outputs incoming data as a .csv at a selected destination
• Appends _yyyymmddhhmmss to selected filename
• Adds the .csv to a compressed .zip file, having the same name as the .csv
• Deletes the original .csv created so only the zipped csv remains
• Uploads zipped csv file to selected SFTP server/directory
• Allows continuation of workflow after upload to SFTP server

Dependencies

• toCSVtoZIP macro and all of its dependencies
• WINSCP.com (currently looks for this at \\alteryx-server\F\ReportingDependencyApps\WINSCP. This could be changed within the macro setup).

Background User Story

 As a BI developer, I want to be able to:

1. Construct a denormalised datamart table using Alteryx, so the complex rules are transparent
2. Output the datamart table as a compressed (.zip) .csv file, with timestamp in the filenames
3. Get the compressed file to an Amazon S3 bucket
4. Do not leave any other copies of the compressed .csv file anywhere apart from the Amazon S3 bucket
5. Achieve all of this and have it contained in a single Alteryx workflow

Reasons why custom macros were required

Alteryx has a robust tool that allows you to output an incoming data stream to a long list of file types, but not to a compressed file

Alteryx also has a great "s3 upload" tool that allows you to output an incoming data stream to a file on Amazon s3, in formats .yxdb, .arvo, .csv and .json, but not to a compressed file

[dataMack]

Thanks for sharing these!  I see a lot of interesting problems that you tackled through the series of macros.  Hopefully SFTP becomes a standard output feature in Alteryx.

Since it looks like your one macro requires it, here's the link to the WinSCP program: https://winscp.net/eng/download.php

[FeliceM]

This is awesome.  I didn't know you could use R this way.  When I try the zip portion, I get an status 127 error.  The solution I have found on the internet suggests adding the R bin path to the environmental variables is the way to fix this error, but that did not work for me.  Did you have to do anything special to get the zip function in R to work?

[RyanNewsome · Accepted solution]

Hi Felice,

Just checking have these been done on the computer you are running it on?

• Rtools installed on server running workflow - https://cran.r-project.org/bin/windows/Rtools/
• After Rtools install, copy C:\RTools\bin\zip.exe to C:\program files\alteryx\R-3.1.2\bin as well as C:\program files\alteryx\R-3.1.2\bin\x64 and C:\program files\alteryx\R-3.1.2\bin\x86

[FeliceM]

That fixed it.  Thank you.

[dberenso]

Hi,

I'm using the: CSVtoZiptoSFTP macro, however, I'm getting the: 'had status 127' error.

Here's the entire message (specific machine/site details removed):

Warning: toCSVtoZIPtoSFTP (2): Tool #19: running command '\DELL--dmin$\WinSCP\WINSCP.COM /command "option batch on" "option confirm off" "option transfer binary" "open sftp://----:--@----" "put ""\----"" ""//upload/Address_Test.zip""" "close" "exit" test' had status 127

Any suggestions?

[RyanNewsome]

If you run the same command using WINSCP.COM without Alteryx, does it work?

If it does, then it could be Rtools not installed and relevant files not copied to Atleryx directories? (see above)

If it doesn't, then you'll need to change the macro to use the same command you usually use with WINSCP.

[alex_reevoo]

Hi Ryan,

I've downloaded R-tools as mentioned, however I have two problems:

1) I can't find the C:\program files\alteryx\ path, the only files I have are in appdata (I imagine this is actually be being dumb but maybe you can assist)

2) The error (which I imagine is related to the above) I get from a couple of tools is "Can't find plugin "AlterysRPluginEngine.dll" - could you shed any light on this? I have a similar business requirement to the above so this would be massively helpful.

Cheers,

Alex

[patrick_digan]

@RyanNewsome Thanks for sharing! The ability to use the R tool to zip up output files is great. 

[RyanNewsome]

Hi Alex,

1.) Replace c:\program files with whatever the nominated install directory was. It could even be on a totally different drive. Check with whoever installed it, or maybe just do a Windows file/folder search for Alteryx or check where the Alteryx shortcut is pointing to. As long as the directory you find has the R-3.1.2 sub directory, then you know you have found the right one :) . Which leads to......

2) Make sure the R package for Alteryx has been installed. http://downloads.alteryx.com/Latest_RInstaller.htm
If you can't see the "R" tool inside Alteryx then this would definitely be the case.

I recently created a cleaner and more flexible version of these macros. It allows much more flexibility of the output format of the file, and the toSFTP macro is fixed (we now have SFTP access from our AWS environment yay).

Please note the above ToSFTP version doesn't work due to an incorrect "update values" formula and it also doesn't allow you to specify things such as SFTP host key and port number ..sometimes people don't like using the default port 22)

Currently I only have it built as ToFileToSFTP (with a tick box inside allowing you to opt whether to delete local file after it has been sent to SFTP server... so it's missing the zip functionality.

I'll post it on the forums once the ToZip part is built, but PM me if you want a copy of it at its current state.

Hope this helps,
Ryan