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)

[HwasuK]

Hi JamelTalbi,

You should remove # in front of install.packages. That will install the package. Once you install it, you can either remove that line or put # back, so you don't install it again each time you run the code.

Hope that works for you.

[HwasuK]

Hi cruparelia,

Do you have protected sheets in your excel file? If you do, you will need to unlock the sheets first. If you want to go down that path, check out this blog.

[JamelTalbi]

Thanks, Hwas, but now I'm receiving the below errors. Any thoughts?

R (9) R version 3.5.3 (2019-03-11) - x86_64
R (9) Loading required package: sysfonts
R (9) Loading required package: showtextdb
R (9) rgeos version: 0.4-2, (SVN revision 581)
R (9) GEOS runtime version: 3.6.1-CAPI-1.10.1
R (9) Linking to sp version: 1.3-1
R (9) Polygon checking: TRUE
R (9) Attaching package: 'AlteryxPredictive'
R (9) The following object is masked from 'package:AlteryxRDataX':
R (9) XMSG
R (9) Installing package into 'C:/Users/talbjam/AppData/Local/Alteryx/R-3.5.3/library'
R (9) (as 'lib' is unspecified)
R (9) Error in contrib.url(repos, "source") :
R (9) trying to use CRAN without setting a mirror
R (9) Calls: install.packages -> contrib.url
R (9) Execution halted
R (9) The R.exe exit code (1) indicated an error.

[HwasuK]

Hi @JamelTalbi,

Are you running Designer as an administrator when you try to install the library?

You can go to Help|About, it should say "Running Elevated". Can you confirm?

[JamelTalbi]

I am not. How would I go about having an administrator run this? Not through Alteryx, I presume?!

Appreciate your continued help.

[Frieda_Leung]

@PeterA I don't have the R Tool, do you have a python equivalent example of this?

[infiniteLoop]

@PeterA 

It works fine when I am running using Designer but gives me error while running using gallery.

• Error: Invalid class string (Tool Id: 1)
• Execution halted (Tool Id: 1)
• The R.exe exit code (1) indicated an error. (Tool Id: 1)

[priyankaDeshmukh]

Hi Peter,

I have replaced the read statment with write and password embeded with it.

Is this correct to password protect the excel file. 

Could you please attach a workflow on password protecting output excel file

Thanks

[joanq]

hi, sorry i am not able to open workflow - i guess i have old version of alteryx.

Can you please share what should be in the formulas and R?

[joanq]

can you please share where i need to write R code as below? I mean which field?