title: How to get sheet names for list of files dynamically?
body: <p>I have the following list allfiles:</p>
  <pre><code>['Input_controls\\CTA-02 Q2.xlsx',
   'Input_controls\\CTA-14 Q2.xlsx',
   'Input_controls\\CTA-23 Q2.xlsx',
   'Input_controls\\CTA-57 Q2.xlsx',
   'Input_controls\\PVHE FMS GRC CTA Analysis Q2 2021.xlsx',
   'Input_controls\\PVHE FMS GRC SoD Analysis Q2 2021.xlsx',
   'Input_controls\\SOD-16 Q2.xlsb',
   'Input_controls\\SOD-21_Q2.xlsx',
   'Input_controls\\SOD-74 Q2.xlsx']
  </code></pre>
  <p>Now I would like a df with all files and their respective sheets
  So ideal output would be:</p>
  <pre><code>file                             sheet_name#1 sheet_name#2 sheet_name#x
  
  'Input_controls\\CTA-02 Q2.xlsx'  foo           bar           test
  </code></pre>
  <p>The above for each input file</p>
  <p>I've drafted the following code but it keeps on executing (never stops):</p>
  <pre><code>#bad
  #xl = pd.ExcelFile('archvio.xlsx')
  df_combined = pd.DataFrame()
  for file in allfiles:
      xl = pd.ExcelFile(file)
      for sheet_name in xl.sheet_names:
          df = xl.parse(sheet_name)
          df['sheet'] = sheet_name       # this adds `sheet_name` into the column `Week`
          df_combined = df_combined.append(df)
  </code></pre>