Python/Excel - Merge .xlsx workbooks from file into .csv

Question

Attempting to compile multiple .xlsx workbooks from a file into a single .csv

Loop I've created is only capturing the first workbook from the file.

How can I alter this to capture all workbooks in a file? Data is only on Sheet1 from all workbooks

import os
import xlrd
import csv

rootdir = r'C:\Users\username\Desktop\Mults'
filenames = []
for subdir, dir, files in os.walk(rootdir):
   for file in files:
      filenames.append(os.path.join(subdir, file))
      wb = xlrd.open_workbook(os.path.join(subdir, file))
      sh = wb.sheet_by_index(0)
      with open('acit_multsTEST.csv','wb') as f:
        c = csv.writer(f)
        for r in range(sh.nrows):
            c.writerow(sh.row_values(r))

I appreciate any help!

Thank you!

Answer 1

So you have to do the following.

• Get a list of all the workbooks
• Open a main csv to append all your data to
• Iterate through your list of workbooks

• Append each sheet to your csv

  import glob
  import os
  import xlrd
  import csv
  
  ROOTDIR = r'C:\Users\username\Desktop\Mults'
  wb_pattern = os.path.join(ROOTDIR, '*.xlsx')
  
  workbooks = glob.glob(wb_pattern)
  
  with open('out.csv', 'wb') as outcsv:
      writer = csv.writer(outcsv)
      for wb in workbooks:
          book_path = os.path.join(ROOTDIR, wb)
          book =  xlrd.open(book_path)
          sheet = book.sheet_by_index(0)
          for row_num in xrange(sheet.nrows):
             row = sheet(row_num) 
             writer.writerow(row)