How to read password protected excel file in R ?

In this article, we are going to see how to read password-protected Excel files in R programming language.

File in use:  file

Method 1: Using excel.link Package.

Here we will use excel.link package to read the file with a password.

Installation:

install.packages(“excel.link”)

xl.read.file() function is used to read Excel files in R programming.

Syntax: 

xl.read.file( “file_name”, password = “pass”)

Example 1: 

R

# import lib
library("excel.link")

# read file with pass
df <- xl.read.file("data.xlsx", password = "gfg@123")

# display df
head(df)

Output:

The same module can be used to first unlock the file and then copy its contents onto another so that it can be accessed again without any password. Here we save the file with a password NULL value and save it to another file.

Syntax: 

xl.save.file( file.object, “New_file”, password = NULL , write.res.password = NULL)

Program:

R

# import lib
library("excel.link")

# read file with pass
df <- xl.read.file("data.xlsx", password = "gfg@123")

# save the df into new file
xl.save.file(df, "Output.xlsx", password = NULL,
             write.res.password = NULL)

# read file without any password
df1 <- xl.read.file("Output.xlsx")

head(df)

Output:

Method 2: Using XLConnect package.

Here we will use XLConnect package to read the password-protected file. This package provides comprehensive functionality to read, write and format Excel data.

loadWorkbook() function loads Microsoft Excel workbooks.

Syntax: 

loadWorkbook(filename , password )

readWorksheet() function reads data from worksheets.

Syntax: readWorksheet( object, sheet).

Parameters: 

• Object: The ‘>workbook to use
• Sheet: The name or index of the worksheet to read from

Program:

R

# import lib
library(XLConnect)

# load the file
workbook <- loadWorkbook("data.xlsx", password = "gfg@123")

# read the object
df <- readWorksheet(workbook, "sheet1")

# display df
head(df)

Output: