python, Help with calculating Average and std for excel file

[g_amanu]

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

[TrustyTony]

file has wrong extension and is not in excel format, I guess, but is plain text file.

[g_amanu]

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?

[TrustyTony]

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-

[g_amanu]

juskkis...can u please explain more..

[Gribouillis]

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.