Python/Excel - IOError: [Errno 2] No such file or

Question

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

Receiving a IOError despite that the files exist

Program is as follows

#-------------- 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:
    print 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:
    r = xlrd.open_workbook(file)
    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_rosw()>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

doc1.xlsx
doc2.xlsx
doc3.xlsx
doc4.xlsx

Traceback (most recent call last):
  File "C:\Users\username\Desktop\Work\Python\excel practice\xlsx - loops files - 09204.py", line 23, in <module>
    r = xlrd.open_workbook(file)
  File "C:\Python27\lib\site-packages\xlrd\__init__.py", line 394, in open_workbook
    f = open(filename, "rb")
IOError: [Errno 2] No such file or directory: 'doc1.xlsx'

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!!

Answer 1

You are opening the plain filename without the path; you are ignoring the directory component.

Don't just print the os.path.join() result, actually use it:

filename = os.path.join(subdir, file) 
r = xlrd.open_workbook(filename)

Answer 2

For the first problem...

Instead of:

r = xlrd.open_workbook(file)

Use:

r = xlrd.open_workbook(os.path.join(subdir,file))

For the TypeError: Instead of:

for sheet in r:
    if sheet.number_of_rows()>0:
        count += 1

Use:

for nsheet in r.sheet_names() #you need a list of sheet names to loop throug
    sheet = r.sheet_by_name(nsheet) #then you create a sheet object with each name in the list
    if sheet.nrows>0: #use the property nrows of the sheet object to count the number of rows
        count += 1

Do the same for the second for loop.