A quick tip for reading in password protected excel files

[PeterA]

There are times that you want to read in Microsoft Excel files that have been password protected without going through too many extra hoops.  Out-of-the-box the INPUT Tool will not do this - 2019.2 included.  But you are in luck there are numerous R & Python packages that can handle this request.  Here is one example leveraging the R Tool.

Workflow to read in a password protected Excel file.

Attached below is the workflow, but if you just want the R code then here you go.  (notice that library that needs to be installed)

# install.packages(c("excel.link"))
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)

[LukeM]

@PeterA do you know the package well?

Is there a way to do the opposite - i.e. write to a password protected excel?

Luke

[PeterA]

@LukeM 

Great Question.  Yes there is a simple way with the same package - just check out the online doc for the library - see https://www.rdocumentation.org/packages/excel.link/versions/0.9.8-1/topics/xl.read.file for more information.

xl.save.file(output, filename, xl.sheet = NULL, password = "alteryx", write.res.password = "alteryx")

[LukeM]

Legend @PeterA, thanks! I've had a few questions from users around this so is a really nice and useful solution.

[msmt85]

Seems like an easy and Nice to have solution. @peter Does it work for xlsb files as Well? Thanks.

[PeterA]

Good question @msmt85. Just gave it a quick test... and I can report that YES... it can handle XLSB files without any problem.

[msheladia]

@PeterA, how can I use this to open and output multiple files at once? Thanks!

[JamelTalbi]

Can somebody indicate where I can download the "excel.link" library? Thanks.

I'm getting the following error message:

R (1) Error in library("excel.link") : there is no package called 'excel.link'

[cruparelia]

I tried to run the workflow 

 but getting error " R (3) Error in top_left_corner[["CurrentRegion"]] :"

Kindly assist to fix the issue.

[l10tovar]

@cruparelia did you ever find a solution to this issue? 

I am having the same error you are having in your screenshot.