Encrypted Excel files
![Gwosa-Sweden](https://us.v-cdn.net/6038231/uploads/Dataiku/nAvatar22.png)
Gwosa-Sweden
Registered Posts: 1 ✭
Does anyone has the experience to load an excrypted Excel file?
Answers
-
Turribeach Dataiku DSS Core Designer, Neuron, Dataiku DSS Adv Designer, Registered, Neuron 2023 Posts: 1,876 Neuron
The msoffcrypto-tool Python package may be one approach but it doesn't support the latest Excel formats. What format is your Excel file on?
-
Could you provide python code to
1. read from non-local source folders of excel files
2. test if they are password protected.
3. if yes, perform password decrypt using msoffcrypto-tool
4. save password unprotected excel back to source folder
Thanks.
-
I tried the below. however, the output excel file could not be read by the Create Dataset , error message
- Used /NEW_SPREADSHEET.xlsx (244.18 KB) to parse data
- Failed to detect file format. Please manually fix
Even if i manually selected excel as format, it still cannot load it into Preview.
==code==
import io import shutil import dataiku import msoffcrypto, openpyxl # Read recipe inputs source = dataiku.Folder("input") source_info = source.get_info() paths = source.list_paths_in_partition() # Write recipe outputs target = dataiku.Folder("output") target_info = target.get_info() for path in paths: decrypted = io.BytesIO() with source.get_download_stream(path) as input_file: with io.BytesIO() as seekable: shutil.copyfileobj(input_file, seekable) file = msoffcrypto.OfficeFile(seekable) file.load_key(password="xxxxx") # Use password file.decrypt(decrypted) xlfile = openpyxl.load_workbook(decrypted) xlfile.save(decrypted) decrypted.seek(0) target.upload_stream("NEW_SPREADSHEET.xlsx", decrypted)