As @COLDSPEED so eloquently pointed out the error explicitly tells you to install xlrd.

pip install xlrd

And you will be good to go.

You can use pandas directly here, I usually like to create a dictionary of DataFrames (with keys being the sheet name):

In [11]: xl = pd.ExcelFile("C:\GreenCSV\Calgary\CWater.xlsx")

In [12]: xl.sheet_names  # in your example it may be different
Out[12]: [u'Sheet1', u'Sheet2', u'Sheet3']

In [13]: dfs = {sheet: xl.parse(sheet) for sheet in xl.sheet_names}

In [14]: dfs['Sheet1'] # access DataFrame by sheet name

You can check out the docs on the parse which offers some more options (for example skiprows), and these allows you to parse individual sheets with much more control...

Or can someone help me take the sheet from xlrd and convert it into a Pandas dataframe?

pd.read_excel can take a book...

import xlrd

book = xlrd.open_workbook(filename='./file_check/file.xls')

df = pd.read_excel(book, skiprows=5)

print(df)

   some   column headers
0     1     some     foo
1     2  strings     bar
2     3     here     yes
3     4      too      no

I'll include the code below that may help if you want to check/handle Excel file types. Maybe you can adapt it for your needs.

The code loops through a local folder and shows the file and extension but then uses python-magic to drill into it. It also has a column showing guessing from mimetypes but that isn't as good. Do zoom into the image of the frame and see that some .xls are not what the extension says. Also a .txt is actually an Excel file.

import pandas as pd
import glob
import mimetypes
import os
# https://pypi.org/project/python-magic/
import magic

path = r'./file_check' # use your path
all_files = glob.glob(path + "/*.*")

data = []

for file in all_files:
    name, extension = os.path.splitext(file)
    data.append([file, extension, magic.from_file(file, mime=True), mimetypes.guess_type(file)[0]])

df = pd.DataFrame(data, columns=['Path', 'Extension', 'magic.from_file(file, mime=True)', 'mimetypes.guess_type'])

# del df['magic.from_file(file, mime=True)']

df

From there you could filter files based on their type:

xlsx_file_format = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'

xls_file_format = 'application/vnd.ms-excel'

for file in all_files:
    if magic.from_file(file, mime=True) == xlsx_file_format:
        print('xlsx')
        #  DO SOMETHING SPECIAL WITH XLSX FILES
    elif magic.from_file(file, mime=True) == xls_file_format:
        print('xls')
        #  DO SOMETHING SPECIAL WITH XLS FILES
    else:
        continue

dfs = []

for file in all_files:
    if (magic.from_file(file, mime=True) == xlsx_file_format) or \
    (magic.from_file(file, mime=True) == xls_file_format):
        # who cares, it all works with this for the demo...
        df = pd.read_excel(file, skiprows=5, names=['some', 'column', 'headers'])
        dfs.append(df)
    
print('\nHow many frames did we get from seven files? ', len(dfs))

Output:

xlsx
xls
xls
xlsx

How many frames did we get from seven files?  4

There are several ways you can go about doing this.

1. Use pandas.read_excel

2. Manually convert excel workbook to csv file then use pandas.read_csv

3. Use Python code to convert excel workbook to csv file then use pandas.read_csv

The third method is your best approach. It's the fastest.

Here is my excel workbook

1

df1 = pandas.read_excel('workbook.xlsx')
print(df1)

Out

  col1    col2     col3        col4
0   I   should       be  completing
1   my  linear  algebra    homework

2

I named the .csv file 'workbook.csv'

df2 = pandas.read_csv('workbook.csv')
print(df2)

Out

  col1    col2     col3        col4
0   I   should       be  completing
1   my  linear  algebra    homework

3

import csv
import xlrd
with xlrd.open_workbook('workbook.xlsx') as wb:
    sh = wb.sheet_by_index(0)
    with open('workbook.csv', 'w', newline="") as csv_file:
        col = csv.writer(csv_file)
        for row in range(sh.nrows):
            col.writerow(sh.row_values(row))
df3 = pandas.read_csv('workbook.csv')
print(df3)

Here is the .csv produced, calle

col1,col2,col3,col4
I,should,be,completing
my,linear,algebra,homework

And then the subsequent dataframe

  col1    col2     col3        col4
0   I   should       be  completing
1   my  linear  algebra    homework

VERDICT

All the outputs for each method is the same but method 3 is the fastest. This means you should import csv and xlrd to convert each of your xlsx files to csv files and then use read_csv. You can use os to get into your specific directories. Add for loops for each file for solution 3.

NOTE

Test method 1 versus 2 for yourself because I am getting somewhat inconsistent results using the timeit module and writing

start = timeit.timeit()
# code
end = timeit.timeit()
print(f"Time {end - start} {df}")

but I am not sure if I am using it correctly. So, at the very least, try the first and last methods for yourself and see which ones go faster.