CODEWITHSUNDEEP
pythonexcelpandasdataframe
Sn4k3Ch4rm3r
Sn4k3Ch4rm3r

Reputation: 55

Python - Read Excel files while file is open

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

Upvotes: 2

Views: 1925

Answers (2)

Sn4k3Ch4rm3r
Sn4k3Ch4rm3r

Reputation: 55

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.

Upvotes: 1

Gabriel Pellegrino
Gabriel Pellegrino

Reputation: 1122

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.

Upvotes: 1

Related Questions