How do you read a password protected excel file into r?

Question

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

Weswesa

Have you tried these answers - stackoverflow.com/a/13997138 – Orlov

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 – Weswesa

what version of excel? xlsx? – Usually

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 – Eijkman

There is an open issue in the github repository for XLConnect: github.com/miraisolutions/xlconnect/issues/61 – Eijkman

for those of us who don't want a Java dependency... any packages that can read password-protected xlsx files without extra Java installations? – Aiguille

@BrianD Hi Brian, I just read your comment. Don't know if you saw it already, but check my reply to the question. – Thithia

Answer 1

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

Jounce

The xl.read.file function causes R to crash – Pavilion

Note that excel.link only works in Windows and MacOS (not Linux) – Subvene

Doesn't work on MacOS either. Windows only. "❗ Microsoft Windows and Microsoft Excel are required for this package." – Bousquet

Hi @MarkDavies, the above code works well. Your R is crashing probably because you open the same pass-worded excel file. When I opened the file while at the same time wanting to import, R crashed. But when I close the excel file and run the above code again, I was able to import the pass-worded excel file into R. – Beetroot

Answer 2

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

Eijkman

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 " – Subvene

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

Thithia

xl.read.file crashes R (within RStudio) for me as well. (2021-05-10) – Jounce

I tried xlsx::read.xlsx() but got the errorError in .jcall("RJavaTools", "Ljava/lang/Object;", "newInstance", class, : java.lang.OutOfMemoryError: GC overhead limit exceeded – Pavilion

Answer 4

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

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

Breakaway

Good advice to keep passwords out of code. I like the keyring package for storing passwords. – Jounce

Answer 5

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.

Cense

Answer 6

Tried below, they seem to work without any complications for me.!

library(xlsx)

read.xlsx2("path/to/your/password_protected_file.xlsx", sheetIndex = 1, password = "password")

OR

library(xlsx) library("rstudioapi") read.xlsx2("path/to/your/password_protected_file.xlsx", sheetIndex = 1, password = password = rstudioapi::askForPassword("Input password for excel sheet."))

Impersonate