title: how to merge multiple excel files using python?
body: <p>I have a code that zipped multiple excel files, and i would like to merge these files and to display their contents in one excel file. Do you have any new ideas how to write this function?</p>
  <p>Also, where is the error in my functions?</p>
  <p>When i run my code it crash and display the below error:</p>
  <pre><code>AttributeError: 'ZipFile' object has no attribute 'seek'
  Traceback:
  File &quot;F:\AIenv\lib\site-packages\streamlit\script_runner.py&quot;, line 333, in _run_script
      exec(code, module.__dict__)
  File &quot;f:\AIenv\streamlit\app2.py&quot;, line 131, in &lt;module&gt;
      excel_file_merge(f)
  File &quot;f:\AIenv\streamlit\app2.py&quot;, line 77, in excel_file_merge
      archive = zipfile.ZipFile(zip_file_name, 'r')
  File &quot;c:\users\lt gm\appdata\local\programs\python\python37\Lib\zipfile.py&quot;, line 1222, in __init__
      self._RealGetContents()
  File &quot;c:\users\lt gm\appdata\local\programs\python\python37\Lib\zipfile.py&quot;, line 1285, in _RealGetContents
      endrec = _EndRecData(fp)
  File &quot;c:\users\lt gm\appdata\local\programs\python\python37\Lib\zipfile.py&quot;, line 259, in _EndRecData
      fpin.seek(0, 2)
  </code></pre>
  <h1>code:</h1>
  <pre><code>import pandas as pd
  import numpy as np
  import zipfile
  import os
  
  
  # Excel file merge function
  def excel_file_merge(zip_file_name):
      df = pd.DataFrame()
      archive = zipfile.ZipFile(zip_file_name, 'r')
      with zipfile.ZipFile(zip_file_name, &quot;r&quot;) as f:
          for file in f.namelist():
              xlfile = archive.open(file)
          if file.endswith('.xlsx'):
              # Add a note indicating the file name that this dataframe originates from
              df_xl = pd.read_excel(xlfile, engine='openpyxl')
              df_xl['Note'] = file
              # Appends content of each Excel file iteratively
              df = df.append(df_xl, ignore_index=True)
      return df
  
  # function in order to zipped files.
  def get_all_file_path(directory):
      file_paths=[]
      for root,directories,files in os.walk(directory):
          for filename in files:
              filepath = os.path.join(root,filename)
              file_paths.append(filepath)
      return file_paths
      
  file_paths = get_all_file_path(uploaded_file)
  print(&quot;following files will be zipped: &quot;)
  for file_name in file_paths:
      print(file_name)
  with zipfile.ZipFile(&quot;my _python_files.zip&quot;,&quot;w&quot;)as f:
      for file in file_paths:
          f.write(file)
  f.close()
  print(&quot;All Files Zipped successfully&quot;)
  excel_file_merge(f)
  </code></pre>
  <p>the error appear at this line  <code>archive = zipfile.ZipFile(zip_file_name, 'r')</code></p>