Load password protected Excel files into Pandas DataFrame

When trying to read an Excel file into a Pandas DataFrame gives you the following error, the issue might be that you are dealing with a password protected Excel file.

pd.read_excel(PATH)
[...]
XLRDError: Can't find workbook in OLE2 compound document

There seems to be no way of reading password protected files with xlrd.

xlwings for the rescue

Fortunately, there is xlwings, which lets you interact with the Excel application itself (via pywin32 or appscript).

The following code will open your Excel file (if not open already, it will launch the Excel app, which then asks for your password) and turn a range selection of a sheet into a Pandas DataFrame.

Note: You need to have Excel installed. The code below was tested on macOS, but Windows should work the same.

import pandas as pd
import xlwings as xw

PATH = '/Users/me/Desktop/xlwings_sample.xlsx'
wb = xw.Book(PATH)
sheet = wb.sheets['sample']

df = sheet['A1:C4'].options(pd.DataFrame, index=False, header=True).value
df

Output:

Your use case

A couple of things you might want to adjust for your case:

• A1:C4 is the selection you want to access (from cell A, first row, to cell C, fourth row), one-based index
• index=False means you don’t want to use the cell values as index
• header=True means you want to use the first row cell values as header