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)

[joanq]

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]

Hi, 

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

[JFurda]

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]

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

[Maiia]

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

[Joma]

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.

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]

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

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]

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

[DougP]

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