26

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

telfreth
  • 261
  • 1
  • 3
  • 3
  • Have you tried these answers - http://stackoverflow.com/a/13997138 – Zero Mar 07 '16 at 20:00
  • Yes I have.. the RDCOMClient can't be installed with my version of R so I'm looking for another library or a work round – telfreth Mar 07 '16 at 20:12
  • what version of excel? xlsx? – C8H10N4O2 Mar 07 '16 at 21:34
  • The most recent version of package XLConnect (0.2-12) adds this functionality. On my windows 10 machine, running R 3.3.2, this fails with the error: Error: NoClassDefFoundError (Java): com/microsoft/schemas/office/x2006/encryption/EncryptionDocument$Factory – user25494 Apr 04 '17 at 16:11
  • There is an open issue in the github repository for XLConnect: https://github.com/miraisolutions/xlconnect/issues/61 – user25494 Apr 09 '17 at 13:32
  • 1
    for those of us who don't want a Java dependency... any packages that can read password-protected xlsx files without extra Java installations? – Brian D Feb 26 '20 at 17:59

3 Answers3

13

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

Dima Kozhevin
  • 3,091
  • 9
  • 33
  • 48
Chris Andrews
  • 131
  • 1
  • 4
11

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
Community
  • 1
  • 1
user25494
  • 1,153
  • 9
  • 25
3

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.

Matteo
  • 73
  • 1
  • 6