How do you read a password protected excel file into r?
I have tried excel.link but its not available for R version 3.2.3 (My version)
I also tried RDCOMClient but it is also not available for R version 3.2.3
How do you read a password protected excel file into r?
I have tried excel.link but its not available for R version 3.2.3 (My version)
I also tried RDCOMClient but it is also not available for R version 3.2.3
I just used xl.read.file
from the excel.link
package.
https://rdrr.io/cran/excel.link/man/xl.read.file.html
It was very straightforward.
Using the same test file from the previous answer (https://github.com/miraisolutions/xlconnect/files/794219/TestWorkbook.xlsx).
install.packages("excel.link")
library("excel.link")
dat <- xl.read.file("TestWorkbook.xlsx", password = "pass", write.res.password="pass")
dat
(The file I needed only had one password, unlike the test file, so I didn't need the last argument for my use.)
XLConnect (0.2-13) can now read password protected excel files
Install latest version of XLConnect and XLConnectJars (0.2-13)
install.packages("XLConnect")
install.packages("XLConnectJars")
Install Unlimited Strength Java(TM) Cryptography Extension Policy File (necessary on OS X and Windows - not needed on Ubuntu linux with OpenJDK 1.8)
http://www.oracle.com/technetwork/java/javase/downloads/jce8-download-2133166.html
How to install unlimited strength JCE for Java 8 in OS X?
library(XLConnect)
Using test file:
https://github.com/miraisolutions/xlconnect/files/794219/TestWorkbook.xlsx
wb <- loadWorkbook("TestWorkbook.xlsx", password="pass")
test <- readWorksheet(wb, "sheet1")
> data
id value1 value2
1 1 1 5
2 2 2 4
3 3 3 3
4 4 4 2
5 5 5 1
To integrate the previous answers: I was looking to do the same and found that excel.link
package has problems with the latest R version as of today and makes R crash.
XLConnect
might work but it has complications from the need of extra installations that might be otherwise unnecessary for you.
I found that xlsx::read.xlsx()
has a password
argument and it worked just fine in my case.
For me this was the most practical solution.