← Back to Blog

How to Fix the Excel File Format Error in Pandas glob

In this blog, if you’re a data scientist or software engineer, you may have encountered the error message stating, ‘Excel file format cannot be determined; you must specify an engine manually’ while utilizing Pandas and glob. This issue arises when Pandas is unable to automatically determine the file format of an Excel file, requiring manual specification of the engine.
By Saturn Cloud | | Miscellaneous | Updated:

As a data scientist or software engineer, you might have come across the error message “Excel file format cannot be determined, you must specify an engine manually” while working with Pandas and glob. This error occurs when Pandas is unable to determine the file format of an Excel file and needs the engine to be specified manually.

In this blog post, we will explore the cause of this error and provide a step-by-step guide on how to fix it.

Table of Contents

  1. What Is Pandas and glob?
  2. The Cause of the Error
  3. How to Fix the Error
  4. Common Errors and Solutions
  5. Conclusion

What Is Pandas and glob?

Pandas is a popular open-source data analysis and manipulation library in Python. It provides data structures and functions for working with structured data, including data frames and series. Pandas has become a go-to tool for data scientists and software engineers because of its ease of use and flexibility.

glob is a Python module used to retrieve files/pathnames matching a specified pattern. It is commonly used alongside Pandas to load multiple files into a data frame.

The Cause of the Error

The “Excel file format cannot be determined, you must specify an engine manually” error occurs when Pandas is unable to determine the file format of an Excel file. This can happen when the file extension is not recognized or when the file is corrupted.

When Pandas reads an Excel file, it uses an engine to determine the file format. The two engines available in Pandas are xlrd and openpyxl. xlrd is the default engine used in Pandas, and it can handle most Excel file formats. openpyxl is an alternative engine that can handle more recent Excel file formats.

If PANDAS is unable to determine the file format, it will raise an error and prompt you to specify an engine manually.

How to Fix the Error

To fix the “Excel file format cannot be determined, you must specify an engine manually” error, you need to specify the engine to be used in Pandas explicitly. Here are the steps to follow:

Step 1: Import the Required Libraries

Before we begin, we need to import the required libraries. In this case, we need Pandas and glob. You can import them using the following code:

import pandas as pd
import glob

Step 2: Specify the Engine

To specify the engine to be used in Pandas, you need to pass the engine parameter to the read_excel() function. The engine parameter accepts either xlrd or openpyxl. Here’s an example:

df = pd.read_excel("filename.xlsx", engine="openpyxl")

In this example, we are using the openpyxl engine to read the Excel file filename.xlsx. If you want to use the xlrd engine, you can replace openpyxl with xlrd.

Step 3: Load Multiple Excel Files

If you’re using glob to load multiple Excel files into a data frame, you can specify the engine as follows:

files = glob.glob("*.xlsx")
dfs = []
for file in files:
    dfs.append(pd.read_excel(file, engine="openpyxl"))
df = pd.concat(dfs, ignore_index=True)

In this example, we are using glob to retrieve all Excel files in the current directory with the .xlsx extension. We then loop through each file, read it into a data frame using the openpyxl engine, and append the data frame to a list. Finally, we concatenate all data frames in the list into a single data frame using the concat() function.

Common Errors and Solutions:

Error 1: xlrd.biffh.XLRDError: Excel xlsx file; not supported This error occurs when trying to read an Excel file in the xlsx format using an outdated version of the xlrd library. To resolve this, install or upgrade xlrd.

# Install or upgrade xlrd
pip install --upgrade xlrd

Error 2: PandasError: Install xlrd >= 1.0.0 for Excel support This error indicates that the xlrd library is either not installed or an older version is being used. Install or upgrade xlrd to resolve the issue.

# Install or upgrade xlrd
pip install --upgrade xlrd

Error 3: No module named 'openpyxl' This error occurs when the Openpyxl library is not installed. Install Openpyxl to resolve the issue.

# Install Openpyxl
pip install openpyxl

Conclusion

In conclusion, the “Excel file format cannot be determined, you must specify an engine manually” error is a common issue that occurs when working with PANDAS and glob. It happens when PANDAS is unable to determine the file format of an Excel file and needs the engine to be specified manually.

To fix this error, you need to specify the engine to be used in PANDAS explicitly. You can do this by passing the engine parameter to the read_excel() function and setting it to either xlrd or openpyxl.

By following the steps outlined in this blog post, you should be able to fix the error and continue working with your Excel files.

About Saturn Cloud

Saturn Cloud is your all-in-one solution for data science & ML development, deployment, and data pipelines in the cloud. Spin up a notebook with 4TB of RAM, add a GPU, connect to a distributed cluster of workers, and more. Request a demo today to learn more.

Start for free