how to read any sheet with the sheet name containing 'mine' from multiple excel files in a folder using python? i am using xlrd

Question

How to read any sheet with the sheet name containing 'mine' from multiple excel files in a folder using python? I am using xlrd.

dir_to_load = "C:\\Users\\User\\Desktop\\"
dir_to_save = "C:\\Users\\User\\Desktop\\"
file_name = "Test.xlsx"

os.chdir(dir_to_load)

wb= xlrd.open_workbook(file_name)
ws = wb.sheet_by_index(-1)
out_list = []

I know I am only reading one file and getting the last sheet but I want the sheets containing or LIKE %Mine% from multiple files. Thanks.

Answer 1

Here is a solution that is based around using a function, get_worksheet_names(spreadsheet_name), to get the names of each worksheet in a spreadsheet:

import xlrd 

# Utility function to get worksheet names from a spreadsheet:
def get_worksheet_names(spreadsheet_name):
    worksheets = xlrd.open_workbook(spreadsheet_name).sheet_names()

    worksheet_names = []
    for j in worksheets:
        worksheet_names.append(worksheets.sheet_by_name(j))

    return worksheet_names  


# Here is your original code, modified:
dir_to_load = "C:\\Users\\User\\Desktop\\"
dir_to_save = "C:\\Users\\User\\Desktop\\"
file_name = "Test.xlsx"

os.chdir(dir_to_load)

wsnames = get_worksheet_names(file_name)

for name in wsnames:
    if 'mine' in name: # change to whatever search criteria you'd like here
        print name

(Note on terminology: Microsoft uses "workbook" and "spreadsheet" interchangeably, but I prefer "spreadsheet" as everyone knows what that means, whereas "workbook" is more ambiguous.)