Find xlsx in a directory and assign the sheets to different dataframes

Junior dev here.

My goal is to get 4 different dfs created from 4 different sheets within 1 Excel file which name can vary based upon which update version is created.

This is my attempt.

import pandas as pd, glob

# Finds the target file in local directory
excel_file = glob.glob('file_v*.xlsx')

# Assigns specific sheet from Excel file to df
hr_df = pd.read_excel(open(excel_file, 'rb'), sheet_name='HR')
title_df = pd.read_excel(open(excel_file, 'rb'), sheet_name='Job_Family')
vendor_df = pd.read_excel(open(excel_file, 'rb'), sheet_name='Vendors')
dept_df = pd.read_excel(open(excel_file, 'rb'), sheet_name='Departments')

The error I'm getting is below. I understand that I can't pass a path object. I'm confused on how to connect the recursive search into opening and assigning my dfs.

TypeError: expected str, bytes or os.PathLike object, not list

Any suggestions and explanations are appreciated. Thank you!

CodePudding user response：

Try this:

import pandas as pd, glob

# Finds the target file in local directory
excel_file = glob.glob('file_v*.xlsx')[0]

# Assigns specific sheet from Excel file to df
f = open(excel_file, 'rb')
hr_df = pd.read_excel(f, sheet_name='HR')
title_df = pd.read_excel(f, sheet_name='Job_Family')
vendor_df = pd.read_excel(f, sheet_name='Vendors')
dept_df = pd.read_excel(f, sheet_name='Departments')

f.close()