Python - Read Excel files while file is open

user_17361394 0
Viewed 374

Python newbie here. I have a folder full of Excel files that I want to read into a dataframe. The following code works great, unless someone is entering data into one of the Excel files, then I get a "PermissionError: [Errno 13] Permission denied" message.

transfolder = *folder where Excel files are located*
transfiles = os.listdir(transfolder)
transdf = pd.DataFrame()
df = pd.DataFrame()
for file in transfiles:
     df = pd.read_excel(os.path.join(transfolder,file), sheet_name = 'Main')
     transdf = transdf.append(df, ignore_index=True)

There are several people working on data entry in the Excel files at any given time so I can't just make them all get out. Is there any way to get it to read the files while they're open, like PowerQuery? I'd use PQ but the files are too large and it's too slow.

I already tried this and no luck - I think it's for older versions: pd.read_excel throws PermissionError if file is open in Excel

pandas dataframe excel python
2 Answers

Whenever you want to operate with these files, create a local copy of them, read them into your dataframe, delete the local copy.

Notice that you will be using the last saved version, so you might not be using the most up to date version given that someone is editing the file.

user_70672240

The answer came to me around 3am, as usual... I added a filter to remove temporary files, like this:

transfolder = *folder where Excel files are located*
transfiles = os.listdir(transfolder)
transdf = pd.DataFrame()
df = pd.DataFrame()
for file in transfiles:
     str = file
     if not str.startswith('~$'):
     df = pd.read_excel(os.path.join(transfolder,file), sheet_name = 'Main')
     transdf = transdf.append(df, ignore_index=True)

Working like a charm so far.

user_173613940
Related