Python/Excel - TypeError: 'Book' object is not iterable

Question

Attempting to extract .xlsx docs from a file and compile the data into a single worksheet.

Receiving TypeError: 'Book' object is not iterable, but I don't have an object titled "Book".

So, I guess my confusion is understanding how to make the workbook iterable

Program is as follows

Python 2.7

#-------------- loop that pulls in files from folder--------------
import os

#create directory from which to pull the files
rootdir = r'C:\Users\username\Desktop\Mults'

for subdir, dir, files in os.walk(rootdir):
for file in files:
    open(os.path.join(subdir, file))
#----------------------merge work books-----------------------

import xlrd
import xlsxwriter


wb = xlsxwriter.Workbook('merged.xls')
ws = wb.add_worksheet()
for file in files:
    filename = os.path.join(subdir, file)
    r = xlrd.open_workbook(filename)
    head, tail = os.path.split(file)
    count = 0
    for sheet in r:
        if sheet.number_of_rows()>0:
            count += 1
    for sheet in r:
        if sheet.number_of_rows()>0:
            if count == 1:
                sheet_name = tail
            else:
                sheet_name = "%s_%s" (tail, sheet.name)
            new_sheet = wb.create_sheet(sheet_name)
            new_sheet.write_reader(sheet)
            new_sheet.close()
wb.close()

Return error as follows

Traceback (most recent call last):
  File "C:\Users\username\Desktop\Work\Python\excel practice\xlsx - loops files - 09204.py", line 27, in <module>
    for sheet in r:
TypeError: 'Book' object is not iterable

Any suggestions or changes?

Also, any advice if I'm heading in the right direction?

I'm new to the python world, so any advice will be much appreciated!

Thank you!!

`r` is an object of type `Book` (representing a Workbook), and you're trying to iterate over it, but it does not support iteration. Googling the exact error message would have lead you to multiple posts on SO and other sites which describe exactly why this error occurs - please do some more research next time. According to [the docs](https://secure.simplistix.co.uk/svn/xlrd/trunk/xlrd/doc/xlrd.html?p=4966#__init__.Book.sheets-method), you could iterate over `r.sheets()` instead. — l4mpi, Sep 30 '14 at 15:35
@14mpi Thank you for the quick response!I did do research and understood the concept but didn't understand a solution to the issue. I appreciate your time — Mike V., Sep 30 '14 at 15:40

score 2 · Answer 1 · answered Sep 30 '14 at 15:36

The problem is that you are trying to iterate over the opened by xlrd workbook, which cannot be done.

There are multiple ways to iterate over the worksheets in the workbook, here is one of them:

workbook = xlrd.open_workbook(filename)
for worksheet in workbook.sheets():
    print worksheet.nrows

Option 2 (using sheet_names()):

workbook = xlrd.open_workbook(filename)
for name in workbook.sheet_names():
    worksheet = workbook.sheet_by_name(name)
    print worksheet.nrows

Option 3 (using nsheets):

workbook = xlrd.open_workbook(filename)
for index in xrange(0, workbook.nsheets):
    worksheet = workbook.sheet_by_index(index)
    print worksheet.nrows

FYI, nrows would give you a number of rows in a sheet.

Python/Excel - TypeError: 'Book' object is not iterable

1 Answers1