This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
on 03-03-202102:22 PM- edited
a month ago
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.
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
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:
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:
Also, uncheck "Run script when refreshed(F5)" checkbox
Issue 2: The following error appears, after running the workflow in the Alteryx Designer:
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:
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