title: Python Merge .xls Files
body: <p>I have a folder full of Excel files. One annoying aspect is that they are all <code>.xls</code> (and not <code>.xlsx</code>).</p>
  <p>What I need to do is read in each of the <code>.xls</code> files, delete the first 7 rows, then take the remaining document and add it to a &quot;master.xlsx&quot; file. (note: master.xlsx doesn't have to be preexisting, can be newly created)</p>
  <p>I haven't started trying to remove rows yet, just trying to simply merge them, but can't figure out how. Do I need to somehow convert all .xls to .xlsx first, then try to merge? I've spent hours looking at other Stack Overflow questions and online resources. It seems like some kind of ancient technology. Also, it's worth mentioning I'm using Python3.</p>
  <p>Here's my code so far:</p>
  <pre><code>import os
  from numpy import genfromtxt
  import re
  import urllib.request
  import pandas as pd
  
  
  # script directory
  script_dir = os.path.dirname(r'C:/Users/Kenny/Desktop/pythonReports/')
  
  
  # get array list of files
  files = []
  file_abs_path = script_dir + '/excels/'
  for file in os.listdir(file_abs_path):
      if file.endswith('.xls'):
          excel_file_path = script_dir + '/excels/' + file
          files.append(excel_file_path)
  
  # f is full file path
  df_array = []
  writer = pd.ExcelWriter('master.xlsx')
  for f in files:
      sheet = pd.read_html(f)
  
      for n, df in enumerate(sheet):
          df_array.append(df)
          # df = df.append(df)
      # df.to_excel(writer,'sheet%s' % n)
  print(df_array)
  
  for df in df_array:
          # new_df = new_df.append(df)
          new_df = pd.concat(df_array)
          new_df.to_excel(writer,'sheet%s' % n)
          writer.save()
      # print(sheet)
  </code></pre>
  <p>At some point I didn't get errors and it was reading and copying the content correctly, but it would re-write the <code>master.xlsx</code> and overwrite the old stuff, instead of concatenating it.</p>
  <p><em><strong>EDIT</strong></em></p>
  <p>Merge is working now. My difficulty now is that I need to grab data from a cell, delete the first 7 rows, then create a new column and add that data to all rows in that column (for the length of the document).</p>
  <p>I think that one thing that makes this hard is I have to use <code>read_html()</code> because <code>read_excel()</code> doesn't work. I get the following error:</p>
  <pre><code>Traceback (most recent call last):
    File &quot;script.py&quot;, line 83, in &lt;module&gt;
      sheet = pd.read_excel(f)
    File &quot;C:\Users\Kenny\AppData\Local\Programs\Python\Python36-32\lib\site-packages\pandas\io\excel.py&quot;, line 200, in read_excel
      io = ExcelFile(io, engine=engine)
    File &quot;C:\Users\Kenny\AppData\Local\Programs\Python\Python36-32\lib\site-packages\pandas\io\excel.py&quot;, line 257, in __init__
      self.book = xlrd.open_workbook(io)
    File &quot;C:\Users\Kenny\AppData\Local\Programs\Python\Python36-32\lib\site-packages\xlrd\__init__.py&quot;, line 441, in open_workbook
      ragged_rows=ragged_rows,
    File &quot;C:\Users\Kenny\AppData\Local\Programs\Python\Python36-32\lib\site-packages\xlrd\book.py&quot;, line 91, in open_workbook_xls
      biff_version = bk.getbof(XL_WORKBOOK_GLOBALS)
    File &quot;C:\Users\Kenny\AppData\Local\Programs\Python\Python36-32\lib\site-packages\xlrd\book.py&quot;, line 1230, in getbof
      bof_error('Expected BOF record; found %r' % self.mem[savpos:savpos+8])
    File &quot;C:\Users\Kenny\AppData\Local\Programs\Python\Python36-32\lib\site-packages\xlrd\book.py&quot;, line 1224, in bof_error
      raise XLRDError('Unsupported format, or corrupt file: ' + msg)
  xlrd.biffh.XLRDError: Unsupported format, or corrupt file: Expected BOF record; found b'\n&lt;html&gt;\n'
  </code></pre>