im trying to calculate the average and std for x, y, and z column for about 50 excel files that i saved in a folder. each excel files has x values in the first column, y in the second, and z in the third column. im using this script, but it kept giving me this error...here is the script and the error:

import xlrd
import numpy
import os


path = "E:\\hello\\files"
dirList=os.listdir(path)
f = open('E:\\hello\\try.xls', 'w')
f.write('Patient_ID, Xavg, xstd, yavg, ystd, zavg, ystd')
f.write("\n")

##print dirList
##i = 0
Col_values=[]
for file in dirList:
    fullpath = os.path.join(path,file)
    if os.path.isfile(fullpath) == 1:
        wb = xlrd.open_workbook(fullpath)
        wb.sheet_names()
        sh = wb.sheet_by_index(0)
        f.write(str(file))
        f.write(", ")
        for i in range(0,3):
            for j in range(sh.nrows):
                Col_values.append(sh.cell(j,i).value)
            a = numpy.average(Col_values)
            b = numpy.std(Col_values)
            f.write(str(a))
            f.write(", ")
            f.write(str(b))
            f.write(", ")
        f.write("\n")

f.close()

and the error is:

Traceback (most recent call last):
  File "C:\Amanu\Amanu_summer_2011_Scripts\y_std_and_average.py", line 18, in <module>
    wb = xlrd.open_workbook(fullpath)
  File "C:\Python26\Lib\site-packages\xlrd\__init__.py", line 429, in open_workbook
    biff_version = bk.getbof(XL_WORKBOOK_GLOBALS)
  File "C:\Python26\Lib\site-packages\xlrd\__init__.py", line 1545, in getbof
    bof_error('Expected BOF record; found %r' % self.mem[savpos:savpos+8])
  File "C:\Python26\Lib\site-packages\xlrd\__init__.py", line 1539, in bof_error
    raise XLRDError('Unsupported format, or corrupt file: ' + msg)
XLRDError: Unsupported format, or corrupt file: Expected BOF record; found '-0.02761'

so i dont get what the probelm is with having that number..

i checked the 'fullpath' and it prints out the the correct path, and i also checked if the files are saved as 'xls'. but it still keep giving me the Error message. Do you know any other way to find the avg and std?

You only need to collect the sums which are needed for th e formulas of average and standard deviation from each file and calculate grand total numbers from the total of filewise values.

I wonder, if you can point to an excelfile with Python. Try to put it into a plain txt-file instead. Excel wil do all those calculations you want to do with python

-Jukkis-

To see if your files are in excel format, try to open them with excel or openoffice. The extension means nothing.Did you write these files yourself with one of your programs ? Then post the code used to create them.

This article has been dead for over six months. Start a new discussion instead.