Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

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

RyanNewsome
9 - Comet

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


 

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

  


 

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

12 REPLIES 12
RyanNewsome
9 - Comet

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-ToFileToSF...

Cassaad
6 - Meteoroid

Hello, can this work on Alteryx R-3.3.2?

JamesBills
8 - Asteroid

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??

 

Labels