Read/Extract password-protected Excel file using R Code in Alteryx Designer

There are use cases where a user wants to read/extract a password-protected Microsoft Excel file, using R code in 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-protected 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.

• 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 is 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

• 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:
  
  

Common Issues

Issue 1:
The following error appears while executing the workflow on the Alteryx Designer:


 
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:

 

Cause(s):

A) This error is due to the incorrect password input in the Formula Tool

B) COM object is not enabled on the server

Resolution(s):

A) 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.

B) See "How to enable COM object on the server" for resolution

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

 

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 the Microsoft Excel Application is installed on the Alteryx Server machine, as the external package - "excel.link" requires the Microsoft Excel Application as a prerequisite, as per the package's documentation: