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>'

Corell

i may have not made myself clear here. The problem is precisely that when the file is open in Excel, it gives me this error, and my question is why. Isn't this a read-only operation? – Corell

Sorry, I missed that part. Yes, you can't even read an Excel file if it is open in Excel. You can try using a file URL, such as file://localhost/path/to/workbook.xlsx to see if it helps. – Cota

@Selcuk: Actually, you can read a workbook that is open in Excel, depending on your method. For example, if you use xlrd directly (not through pandas), you can do it. – Nihi

Can you please share the path pointed by fPath; it will really be helpful if you could share the snippet as well. pd.read_excel() does not report any permission issues at all even after having the file opened in MS Excel (on Windows 10, with Anaconda python=3.5.6, pandas=0.23.4 and xlrd=1.2.0). – Derby

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.

Laboratory

Unfortunately setting the read_excel engine parameter to "openpyxl" generates the same permissions error. The "odf" engine is just for OpenDocument file formats (.odf, .ods, .odt) The “pyxlsb” engine supports Binary Excel files – Slosh

Answer 2

Note: The solution below used to work but stopped working for me after a recent Windows 10 update...but it worked for some so it really depends on which version of Windows update you have.

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")

Stagecoach

Hilariously, if you try to copy the open Excel file with shutil.copyfile() then you also get a permission error – Slosh

@Slosh That must be for another reason because I've tested in several times and it works! (also someone gave it thumbs up). Perhaps you're making a copy in a protected folder or something? or perhaps the file is corrupted somehow? – Stagecoach

thanks for following up on my comment, but I'm sorry to report that it is still not working for me. I followed your example more closely and used shutil.copy instead of shutil.copyfile but I still get a permission error (PermissionError: [Errno 13] Permission denied: '@device table.xlsx') if the .xlsx file is open in Excel on my Windows 11 system. It works fine if the .xlsx file is NOT open in Excel. – Slosh

That's weird...I tested it on Windows 10 after your initial comment and in fact I have a program that works using this method all of the time. Perhaps Windows 11 handles the file lock in a different way. – Stagecoach

lol, I guess we'll just have to put it down to a, "undocumented feature" in Windows 11 😉 – Slosh

@Slosh Microsoft never stops surprising us :-) – Stagecoach

I also get a permission error when copying with shutil. Win10, if that makes any difference. Are either of you syncing the file over sharepoint by any chance? Mine excel file is in a folder which is synced to sharepoint, and I'm wondering if this is the issue @Slosh – Siding

@RoryL I think SharePoint also locks the copy as it is trying to sync it. I suggest making the copy outside SharePoint in a local directory. Hope that helps! – Stagecoach

Answer 3

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

Excitability

Works perfectly - can you explain what are the steps in the code? – Rugen

Answer 4

I fix this error simply closing the .xlsx file that was open.

Underbelly

I do not understand why I get negative points – Underbelly

This does not help if the usecase is that the file might be open at any time. – Arleanarlee

This does function if you can close the file. It could be most of the cases. – Underbelly

It may work but it is far from an optimal solution. Especially if you're working interactively and you have to open and close the file several times. 🤷‍♂️ – Glossographer

This is the exact issue i had. I gave you a positive upvote – Schoenburg

Answer 5

Excel is blocking the access to the file. The way I worked around this problem is to copy the file to a temporary file and open this one instead.

Unfortunately most of the copy methods will fault with the same error. (shutils, os, !copy). Finally copying with the powershell solved the problem for me:

import subprocess
import os
import pandas as pd

#df = pd.read_excel('test.xlsx') // This will error when test.xlsx is opened in excel

p = subprocess.call(['powershell.exe', 'copy test.xlsx tempfile.xlsx'])
df = pd.read_excel('tempfile.xlsx', sheet_name="Tabelle2", na_filter=False)
os.remove('tempfile.xlsx')

print(df.shape)

Katelynnkaterina

Answer 6

If you have your file inside a sharepoint / OneDrive folder or something similar, this may also be the root cause of the issue.

I had the same issue and after moving it out of my OneDrive to a local folder, reading the workbook with pd.read_excel as is (no other engine, no copying beforehand) worked fine.

Turley

Answer 7

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.

Swigart

Answer 8

I got this to work by first setting the working directory, then opening the file. Maybe something to do with shared drive permissions and read_excel function.

import os
import pandas as pd

os.chdir("c:\\Users\\...\\")

filepath = "...\\filename.xlsx"
sheetname = 'sheet1'

df_xls = pd.read_excel(filepath, sheet_name=sheetname, engine='openpyxl')

Kaiser

Answer 9

I've tried most of the solutions, but none worked, what i did find is that if i execute this line twice, it will work:

file = pd.ExcelFile("myfile.xlsm")

the error PermissionError: [WinError 5] permission denied will occur in the first execution, but not in the second. so basically this solves it for me :

try:
    file = pd.ExcelFile("myfile.xlsm")
except PermissionError :
    pass
file = pd.ExcelFile("myfile.xlsm")

i hope it helps.

Rockandroll

Answer 10

I experienced this error also. Parsing the engine in the function call did not solve the problem. However, I noticed there were some dots/periods (".") in the file name, besides the one before the .xlsx file extension. Removing these dots from the file name, and then trying the import again, solved the problem.

Duteous

Answer 11

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

Sanguineous

This solution does not work for me - I get the same PermissionError. – Nightwalker

Answer 12

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

Oar

Answer 13

You can set engine='python' then you can run it even if the file is open

df = pd.read_excel(filename, engine = 'python')

Subclavius

"unknown engine: python" – Shanaeshanahan

Does not work, unknow engine – Harcourt