32

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
  • 321
  • 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
  • @BrianD Hi Brian, I just read your comment. Don't know if you saw it already, but check my reply to the question. – Matteo Sep 17 '21 at 13:13

5 Answers5

18

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,602
  • 9
  • 39
  • 52
Chris Andrews
  • 181
  • 1
  • 5
13

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

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
llrs
  • 3,308
  • 35
  • 68
user25494
  • 1,289
  • 14
  • 27
  • Nice solution that works in linux too. Thanks! Note that XLConnectJars is no longer needed and was removed from CRAN: "Archived on 2020-03-26 as no longer used by XLConnect " – llrs Mar 10 '23 at 10:25
6

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
  • 2,774
  • 1
  • 6
  • 22
  • xl.read.file crashes R (within RStudio) for me as well. (2021-05-10) – Chris Andrews May 10 '21 at 15:10
  • I tried `xlsx::read.xlsx()` but got the error`Error in .jcall("RJavaTools", "Ljava/lang/Object;", "newInstance", class, : java.lang.OutOfMemoryError: GC overhead limit exceeded` – Mark Davies Apr 28 '22 at 20:43
1

Building on Chris Andrew's answer, to avoid writing your password in the code, you can also add in the askForPassword function. This will give you a pop-up window asking you to input the password when you run the code.

install.packages("excel.link")
install.packages("rstudioapi")

library("excel.link")

pw_message <- "Input password for excel sheet."

dat <- xl.read.file("TestWorkbook.xlsx", password = rstudioapi::askForPassword(pw_message))

dat
R me matey
  • 617
  • 5
  • 9
1

This is an older thread now I know, but I thought I would add this here in case it's useful.

I have been reading an Excel file into a Shiny application and found xlsx::read.xlsx() to be far slower than excel.link::xl.read.file() for my purposes.

However, using excel.link, I noticed that closing the app would not close the associated Excel process while xlsx did.

Using excel.link, it was necessary to include system("TASKKILL /F /IM EXCEL.exe") in order to force Excel to quit after the file had been read.

r0bt
  • 383
  • 3
  • 12