Input and Output password protected XLSX files in Alteryx

[BenMoss]

My colleague (Phil Lowe) and I have been working on developing two macro's for use by the community. These macro's are designed to allow users to input data from a password protected XLSX file and output data to a password protected XLSX file.

The input macro can be found on the gallery here: https://gallery.alteryx.com/#!app/Input-Password-Protected-XLSX/5a9d23a7826fd31b1c2826ea

The output macro can be found on the gallery here: https://gallery.alteryx.com/#!app/Output-Password-Protected-XLSX/5a9d074e0462d716682045a9

These macros are very much in beta and we are already aware of a couple of known issues but the idea of posting these here is  to gather as much feedback as possible from the community so we can improve the macros and make them more reliable.

Current known issues

In order for the macro to run successfully the workflow with which it is within must be saved else the end user will get a write error.

The macro does not run on parallels. 

A single error ' Designer x64 The Designer x64 reported: Error running Event #1: The external program ".\deleteOutput.bat" returned an error code: 1: The system could not find the environment option that was entered. ¶ (203)' will always appear. This does not appear to affect the functionality of the tool.

How the macros work

The input macro uses a visual basic script to temporarily generate an unprotected excel file. The macro then uses the dynamic input tool to read this file into alteryx before a clean up script is applied on completion of the workflow to delete this unprotected file and other generated scripts.

The output macro writes the data stream as an unprotected file before a visual basic script creates a copy of this file and protects it with the password specified by the user within the macro configuration. A clean up process then takes place to remove the unprotected copy of the file and all other scripts.

[AndrewCrayford]

Hi @BenMoss 

When I run this macro, I am able to create a password protected xlsx file but I am getting the following error message?

Please advise on this,

[caitlinduff]

I get a similar error for the delete.bat, which I cannot resolve.

[caitlinduff]

I actually was able to solve by removing the "echo" portion of the formula tool. 

[ChrisTX]

In the original post, under Current known issues, Ben mentions

A single error ' Designer x64 The Designer x64 reported: Error running Event #1: The external program ".\deleteOutput.bat" returned an error code: 1: The system could not find the environment option that was entered. ¶ (203)' will always appear. This does not appear to affect the functionality of the tool.

[DevZig1103]

Thanks for this @BenMoss.  I was just at a client that used this macro and it worked well for them.  However, while helping them make it work for their needs, I was able to strip down some of the complexity in the macro and make it a bit more generic.  Specifically, it is now extension-agnostic (handles both XLSX and XLS), allows for multiple files to be read in at once (instead of providing folder location and file name, they simply feed in a full path), and utilizes temporary files so that the delete.bat file is not needed anymore.

Please note that this is a batch macro, so a directory tool of some sort must be used in conjunction with this macro.  If a user only wants to read in one file and doesn't want to use a directory tool, this can simply be converted by using a text box tool instead of the control parameter, where the user would provide the full path of the file (with extension).  Also, I created another very simple macro to read in the data (instead of using dynamic input tool) so it doesn't error if files don't have the same exact schema (it auto-configures by name).

[SariRam]

I am able to run this successfully in my local(did some minor tweaks to the macro). However, when I publish to the server-

1. I had to created a blank excel file with "vNoPassword" appended to override the validation. 

2. When I try executing the workflow, it goes into some kind of infinite loop that I will have to abort the execution  after 10+ mins

Also, I don't see the noPassword.xlsx file being generated in the folder. 

Any workaround?

Thanks,

Saritha

[DevZig1103]

I believe Microsoft Office (Excel at the very least) will need to be installed on the server, since this is using a VBS script to create the vNoPassword file.

[msheladia]

Thanks @DevZig1103, was there any other tool you used so the macro can now handle both XLSX and XLS?

[ChrisTX]

This related post might help:

A quick tip for reading in password protected Excel files

...numerous R & Python packages that can handle this request

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/A-quick-tip-for-reading-in-password-pr...

[Meenatchiv]

I removed the following lines from Delete File Formula in the macro and the error message did not appear after that.

ECHO "This script will now self-destruct. Please ignore the next error message"

Del %0