You might come across password protected spreadsheets in the workplace as they help to improve security within a company, however Alteryx will not let you just drag in and read these files. (Note: you will still need the password to access the spreadsheet in Alteryx). This method will require using the R tool, although all of the script for this is available online so no R knowledge is needed.
To run the R package you will need to first download it using the app attached to this blog:
AlteryxProducts
Run this app and paste the following into the install list, this should install the package on your local machine (note that anyone else who wants to run this workflow will also need this installed):
The next steps will involve Alteryx, as all of the R setup should be complete. As Alteryx does not let us bring in the spreadsheet through an input tool, we will need to use the directory tool to point the script towards the right location.
Point the directory to where the spreadsheet is located and use the specification to describe the file (*.xlsx for spreadsheets, and replace the * with the name of the spreadsheet). The next step is to bring in a formula tool with the password specified as below:
name the formula output column excel_password, and wrap the password in quotations for the R tool. Drag in the R tool from the developer pane and connect it to this formula tool.
Copy the following code into the configuration of the R tool:
library("excel.link")
df <- read.Alteryx("#1", mode="data.frame")
filename <- as.character(df[1,"FullPath"])
excel_pwd <- as.character(df[1,"excel_password"])
excel_data <- xl.read.file(filename, password = excel_pwd, write.res.password=excel_pwd)
write.Alteryx(excel_data, 1)
The R tool will output the data from the first connector, so drag in a browse tool to this to view the contents of the spreadsheet.
