ValueError: Excel file format cannot be determined, you must specify an engine manually

Question

I'm trying to convert multiples files in a diretory to CSV format; I did this code, and ran it in 3 folders. But that one i get this error

i found something like this:

if file_extension == 'xlsx':

    df = pd.read_excel(file.read(), engine='openpyxl')

elif:

    df = pd.read_csv(file.read())

but i get the syntax error;

How can i solve this problem and run the code?

Answer 1

So I was getting the same issue and when I went to troubleshoot I noticed that some of the incoming files contained a "~$" before the file name. These files are so-called owner files (sometimes referred to as "lock" files). If you do something like this below, you can bypass them. Or at least that's what I did. You'll have to look and see what's different with the files you cant read.

for file in files:
    if file.endswith('.xlsx'):
        if '~$' in file:
            continue
        else:
          file_list.append(in_path + '/' + file)

I hope this answers your question.