Python Opening in Excel writing to CSV

Question

I am learning Python.

Here is what I am trying to do and in the order I'm trying to do it:

1. Open up an excel file
2. Find all of the worksheets
3. Take all of the rows on each work sheet and save as a csv
4. If there are 3 worksheets on 1 excel file, there should be 3 csv files.

Problem: I can't seem to be able to write the number of rows onto the worksheet.

My code:

import os, csv, re, openpyxl

file_storage = []
excel_storage = []
excel_columns = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z']
rowData = []


os.chdir('C:\\Users\\testing\\Desktop\\python\\chapter_14\\practice_project_files')

print(str(os.getcwd()))


for filenames in os.listdir():
    file_storage.append(filenames)

for x in range(len(file_storage)):
    file_type = re.compile('.*\.xlsx')
    files_found = file_type.search(file_storage[x])
    if files_found:
        excel_storage.append(file_storage[x])


for y in range(len(excel_storage)):
    wb = openpyxl.load_workbook(excel_storage[y])
    #print(wb.sheetnames)
    #print(str(len(wb.sheetnames)))
    for z in range(len(wb.sheetnames)):
        wb.active = z
        sheet = wb.active
        with open('test.csv', 'wb') as f:
            csvWriter = csv.writer(f)
            for r in range(1,sheet.max_row + 1):
                for colNum in range(0, sheet.max_column ):
                    stuff = sheet[excel_columns[colNum] + str(r)].value
                    rowData.append(stuff)
                csvWriter.writerow(r)


    wb.close()

The part of my code that is giving me problems in particular:

rowData.append(stuff)

csvWriter.writerow(r)

The error message I am getting:

iterable expected, not int

What am I doing wrong?

Answer 1

You are trying to write the row number as an entire row in the CSV file. Replace csvWriter.writerow(r) with csvWriter.writerow(rowData), and also add rowData = [] before the line with for colNum in ....