import of excel sheet with password

[Kyra]

Hello there!

I have been trying the whole day to import a dataset from excel to R, which is password-protected. I have tried so many things, but always end up getting some kind of error-message. I really need help, thanks a lot!!

this was my latest attempt (I put in the real password of course):

install.packages("readxl")
install.packages("gdata")
install.packages("openxlsx")
library(readxl)
library(gdata)
library (openxlsx)

dateipfad <- "C:/Users/Startklar/Desktop/Kopie.xlsx"
passwort <- "PASSWORD"

daten <- read_xlsx(dateipfad, password = passwort)

meineDaten <- daten
head(meineDaten)
View (meineDaten)

daten <- read_xlsx(dateipfad, password = passwort)
*Fehler in read_xlsx(dateipfad, password = passwort) : *

• unbenutztes Argument (password = passwort)*

[FJCC]

I don't see any way to read a password protected Excel file with either readxl or openxlsx. The excel.link package has the function xl.read.file() that can do it. The package requires that Excel be available. I have never used the excel.link package.

[AlexisW]

It also looks like {xlsx} might have the option (also didn't test it myself).

[Rolando]

Would it not be possible to open the excel file in excel and export the contents to a non-password protected excel file or just a simple csv file? and then import the new file.

Just and idea which might help.

[jimbrig2011]

If the issue is that you cannot access Excel or the Native Windows COM interface to create an excel instance in the shell (i.e. on linux server) then you could resolve the issue via some Python and (if necessary) wrap the python with reticulate.

The best python library for this is msoffcrypto-tool which does not require Excel installation and is fully cross-platform with Python. It also supports all password-protected (encrypted) Microsoft Office documents, including the older XLS binary file format.

pip install msoffcrypto-tool
msoffcrypto-tool "MyProtectedFile.xlsx" "MyUnprotectedFile_Decrypted.xlsx" -p "mypassword"

or via python api:

import io

import msoffcrypto
import openpyxl


decrypted_workbook = io.BytesIO()

with open('Myfile.xlsx', 'rb') as file:
    office_file = msoffcrypto.OfficeFile(file)
    office_file.load_key(password='caa team')
    office_file.decrypt(decrypted_workbook)

# `filename` can also be a file-like object.
workbook = openpyxl.load_workbook(filename=decrypted_workbook)

PowerShell and C#/.NET can also do the same without any dependency on the Excel COM Object Library.

You can also simply zip and unzip the excel file to access its internal XML and search for the password, delete it, re-zip, and it will be gone.

[Kyra]

Thank you all so much for your suggestions! I will try out everything