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)

[JFurda]

I'm getting an error stating that "Warning: R (1): package 'excel.link' is not available (for R version 3.5.3)"

Does this mean that my R studio is too old of a version? My company requires that applications go through an evaluation process prior to being released into the ecosystem, so I want to make sure I'm pushing in the proper direction. 

Thanks! 

[DougP]

I was getting that warning too @JFurda.

If you specify a mirror near you in your install.packages() command it should work

install.packages("excel.link", repos="http://archive.linux.duke.edu/cran/")

LIst of mirrors: https://cran.r-project.org/mirrors.html

[JFurda]

I have tried several mirrors. I continue to get an error stating that: 

Warning: R (1): package 'excel.link' is not available (for R version 3.5.3)

Is it that I have an older version of R that is incompatible or is there a specific mirror that has the proper package? 

[DougP]

I too am running R version 3.5.3. I don't see the warning when I use this mirror: repos="http://archive.linux.duke.edu/cran/"

I'm using Alteryx Designer 2019.4 if that is helpful.

[JFurda]

It might be that my company has those pages blocked because I'm getting the same error for each mirror I've tried. It is strange, though, since I can access them from my IE and Chrome browsers. 

Info: R (1): Warning: unable to access index for repository https://mirrors.nics.utk.edu/cran/src/contrib:
Info: R (1): cannot open URL 'https://mirrors.nics.utk.edu/cran/src/contrib/PACKAGES'
Info: R (1): Warning: unable to access index for repository https://mirrors.nics.utk.edu/cran/bin/windows/contrib/3.5:
Info: R (1): cannot open URL 'https://mirrors.nics.utk.edu/cran/bin/windows/contrib/3.5/PACKAGES'
Warning: R (1): package 'excel.link' is not available (for R version 3.5.3)

Can I download the package and manually unzip it into the proper directory or is it a requirement to be "installed" by Alteryx? 

[PaulNo]

Hi @JFurda,

You could install the package from a local file too.

The package file could be downloaded from https://cran.r-project.org/bin/windows/contrib/3.5/excel.link_0.9.8-1.zip 

If you download it to, for example, C:\temp, then you could install it using the following R command (to be entered into an R tool if needed):

 install.packages("C:/temp/excel.link_0.9.8-1.zip",repos=NULL)

Kind regards,

PaulN 

[ikhellal]

thanks for this answer. i do have a problem with protected sheet also.

after unlock the excel file i have protected sheet with password how can i solve this problem???

helpp please.