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 Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

A quick tip for reading in password protected excel files

PeterA
Alteryx Alumni (Retired)

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.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)

 

 

 

36 REPLIES 36
JFurda
8 - Asteroid

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
6 - Meteoroid

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
8 - Asteroid

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
6 - Meteoroid

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
8 - Asteroid

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
10 - Fireball

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

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.

Labels