Alteryx Designer Desktop Discussions
Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
Hello,
I would like to share the following macros, which are improved versions of the macros I posted here - http://community.alteryx.com/t5/Data-Preparation-Blending/output-to-CSV-ZIP-S3-bucket-SFTP-macros-at...
It is awesome that Alteryx has allowed SFTP upload in version 10.5 by using the download tool, but hopefully in future versions the process will be made much simpler i.e. only having to use 1 tool. The following knowledge base article shows how to do it if you are interested - http://community.alteryx.com/t5/Alteryx-Knowledge-Base/Upload-to-SFTP-FTP-More-Examples-with-Alteryx...
There is also a SFTP upload macro that @danielbrun kindly shared, which can be found here - http://community.alteryx.com/t5/Data-Sources/SFTP-macro-for-Alteryx-upload-directly-to-SFTP/m-p/2135... - This is a nice simple one to use and may meet most peoples needs.
The biggest benefits of the following macros compared to the previous versions I posted are:
- Option to output file as a .csv or .txt
- Option to specify what delimiter character to use
- Option to show headers in first row of data
- Option to add a date stamped suffix or prefix to filename/zip filename
- Flexibility over the configuration of the SFTP server you want to send to i.e. sub directory to send file to (or root dir if desired), SFTP port number to use, explicit hostkey value
- Option to select whether to delete the local file/zipfile after it has been delivered to your desired SFTP server (applicable to ToFileToSFTP and ToFileToZipToSFTP macros - by default set to Yes)
IMPORTANT: Ensure to refer to Dependencies listed for each macro
toFile
Functionality
- Outputs incoming data to a selected destination (user has ability to output as .txt or .csv - default is .txt)
- Ensure to use full UNC path e.g. \\server-name\D\foldername\tempfiledirectory (especially important when deploying workflows to alteryx scheduler)
- Allows control over filename
- Allows control over delimiter character to use
- Allows a suffix or prefix to be added to the filename in one of the following formats (representing creation date or datetime)
- yyyymmdd
- _yyyymmdd
- yyyymmddhhmmss
- _yyyymmhhmmss
More could easily be added inside the macro setup if you desire - Allows the user to select whether to show column headers in the first row of data
- Allows continuation of workflow after data file has been created
If any mandatory fields are left blank, the macro will show an error and will not execute and cause your workflow to fail e.g.
Dependencies
- none
toFileToZIP
Functionality
- All functionality that the ToFile macro has (highlighted above)
- Adds the file to a zip file, with the zip file having the same name as the filename being zipped
- Deletes the file that was first created, after it has been added to the zip file created
- Allows continuation of workflow after the zipped file has been created
If any mandatory fields are left blank, the macro will show an error and will not execute and cause your workflow to fail e.g.
Dependencies
- ToFile macro
- Alteryx predictive tools installed on the machine you are running the workflow on (ensure to install the correct version that matches your version of Alteryx)
- Rtools installed on the machine you are running the workflow on (Download URL shown within attached .txt file on this post)
- After Rtools has been installed, 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 (C:\program files may not be the location that Alteryx was installed to on your machine and your version of Alteryx predictive tools may have installed a different version of R-3.1.2, so use these example directory locations as a guide)
toFileToSFTP
IMPORTANT:
Some users may get an error code status 127 returned from WINSCP (turn on "show macro messages" to see this).
- If this occurs, the first thing I would recommend to do would be to use the WINSCP.exe GUI and test to make sure you can actually connect to the SFTP server you are trying to send the data to
- If that works, then the problem may be because the SFTP server you are trying to send to, only supports SCP and not SFTP (when using WINSCP GUI, while you have SFTP protocol selected, you also have SCP fallback enabled. In scripting, the fallback is not enabled, so WinSCP.COM fails). Solution for this would be to change the formula used in the action tool (#7) that updates the R tool in this macro, so sftp:// is replaced with scp://, or another option would be to fix your server to allow SFTP (source: https://winscp.net/forum/viewtopic.php?t=14318)
- You may be using a version of WINSCP.COM that is too old? In the txt file attached to this post, I have included a download URL to the version that I am using which definitely works with these macros (v5.7.5)
Functionality
- All functionality that the ToFile macro has (highlighted above) - in order to SFTP a file, it must be created and stored somewhere first
- Allows user to enter the location of their WINSCP.com file (used for SFTP transfer) - note: I used a 'Text box' tool for this instead of a 'Folder browse' tool, as the 'Folder browse' tool does not allow you to specify a default value. If you don't want to have to enter in the location of your WINSCP.COM file each time you configure this macro tool's options, edit the 'Text box' (#100) tool inside the macro so that a default value is specified
- Delivers the file to a user specified SFTP location where the user can nominate:
- Server IP address or DNS name (mandatory and may need to be done by ip address and not DNS name depending on your environment)
- Username (mandatory)
- Password (mandatory)
- SFTP Directory (can be left blank if user wants to copy to root directory)
- SFTP HostKey (mandatory and can be discovered in 2 ways)
- Connect to the SFTP server for the first time using the WINSCP GUI
- Enable "show All macro messages" in the Runtime tab in Alteryx, enter a bogus HostKey value and execute the workflow. In the Results messages, the HostKey will then be visible
- SFTP port to use (if left blank, the macro will use the default SFTP port of 22)
- Allows the user to control whether to delete the local file, after it has been delivered to the specified SFTP server (Note that this tickbox is by default enabled, due to in most circumstances, the user would not want to keep the local file after it has been delivered to the SFTP server)
If any mandatory fields are left blank, the macro will show an error and will not execute and cause your workflow to fail e.g.
Please note, the first time you configure the macro in your workflow, it will show the following error message:
Ignore this error message. Once you have run the workflow and save it after a successful run, the error message will no longer appear
It is highly recommended to always enable "show all macro messages" in the Runtime tab in Alteryx when using this macro, for troubleshooting purposes, especially when using it as part of a workflow that has been deployed to Alteryx scheduler
Dependencies
- ToFile macro
- Alteryx predictive tools installed on the machine you are running the workflow on (ensure to install the correct version that matches your version of Alteryx)
- WINSCP.com command line utility (I use version 5.7.5 and Download URL shown within attached .txt file on this post)
toFileToZipToSFTP
IMPORTANT:
Some users may get an error code status 127 returned from WINSCP (turn on "show macro messages" to see this).
- If this occurs, the first thing I would recommend to do would be to use the WINSCP.exe GUI and test to make sure you can actually connect to the SFTP server you are trying to send the data to
- If that works, then the problem may be because the SFTP server you are trying to send to, only supports SCP and not SFTP (when using WINSCP GUI, while you have SFTP protocol selected, you also have SCP fallback enabled. In scripting, the fallback is not enabled, so WinSCP.COM fails). Solution for this would be to change the formula used in the action tool (#7) that updates the R tool in this macro, so sftp:// is replaced with scp://, or another option would be to fix your server to allow SFTP (source: https://winscp.net/forum/viewtopic.php?t=14318)
- You may be using a version of WINSCP.COM that is too old? In the txt file attached to this post, I have included a download URL to the version that I am using which definitely works with these macros (v5.7.5)
Functionality
- All functionality that the ToFileToZip macro has (highlighted above) - in order to SFTP a zipped file, it must be created and stored somewhere first
- Allows user to enter the location of their WINSCP.com file (used for SFTP transfer) - note: I used a 'Text box' tool for this instead of a 'Folder browse' tool, as the 'Folder browse' tool does not allow you to specify a default value. If you don't want to have to enter in the location of your WINSCP.COM file each time you configure this macro tool's options, edit the 'Text box' (#128) tool inside the macro so that a default value is specified
- Delivers the zipped file to a user specified SFTP location where the user can nominate:
- Server IP address or DNS name (mandatory and may need to be done by ip address and not DNS name depending on your environment)
- Username (mandatory)
- Password (mandatory)
- SFTP Directory (can be left blank if user wants to copy to root directory)
- SFTP HostKey (mandatory and can be discovered in 2 ways)
- Connect to the SFTP server for the first time using the WINSCP GUI
- Enable "show All macro messages" in the Runtime tab in Alteryx, enter a bogus HostKey value and execute the workflow. In the Results messages, the HostKey will then be visible
- SFTP port to use (if left blank, the macro will use the default SFTP port of 22)
- Allows the user to control whether to delete the local zip file, after it has been delivered to the specified SFTP server
- Note that this tickbox is by default enabled, due to in most circumstances, the user would not want to keep the local file after it has been delivered to the SFTP server
If any mandatory fields are left blank, the macro will show an error and will not execute and cause your workflow to fail e.g.
Please note, the first time you configure the macro in your workflow, it will show the following error message:
Ignore this error message. Once you run the workflow and save it after a successful run, the error message will no longer appear
It is highly recommended to always enable "show all macro messages" in the Runtime tab in Alteryx when using this macro, for troubleshooting purposes, especially when using it as part of a workflow that has been deployed to Alteryx scheduler
Dependencies
- ToFile macro
- ToFileToZip macro
- Alteryx predictive tools installed on the machine you are running the workflow on (ensure to install the correct version that matches your version of Alteryx)
- Rtools installed on the machine you are running the workflow on (Download URL shown within attached .txt file on this post)
- After Rtools has been installed, 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 (C:\program files may not be the location that Alteryx was installed to on your machine and your version of Alteryx predictive tools may have installed a different version of R-3.1.2, so use these example directory locations as a guide)
- WINSCP.com command line utility (I use version 5.7.5 and the Download URL shown within attached .txt file on this post)
Solved! Go to Solution.
I noticed that if you are dealing with a large amount of data, the ToFileToZip, ToFileToSFTP and ToFileToZipToSFTP macros run really slow and will also return the following error most times:
The R.exe exit code (4294967295) indicated an error.
I got this when inputting 6GB worth of data.
Thanks to @KaneG's response on the following post: http://community.alteryx.com/t5/Advanced-Analytics/Boosted-Model-Error/td-p/5509 which pointed me in the right direction to realise it is memory overload related issue (even though I am using R 64 bit and the machine I am using has 64GB of ram?)
The reason why this happens with the macros posted, is because the ToFile macro pushes out the whole data set to its macro output, which then is read into the R tool as a data frame inside the ToFileToZip, ToFileToSFTP and ToFileToZipToSFTP macros.
I experimented with using the as Data Frame:Chunked: option highlighted on this page: https://help.alteryx.com/9.5/R.htm , but it did not help for me.
If you get this issue, please use the attached replacement for the ToFile macro, which instead of outputting all the incoming data, it outputs the FileCreateCompletionTime (1 row of data)
If anyone has experienced this and has succesfully managed to read in a large amount of data as a data frame to the R tool, please could you share how you did it?
Below shows the changes made for the alternative version of ToFile.yxmc attached to this post.
If you are pushing in smaller amounts of data to these macros, you should not have to worry about this.
ryan
