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 toCSVtoZIPFunctionality* 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 / toCSVtoZIPtoS3andDeleteFunctionality * 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: * Construct a denormalised datamart table using Alteryx, so the complex rules are transparent * Output the datamart table as a compressed (.zip) .csv file, with timestamp in the filenames * Get the compressed file to an Amazon S3 bucket * Do not leave any other copies of the compressed .csv file anywhere apart from the Amazon S3 bucket * Achieve all of this and have it contained in a single Alteryx workflow Reasons why custom macros were requiredAlteryx 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 toCSV.yxzp toCSVtoZIP.yxzp toCSVtoZIPtoS3.yxzp toCSVtoZIPtoS3andDelete.yxzp toCSVtoZIPtoSFTP.yxzp

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

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:

Construct a denormalised datamart table using Alteryx, so the complex rules are transparent
Output the datamart table as a compressed (.zip) .csv file, with timestamp in the filenames
Get the compressed file to an Amazon S3 bucket
Do not leave any other copies of the compressed .csv file anywhere apart from the Amazon S3 bucket
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

toCSV.yxzp

toCSVtoZIP.yxzp

toCSVtoZIPtoS3.yxzp

toCSVtoZIPtoS3andDelete.yxzp

toCSVtoZIPtoSFTP.yxzp

Macros

Accepted answers

RyanNewsome

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

All comments

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

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

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.

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

RyanNewsome

Please refer to this post for new and improved versions of these macros:

http://community.alteryx.com/t5/Data-Preparation-Blending/ZIP-and-SFTP-ToFile-ToFileToZIP-ToFileToSFTP-ToFileToZipToSFTP/m-p/28875

JamesBills

Hi. Don't suppose anyone has used this lately? Long shot as I can see it was posted 5 years ago now!

But this seems like the perfect solution for a problem I have. I run the csvZiptoS3 macro with no errors, and tells me it has run succesfully but i get nothing. It hasn't produced the zipped csv and not sent it over csv.

Anyone ran into the same? Wonder whether it's a change in setting since this was published.

I.e. using 3.1.2 r tools??

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703