Password protected excel spread sheets

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.

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

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.

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

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

@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

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

1 Like

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.

boss any idea of what this means? have tried many things but can’t this this to work

It looks like you need to add the msoffcrypto package to your Python environment. If you’re using Anaconda, you could use conda install from the command line to do this.

1 Like

It should work like this

pip install msoffcrypto-tool
1 Like

This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.