Open excel workbook in OneDrive folder using Python

1
votes

I am trying to use pandas.read_excel to read data from the Excel workbook. However, if the workbook is stored in the OneDrive folder and synced to OneDrive, it cannot be read when it is already open. It reports an error message

[Errno 13] Permission denied: 'C:\Users\Name\OneDrive\Test\Test.xlsx'

seems failed when trying to open the file

with open(filename, "rb") as f:

But when I closed the workbook, pandas.read_excel succeeded fetching data from it. And files not in the OneDrive folder can always be accessed no matter open or not. Please see below cases:

pd.read_excel(r'C:\Users\Name\Test\Book1.xlsx') # successful when book is open pd.read_excel(r'C:\Users\Name\Test\Book1.xlsx') # successful when book is closed pd.read_excel(r'C:\Users\Name\OneDrive\Book1.xlsx') # failed when book is open pd.read_excel(r'C:\Users\Name\OneDrive\Book1.xlsx') # successful when book is closed

I was wondering if it is something related to OneDrive? And is there any way I can read data from an opened workbook stored in the OneDrive folder? Since I want to read data from the closed workbook as well, xlwings should not be a good idea, is there a general technique I can use to read data from all excel workbooks no matter open or not, and no matter in OneDrive or not?

1
pythonexcelpandasonedrive
You can always read the file when it's closed, but only sometimes when it's open. Is possible to simply leave it closed? – Paul H
Or you can download first the excel file and then manipulate it all of this with python – Straight Coding
The issue is the way OneDrive works. Remember, OneDrive is trying to sync the open file to the cloud. So my "guess" is, the PermissionError results from OneDrive having access. – Trenton McKinney

1 Answers

0
votes

I have had success with this by simply turning off the autosave feature in excel.

edit: This seems to only work with an older .xls file and not .xlsx