Alteryx Designer Discussions

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

A quick tip for reading in password protected excel files

PeterA
Alteryx
Alteryx

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)

 

 

 

35 REPLIES 35
HwasuK
Alteryx
Alteryx

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
Alteryx
Alteryx

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
7 - Meteor

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
Alteryx
Alteryx

Hi @

 

 

 

JamelTalbi
7 - Meteor

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

 

Appreciate your continued help.

Frieda_Leung
5 - Atom

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

infiniteLoop
6 - Meteoroid

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

Hi Peter,

 

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

Is this correct to password protect the excel file. 

 

priyankaDeshmukh_0-1592379806345.png

 

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

 

Thanks

 

joanq
7 - Meteor

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
7 - Meteor

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

Labels