Alteryx Designer Desktop Discussions
Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
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
- 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
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/)
**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).
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
Solved! Go to Solution.
