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.
2 Likes
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
6 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.
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.
Hi all,
with the latest release of version 4.4, the Excel Reader supports reading password protected files. Thanks for all the feedback!
Best,
Simon
6 Likes