pandas - python pd.read_excel [WinError 32] The process cannot access the file because it is being used by another process

Question

I am struggling with the following issue:

I receive a daily .xls file. However, the format of that file is not the same as the extension. Sometimes it's .xlsx and sometimes it's .xlsb. I am trying to read the contents of this file into a pandas data frame, so I wrote the following code:

import openpyxl
import pyxlsb 
import pyodbc
import os
import pandas as pd

try:
    full_name1 = "my_file.xls"
    full_name2 = "my_file.xlsx"
    os.rename(full_name1, full_name2)
    df = pd.read_excel(full_name2, sheet_name = 'Sheet 2', engine = 'openpyxl', skiprows = range(0, 7), usecols = "A:X")

except:
    
    full_name1 = "my_file.xlsx"
    full_name2 = "my_file.xlsb"
    os.rename(full_name1, full_name2)
    df = pd.read_excel(full_name2, sheet_name = 'Sheet 2', engine = 'pyxlsb', skiprows = range(0, 7), usecols = "A:X")    

However, I get the error:

[WinError 32] The process cannot access the file because it is being used by another process: 'my_file.xlsx' -> 'my_file.xlsb'

And the 'my_file.xlsx' seems to still be in use.

Is there a way around this?

question from:https://stackoverflow.com/questions/65939828/python-pd-read-excel-winerror-32-the-process-cannot-access-the-file-because-it

Answer 1

Why not going through the list of files in the directory?:

import os
for directory, subdirlist, filelist in os.walk('C:\Google Drive\Python\Uhlmann\'):
    for file in filelist:
        df = pd.read_excel(file, sheet_name = 'Sheet 2', engine = 'openpyxl', skiprows = range(0, 7), usecols = "A:X")