pd.read_excel throws PermissionError if file is open in Excel

Question

Whenever I have the file open in Excel and run the code, I get the following error which is surprising because I thought read_excel should be a read only operation and would not require the file to be unlocked?

    Traceback (most recent call last):
  File "C:\Users\Public\a.py", line 53, in <module>
    main()
  File "C:\Users\Public\workspace\a.py", line 47, in main
    blend = plStream(rootDir);
  File "C:\Users\Public\workspace\a.py", line 20, in plStream
    df = pd.read_excel(fPath, sheetname="linear strategy", index_col="date", parse_dates=True)
  File "C:\Users\Public\Continuum\Anaconda35\lib\site-packages\pandas\io\excel.py", line 163, in read_excel
    io = ExcelFile(io, engine=engine)
  File "C:\Users\Public\Continuum\Anaconda35\lib\site-packages\pandas\io\excel.py", line 206, in __init__
    self.book = xlrd.open_workbook(io)
  File "C:\Users\Public\Continuum\Anaconda35\lib\site-packages\xlrd\__init__.py", line 394, in open_workbook
    f = open(filename, "rb")
PermissionError: [Errno 13] Permission denied: '<Path to File>'

Answer 1

Generally Excel have a lot of restrictions when opening files (can't open the same file twice, can't open 2 different files with the same name ..etc).
I don't have excel on machine to test, but checking the docs for read_excel I've noticed that it allows you to set the engine.
from the stack trace you posted it seems like the error is thrown by xlrd which is the default engine used by pandas.

try using any of the other ones

Supported engines: “xlrd”, “openpyxl”, “odf”, “pyxlsb”, default “xlrd”.

so try with the rest, like

df = pd.read_excel(fPath, sheetname="linear strategy", index_col="date", parse_dates=True, engine="openpyxl")

I know this is not a real answer, but you might want to submit a bug report to pandas or xlrd teams.

Answer 2

I would suggest using the xlwings module instead which allows for greater functionality.

Firstly, you will need to load your workbook using the following line:

If the spreadsheet is in the same folder as your python script:

import xlwings as xw
workbook = xw.Book('myfile.xls')

Alternatively:

workbook = xw.Book('"C:\Users\...\myfile.xls')

Then, you can create your Pandas DataFrame, by specifying the sheet within your spreadsheet and the cell where your dataset begins:

df = workbook.sheets[0].range('A1').options(pd.DataFrame, 
                                            header=1,
                                            index=False, 
                                            expand='table').value

When specifying a sheet you can either specify a sheet by its name or by its location (i.e. first, second etc.) in the following way:

workbook.sheets[0] or workbook.sheets['sheet_name']

Lastly, you can simply install the xlwings module by using Pip install xlwings

Answer 3

As a workaround I suggest making python create a copy of the original file then read from the copy. After that the code should delete the copied file. It's a bit of extra work but should work.

Example

import shutil
shutil.copy("C://Test//Test.xlsx", "C://Test//koko.xlsx")

Answer 4

Mostly there is no issues in your code. [ If you publish the code it will be easier.] You need to change the permissions of the directory you are using so that all users have read and write permissions.

Answer 5

You can set engine = 'xlrd', then you can run the code while Excel has the file open.

df = pd.read_excel(filename, sheetname, engine = 'xlrd')

You may need to pip install xlrd if you don't have it

Answer 6

You may also want to check if the file has a password? Alternatively you can open the file with the password required using the code below:

import sys
import win32com.client
xlApp = win32com.client.Dispatch("Excel.Application")
print "Excel library version:", xlApp.Version
filename, password = <-- enter your own filename and password
xlwb = xlApp.Workbooks.Open(filename, Password=password) 
# xlwb = xlApp.Workbooks.Open(filename)
xlws = xlwb.Sheets([insert number here]) # counts from 1, not from 0
print xlws.Name
print xlws.Cells(1, 1) # that's A1