title: How to convert a worksheet to a Data frame in Pandas?
body: <p>I am trying to read different worksheets from an Excel workbook in Python with  Pandas. When I read the entire workbook and then I want to apply a .merge() then the first worksheet is read but the others are not considered. I tried to read each worksheet of the workbook but I guess they were not successfully converted to data frames because when I apply .merge() I end up with the following error: <code>ValueError: Invalid file path or buffer object type: &lt;class 'pandas.core.frame.DataFrame'&gt;</code></p>
  
  <p>This is what I have done so far:</p>
  
  <h1>This code works for converting the entire workbook to a data frame but only the data of the first worksheet is processed</h1>
  
  <pre><code>import pandas as pd
  import pypyodbc
  
  #sql extractor
  start_date = date.today()
  retrieve_values = "[DEV].[CS].[QT_KPIExport] @start_date='{start_date:%Y-%m-%d}'".format(
      start_date=start_date)
  connection = pypyodbc.connect(driver="{SQL Server}", server="xxx.xxx.xxx.xxx", uid="X",pwd="xxx", Trusted_Connection="No")
  data_frame_sql = pd.read_sql(retrieve_values, connection)
  
  #Read the entire workbook 
  wb_data = pd.ExcelFile("C:\\Users\\Dev\\Testing\\Daily_Data\\NSN-Daily Data Report.xlsx")
  #Convert to a dataframe the entire workbook
  data_frame_excel = pd.read_excel(wb_data,index_col=None,na_values=['NA'],parse_cols="J")
  
  #apply merge
  merged_df   = data_frame_sql.merge(data_frame_excel,how="inner",on="sectorname")
  </code></pre>
  
  <h2>This code tries to read the different worksheets and convert them to data frames with no success...yet! (check the answer below)</h2>
  
  <pre><code>data_frame_sql = pd.read_sql(retrieve_values, connection)
  
  #Method 1: Tried to parse worksheet 2
  #Read the entire workbook and select the specific worksheet
  wb_data = pd.ExcelFile("C:\\Users\\Dev\\Testing\\Daily_Data\\NSN-Daily Data Report.xlsx", sheetname="Sheet-2")
  data_frame_excel = pd.read_excel(wb_data,index_col=None,na_values=['NA'],parse_cols="J")
  
  #apply merge
  merged_df   = data_frame_sql.merge(data_frame_excel,how="inner",on="sectorname")
  #No success... the data of the first sheet is read
  
  #Method 2: Tried to parse worksheet 2
  #Read the entire workbook
  wb_data = pd.ExcelFile("C:\\Users\\Dev\\Testing\\Daily_Data\\NSN-Daily Data Report.xlsx")
  data_frame_excel = pd.read_excel(wb_data,index_col=None,na_values=['NA'],parse_cols="J")
  
  #select one specific sheet
  ws_sheet_2 = wb_data.parse("Sheet-2")
  
  #apply merge
  merged_df   = data_frame_sql.merge(ws_sheet_2,how="inner",on="sectorname")
  # No success.... ValueError: Invalid file path or buffer object type: &lt;class 'pandas.core.frame.DataFrame'&gt;
  </code></pre>
  
  <p>Any help or advice is greatly appreciated. </p>