CODEWITHSUNDEEP
pythonexcelcsvexport-to-csvxlrd
Mike V.
Mike V.

Reputation: 83

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

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!

Upvotes: 0

Views: 1781

Answers (1)

b10n
b10n

Reputation: 1186

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)

Upvotes: 3

Related Questions