Submit your innovative use case or inspiring success story to the 2023 Dataiku Frontrunner Awards! LET'S GO

Encrypted Excel files

Level 1
Encrypted Excel files

Does anyone has the experience to load an excrypted Excel file?

0 Kudos
3 Replies
Level 6

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?

0 Kudos
Level 1

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



0 Kudos
Level 1

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.





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
            xlfile = openpyxl.load_workbook(decrypted)
            target.upload_stream("NEW_SPREADSHEET.xlsx", decrypted)



0 Kudos