You can use Pandas pandas.read_excel just like pandas.read_csv:

import pandas as pd
file_errors_location = 'C:\\Users\\atheelm\\Documents\\python excel mission\\errors1.xlsx'
df = pd.read_excel(file_errors_location)
print(df)

There are two modules for reading xls file : openpyxl and xlrd

This script allow you to transform a excel data to list of dictionnaries using xlrd

import xlrd

workbook = xlrd.open_workbook('C:\\Users\\atheelm\\Documents\\python excel mission\\errors1.xlsx')
workbook = xlrd.open_workbook('C:\\Users\\atheelm\\Documents\\python excel mission\\errors1.xlsx', on_demand = True)
worksheet = workbook.sheet_by_index(0)
first_row = [] # The row where we stock the name of the column
for col in range(worksheet.ncols):
    first_row.append( worksheet.cell_value(0,col) )
# tronsform the workbook to a list of dictionnary
data =[]
for row in range(1, worksheet.nrows):
    elm = {}
    for col in range(worksheet.ncols):
        elm[first_row[col]]=worksheet.cell_value(row,col)
    data.append(elm)
print data

Unfortunately, the python engine 'xlrd' that is required to read the Excel docs has explicitly removed support for anything other than xls files.

So here's how you can do it now -

• Install openpyxl:

• Change your pandas code to:

  pandas.read_excel('cat.xlsx', engine='openpyxl')

Note: This worked for me with the latest version of Pandas (i.e. 1.1.5). Previously, I was using version 0.24.0 and it didn't work so I had to update to latest version.

Another way to do it:

import openpyxl 
workbook_errors = openpyxl.Workbook()
workbook_errors = openpyxl.load_workbook(file_errors_location)