CODEWITHSUNDEEP
rexcel
telfreth
telfreth

Reputation: 331

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

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

Upvotes: 33

Views: 42882

Answers (6)

Bennn
Bennn

Reputation: 881

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

Upvotes: 0

R me matey
R me matey

Reputation: 685

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

Upvotes: 2

user25494
user25494

Reputation: 1371

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

Upvotes: 17

r0bt
r0bt

Reputation: 591

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.

Upvotes: 1

Matteo
Matteo

Reputation: 2926

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.

Upvotes: 8

Chris Andrews
Chris Andrews

Reputation: 303

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

Upvotes: 20

Related Questions