Python - Extracting excel docs from file, need help reading data

Question

So I've been working on a project extracting .xlsx docs from a file in attempt to compile the data into one worksheet.

So for I've managed a loop to pull the documents but now I'm stuck trying to read the documents.

Python 2.7

As follows, my script and response in the shell

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

#create directory from which to pull the files
rootdir = '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 = workbook.add_worksheet()
for file in filelist:
    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()

The error I'm receiving when I run the program

C:\Users\username\Desktop\Mults\doc1.xlsx
C:\Users\username\Desktop\Mults\doc2.xlsx
C:\Users\username\Desktop\Mults\doc3.xlsx
C:\Users\username\Desktop\Mults\doc4.xlsx

Traceback (most recent call last):
  File "C:\Users\username\Desktop\Work\Python\excel practice\xlsx - loops files 
- 09204.py", line 20, in <module>
wb = xlsxwriter.workbook('merged.xls')
TypeError: 'module' object is not callable

I know I'm missing a step somewhere to connect the data.

I've practiced with xlsxwriter in other scripts and the module worked fine. For some reason won't recognize it here.

Also, as suggested I've tried xlwt, but experienced trouble importing the module into my shell even though it is installed accordingly.

Any tips will be helpful!

Thanks!

Not sure why you keep rolling your question back and forth. Could you explain? — Andrew Barber
– Andrew Barber, Commented Sep 29, 2014 at 20:46

Padraic Cunningham · Accepted Answer · 2014-09-29 19:45:34Z

4

It is a capital W in WorkBook

 wb = xlsxwriter.Workbook('merged.xls')

You should also use / slashes or r raw string in paths in windows:

r'C:\Users\username\Desktop\Mults'

'C:/Users/username/Desktop/Mults'

ws = workbook.add_worksheet() will also cause an error as workbook is not defined anywhere.

I think you mean wb.add_worksheet()

answered Sep 29, 2014 at 19:45

Padraic Cunningham

181k30 gold badges264 silver badges327 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mike V. Over a year ago

Would you say that I'm heading the right direction? I seem to have trouble reading multiple docs. Tried Pandas but receiving the same error

Padraic Cunningham Over a year ago

If you have a new error you should ask a new question after you have tried debugging explaining what you have tried and exactly what the problem is, editing your original question with a new question is not the way to go. Your error is pretty self explanatory, in your current directory you do not have a file 'doc1.xlsx'

Collectives™ on Stack Overflow

Python - Extracting excel docs from file, need help reading data

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related