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

Hi, can you please help, what need to be put in the formula?

Also..do I understand correctly, that the R code need to be write in the R configuration? That's all?

Andrzej
8 - Asteroid

Hi, 

 

I got an error when I try to install package. In Rstudio I can do it. Dou you have idea why? 

JFurda
8 - Asteroid

I am getting the below error after installing the excel.link library in the proper folder. 

Error: R (19): Error: package or namespace load failed for 'excel.link' in rbind(info, getNamespaceInfo(env, "S3methods")):

 

Is that an issue with my R install, the password protected file that I'm attempting to open, or something else? 

Sri9
8 - Asteroid

if i want to read from multiple sheet names how to specify the sheet names? 

Maiia
6 - Meteoroid

Hi, I am facing the same problem, where you able to resolve Invalid Class String Error?

Joma
7 - Meteor

Hi all,

 

thank you for the quick tip on reading password protected excel files. That helps a lot.

 

But as i tried to play around with the input file, an error occured.

The original excel file only includes 1 value which is in the first column/row. But what if the first row only includes are header and the important data is below. I changed my data as shown in the screenshot. But my final output will still be the same: It shows only one value, which is "Header".

The idea would be just to read all values in the file and then i can clean the file with Alteryx.

 

Joma_0-1605089836187.png

 

I guess i have to adapt somehow the Rcode in following row?

 

df <- read.Alteryx("#1", mode="data.frame")

 

Can somebody help me here?

 

Thanks in advance.

 

Best 

Joma

 

seven
12 - Quasar

For anyone having difficulties installing the excel.link R package in Alteryx, please follow these steps:

 

  1. Open the Start Menu
  2. Type "Alteryx"
  3. Right-click Alteryx and select "Run as administrator"
  4. seven_0-1606231148135.png
  5. Use an R tool located in the Developer palette
  6. Paste the code and run
install.packages('excel.link',repos='http://cran.us.r-project.org')

 

MarieB
Alteryx Alumni (Retired)

Does anyone know if there is a similar package that would work with r version 3.4.4 as found in Alteryx 2018.4? Thanks

pescobar11
5 - Atom

I am in the same boat here as well.  Anyone have an idea?

DougP
6 - Meteoroid

This is super helpful. Thank you!

 

I am encountering a strange issue using this package where the workflow runs successfully locally and outputs a password-protected file as desired; however, when I run the same workflow from a Gallery the R Tool throws an error: "Error: Invalid class string".

 

I have attached a sample workflow.

 

My troubleshooting so far shows that:

1. When run in the Gallery the excel.link package is installed and seems to be loaded successfully.

2. The error seems to be taken on the last line of the R script (the xl.save.file() command)

 

Any ideas why the workflow would perform differently in a Gallery versus locally?

 

 

My R script looks like this:

if("excel.link" %in% rownames(installed.packages()) == FALSE)
	{install.packages("excel.link", repos = "https://mirrors.nics.utk.edu/cran/")}

library("excel.link")

df <- read.Alteryx("#1", mode="data.frame")
today <- Sys.Date()
format(today, format="%Y-%m-%d")

filename <- as.character(paste("Test Report_", today, ".xlsx", sep = ""))

xl.save.file(df, filename, row.names = FALSE, col.names = TRUE, password = "TestPass")

 

 

 

Labels