I am not sure why I am getting this error although sometimes my code works fine!
Excel file format cannot be determined, you must specify an engine manually.
Here below is my code with steps:
1- list of columns of customers Id:
customer_id = ["ID","customer_id","consumer_number","cus_id","client_ID"]
2- The the code to find all xlsx files in a folder and read them:
l = [] #use a list and concat later, faster than append in the loop
for f in glob.glob("./*.xlsx"):
df = pd.read_excel(f).reindex(columns=customer_id).dropna(how='all', axis=1)
df.columns = ["ID"] # to have only one column once concat
l.append(df)
all_data = pd.concat(l, ignore_index=True) # concat all data
I added the engine openpyxl
df = pd.read_excel(f, engine="openpyxl").reindex(columns = customer_id).dropna(how='all', axis=1)
Now I got a different error:
BadZipFile: File is not a zip file
pandas version: 1.3.0 python version: python3.9 os: MacOS
is there a better way to read all xlsx files from a folder ?
Found it. When an excel file is opened for example by MS excel a hidden temporary file is created in the same directory:
~$datasheet.xlsx
So, when I run the code to read all the files from the folder it gives me the error:
Excel file format cannot be determined, you must specify an engine manually.
When all files are closed and no hidden temporary files ~$filename.xlsx in the same directory the code works perfectly.
I also got an 'Excel file format...' error when I manually changed the 'CSV' suffix to 'XLS'. All I had to do was open excel and save it to the format I wanted.
Also make sure you're using the correct pd.read_* method. I ran into this error when attempting to open a .csv file with read_excel() instead of read_csv(). I found this handy snippet here to automatically select the correct method by Excel file type.
if file_extension == 'xlsx':
df = pd.read_excel(file.read(), engine='openpyxl')
elif file_extension == 'xls':
df = pd.read_excel(file.read())
elif file_extension == 'csv':
df = pd.read_csv(file.read())
Looks like an easy fix for this one. Go to your excel file, whether it is xls or xlsx or any other extension, and do "save as" from file icon. When prompted with options. Save it as CSV UTF-8(Comma delimited)(*.csv)
https://stackoverflow.com/a/32241271/17411729
link to an answer on how to remove hidden files
Mac = go to folder press cmd + shift + . will show the hidden file, delete it, run it back.
In macOS, an "invisible file" named ".DS_Store" is automatically generated in each folder. For me, this was the source of the issue. I solved the problem with an if statement to bypass the "invisible file" (which is not an xlsx, so thus would trigger the error)
for file in os.scandir(test_folder):
filename = os.fsdecode(file)
if '.DS_Store' not in filename:
execute_function(file)
In my case, I usedxlrd. So in terminal:
pip install xlrd
If pandas is not installed, install it:
pip install pandas
Now read the excel file this way:
import pandas as pd
df = pd.read_excel("filesFolder/excelFile.xls", engine='xlrd')