Now that it's live, don't forget to accept your certification badge on Credly today! Learn more here.

Alteryx Designer Knowledge Base

Definitive answers from Designer experts.

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

vijayakarthikeyan
Created

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.pngimage.png

 

Common Issues


Issue 1:
The following error appears while executing the workflow on the Alteryx Designer:
image.pngimage.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

image.png

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

image.pngimage.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.pngimage.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.pngimage.png

 

 


 

    
Attachments
100% rated this as helpful (1/1)
Comments
SeanAdams
17 - Castor
17 - Castor

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

meenu19951
5 - Atom

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