Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Knowledge Base

Definitive answers from Designer Desktop 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
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?

pjandliz
7 - Meteor

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
5 - Atom

@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
5 - Atom

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
7 - Meteor

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