CODEWITHSUNDEEP
pythonexcelpython-3.xpandas
guagay_wk
guagay_wk

Reputation: 28030

Pandas unable to open this Excel file

I am trying to use python pandas to open an Excel file. Code is simple as shown below;

import pandas as pd
df = pd.read_excel('../TestXLWings.xlsm', sheetname="TestSheet")

I got an error below;

Traceback (most recent call last):
  File "C:\Program Files\JetBrains\PyCharm Community Edition 2017.2\helpers\pydev\pydevd.py", line 1599, in <module>
    globals = debugger.run(setup['file'], None, None, is_module)
  File "C:\Program Files\JetBrains\PyCharm Community Edition 2017.2\helpers\pydev\pydevd.py", line 1026, in run
    pydev_imports.execfile(file, globals, locals)  # execute the script
  File "C:\Program Files\JetBrains\PyCharm Community Edition 2017.2\helpers\pydev\_pydev_imps\_pydev_execfile.py", line 18, in execfile
    exec(compile(contents+"\n", file, 'exec'), glob, loc)
  File "C:/Users/testing/Dropbox/Test-XLwings/test.py", line 3, in <module>
    df = pd.read_excel('../TestXLWings.xlsm', sheetname="TestSheet")
  File "C:\ProgramData\Anaconda3\lib\site-packages\pandas\io\excel.py", line 203, in read_excel
    io = ExcelFile(io, engine=engine)
  File "C:\ProgramData\Anaconda3\lib\site-packages\pandas\io\excel.py", line 260, in __init__
    self.book = xlrd.open_workbook(io)
  File "C:\ProgramData\Anaconda3\lib\site-packages\xlrd\__init__.py", line 441, in open_workbook
    ragged_rows=ragged_rows,
  File "C:\ProgramData\Anaconda3\lib\site-packages\xlrd\book.py", line 87, in open_workbook_xls
    ragged_rows=ragged_rows,
  File "C:\ProgramData\Anaconda3\lib\site-packages\xlrd\book.py", line 595, in biff2_8_load
    raise XLRDError("Can't find workbook in OLE2 compound document")
xlrd.biffh.XLRDError: Can't find workbook in OLE2 compound document

My Excel file is xlsm and protected by password. What does OLE2 compound document mean exactly? Does pandas have problems opening this kind of Excel files? I am using python v3.6

Upvotes: 15

Views: 64474

Answers (2)

Surinder
Surinder

Reputation: 31

I would create a new excel file and remove sensitivity label in excel. Then be able to read the file with pd.

Upvotes: 2

guagay_wk
guagay_wk

Reputation: 28030

I will answer my own question. In one of the comments from ayhan, Excel-protected files cannot be read by xlrd. One solution is to remove the protection.

I need the command to unprotect an Excel file from python

Another solution to read the Excel-protected file is to use xlwings. I have verified that xlwings is able to read protected Excel files when the Excel file is opened.

Upvotes: 24

Related Questions