How To: Read/Extract password protected Excel file using R Code in Alteryx Designer

[vijayakarthikeyan]

There are use-cases where a user wants to read/extract a password protected Microsoft Excel file, using R code into Alteryx Designer. This article helps in extracting the password protected Excel files into Alteryx Designer and the common issues to resolve during this process.

Please note - "excel.link" is an external package outside Alteryx and we do not support this package. This article provides an overview of extracting password protecting files to Alteryx Designer using R code as a template. The user may need to enhance the R code to meet his/her requirements, meeting the prerequisites.
 

Prerequisites

• Alteryx Designer
• Alteryx Predictive tools (which in turn makes the R tool available. If you do not have Predictive tools installed, you would see a Question mark, in place of the R tool within the workflow)
• Basic knowledge of R code
• Microsoft Excel Application installed on this machine
• A Password Protected Microsoft Excel Application (This article details the steps to create a password-protected Excel application)
• Network connectivity to download the R package

Procedure
 

• Download the attached Alteryx yxzp file that helps in providing the R Code to read the password-protected Excel file.
• Open the Alteryx Designer and click on File menu --> Open Workflow, to open the downloaded yxzp file on the Alteryx Designer
• Please note - The Designer version used is 2020.4 and the corresponding R version used is - 3.6.3. Make sure that you have the matching version of R installed for your Alteryx Designer(Otherwise, you will end up resulting on this error if there is a mismatch)
• Select the folder path of the password-protected file on the Director Tool(Make sure that there is one excel file under this folder, as this is a Directory Tool)
• Run the workflow and the output is displayed, reading the data from the password-protected Excel file:

image.png

Common Issues

Issue 1:
The following error appears while executing the workflow on the Alteryx Designer:
image.png
Cause: This error appears when there is no package called "excel.link" installed on this machine, where the workflow runs.
Resolution: Make sure that the excel.link package installed on the machine, where this workflow is run. To install this using the R tool, please add the following line at the beginning of the R tool text-editor:

# The set of possible repositories to use
repos <- c("http://cran.revolutionanalytics.com", "http://cran.rstudio.com")
# Select a particular repository
repo <- sample(repos, 2)
install.packages(c("excel.link"),repos=repo,dependencies = TRUE)

Also, uncheck "Run script when refreshed(F5)" checkbox



Issue 2:
The following error appears, after running the workflow in the Alteryx Designer:

image.png

Cause: This error is due to the incorrect password inputted in the Formula Tool
Resolution: Make sure that the correct password is provided in the Formula Tool, verifying the case-sensitivity, and provide the exact password used for opening the Microsoft Excel Application.

Issue 3:
When this workflow is published to the gallery, the following error is thrown while running on the Gallery:

image.png

Cause: This error is thrown when there is no Microsoft Excel Application installed on the Alteryx Server machine.
Resolution: To fix this error, make sure that Microsoft Excel Application is installed on the Alteryx Server machine, as the external package - "excel.link" requires Microsoft Excel Application as a pre-requisite, as per the package's documentation :
image.png

[SeanAdams]

This is very helpful, and a common need - thank you for this writeup!

[meenu19951]

@vijayakarthikeyan where can I get the packaged workflow for this?

[pjandliz]

I'm able to open a protected XL file using this process - thank you.
It seems to return the data on the 1st sheet in the file; how would I go about specifying which sheet (either by sequence or 'name') I would like the data returned from pls?

I've found some info by using the 'readxl' package but I think this is for unprotected XL files?
(I'm a beginner using R in Alteryx - and R in general - so apologise upfront if this is a dumb question).

Thx

[rachelward617]

@pjandliz I just had this same issue today - see https://community.alteryx.com/t5/Alteryx-Designer-Discussions/R-Code-based-excel-file-reading/td-p/6... 

1) create another formula called "SheetName", and set the value equal to your sheet name, for example "Sheet1"

2) add a line to your code that says: sheet_name <- as.character(df[1,"SheetName"])

3) modify the line that reads in the file as follows: excel_data <- xl.read.file(filename, password = excel_pwd, write.res.password=excel_pwd, xl.sheet=sheet_name)

[BriceChapman]

I'm getting the error "Error in unzip(zipname, exdir = dest) : cannot open file 'C:ProgramData/...' : Permission Denied". I'm guessing this is because on my work computer I do not have permission to unzip files in this location, is there a work around to this that anyone knows of? Maybe changing the location where the file is unzipped? Thank you.

[HardikJani]

Hi Vijay,

I tried running it exactly as you mentioned. I am getting a different error. It says 

Error in unzip

(zipname, exdir=dest):

Execution halted

The R.exe exit code (1) indicated an error.

Apologies for not being able to share the ss