title: python efficient way to append all worksheets in multiple excel into pandas dataframe
body: <p>I have around 20++ xlsx files, inside each xlsx files might contain different numbers of worksheets. But thank god, all the columns are the some in all worksheets and all xlsx files. By referring to <a href="https://stackoverflow.com/questions/26521266/using-pandas-to-pd-read-excel-for-multiple-worksheets-of-the-same-workbook">here</a>&quot;, i got some idea. I have been trying a few ways to import and append all excel files (all worksheet) into a single dataframe (around 4 million rows of records).</p>
  <p>Note: i did check <a href="https://stackoverflow.com/questions/20908018/import-multiple-excel-files-into-python-pandas-and-concatenate-them-into-one-dat">here</a>&quot; as well, but it only include file level, mine consits file and down to worksheet level.</p>
  <h2>I have tried below code</h2>
  <pre><code># import all necessary package
  import pandas as pd
  from pathlib import Path
  import glob
  import sys
  
  # set source path
  source_dataset_path = &quot;C:/Users/aaa/Desktop/Sample_dataset/&quot;
  source_dataset_list = glob.iglob(source_dataset_path + &quot;Sales transaction *&quot;)
  
  for file in source_dataset_list:
  #xls = pd.ExcelFile(source_dataset_list[i])
      sys.stdout.write(str(file))
      sys.stdout.flush()
      xls = pd.ExcelFile(file)
      out_df = pd.DataFrame() ## create empty output dataframe
  
      for sheet in xls.sheet_names:
          sys.stdout.write(str(sheet))
          sys.stdout.flush() ## # View the excel files sheet names
          #df = pd.read_excel(source_dataset_list[i], sheet_name=sheet)
          df = pd.read_excel(file, sheetname=sheet)
          out_df = out_df.append(df)  ## This will append rows of one dataframe to another(just like your expected output)
  </code></pre>
  <h2>Question:</h2>
  <p>My approach is like first read the every single excel file and get a list of sheets inside it, then load the sheets and append all sheets. The looping seems not very efficient expecially when datasize increase for every append.</p>
  <h3>Is there any other efficient way to import and append all sheets from multiple excel files?</h3>