Password protected excel spread sheets

#1

All, is there a work around to read data from excel files that are password protected?

have alot of files… each having same password to open… then a prompt that needs to be clicked okay.

0 Likes

#2

Hi @jinbaba -

Unfortunately, right now this isn’t supported with KNIME nodes.

You might be able to use a library like openpxyl to do it within a Python or R Snippet, but in my limited searching online there seems to be some dispute about that. (Maybe @mlauber71 knows more?)

Another workaround might be to use some kind of external Excel password remover for your group of files, then importing them into KNIME. There are a few of those tools out there.

1 Like

#3

There are several R packages that claim to be able to open Excel files with passwords. Unfortunately I was not able yet to set up a working example on my Mac due to problems with rJava package (could be that it works better on a Windows machine).

openpyxl could be another possbility, again I would have to check to come up with a working example. I fear some trial and error will be involved - and it will depend on the configuration of your system.

0 Likes

#4

thanks guys! will keep trying and revert back if i can find a solution

0 Likes

#5

Try XLConnect package in R. I know this works from experience as have used this program before.

function is: -
loadWorkbook(filename, create = FALSE, password = NULL)

(Of course, post back if it doesn’t!)

2 Likes

#6

@jinbaba I created an example the uses the Python library msoffcrypto-tool to decrypt an excel file. As I said I had trouble with the R libraries. Some packages would only offer you the setting and removal of change protection but not decryption like this one does

5 Likes

Access Password Protected Excel Files
#7

thanks let me try to follow your example… looks a bit too complicated for me… but i will give it a go!

1 Like

#8

The workflow is built to provide a fully functioning example. In the end it is very simple:

  • give a path to an excel file
  • if you want provide a different excel file name if you do not want to overwrite it
  • load and decrypt the file in python

You could also wrap a loop around it to deal with multiple excel files at the same time.

0 Likes