I have stored date of birth in the below format in excel file.
DateOfBirth
10/15/1980
While reading this value from excel into the application the value is
read as 29509.0
I need a string to be return in the format mm/dd/yyyy.
following is the snippet of my code
def read_from_excel(self,filepath,sheetname,columnname,rowno):
try:
book=xlrd.open_workbook(filepath,on_demand=True)
except BaseException:
raise ValueError("Unable to open File %s"%filepath)
try :
sheetobj=book.sheet_by_name(sheetname)
except BaseException:
raise ValueError("SheetName %s not found"%sheetname)
colx=sheetobj.ncols
rowno=int(rowno)
temp=0
for i in range(0,colx):
i=int(i)
if sheetobj.cell_value(0, i) == columnname:
temp=1
try:
cellvalue=sheetobj.cell_value(rowno, i)
if cellvalue==None or cellvalue=="":
raise ValueError("RowNo %i of column %s is
Empty"%(rowno,columnname))
else:
return cellvalue
except BaseException:
raise ValueError("RowNo %i of column %s is
Empty"%(rowno,columnname))
else:
temp=0
if temp==0:
raise ValueError("%s column name not found"%columnname)
Please let me know any suggestion for the mentioned problem ASAP.
> Also function: > xldate_as_tuple(xldate, datemode)
Excellent advice, Adrian.
Mayur, the xlrd documentation is included in the various downloads ... e.g. on Windows, with a default Python 2.6 installation and xlrd installed using the Windows installer, the doc files are in C:\Python26\Lib\site-packages\xlrd\doc
> which is probably not very good programing but works for me :-)
1.0 - epsilon <= probability <= 1.0 :-)
It won't work if the datemode is 1 (i.e. Mac-style 1904-based dates), and it is likely to fail in the worst possible way: silently, with the result about 4 years out of whack.
string? So you get the float that xlrd delivers and convert it to a
string ...
well i don't knew (or cared) what data type comes from xlrd - i just
.cell_value(rowx=transaction, colx=1), pass it to dumbmsdate and it
gets int-ed there
so yeah it should be for example
def dumbmsdate(dumbmsfloat): :-)
which is probably not very good programing but works for me :-)
1.0 - epsilon <= probability <= 1.0 :-)
It won't work if the datemode is 1 (i.e. Mac-style 1904-based dates),
and it is likely to fail in the worst possible way: silently, with the
result about 4 years out of whack.
yes i never got mac-style excel file, but it could happen any time -
after some tinkering around i have two solutions
1)
#!/usr/bin/python
# -*- coding: utf-8 -*-
import datetime
import xlrd
def dumbmsdate(dumbmssomething, msdateoff=692863): # a compromise
between
# 693594 and
693594-1462
if dumbmssomething == '': # just in case - can cell_type 3 be empty?
return ''
else:
return
datetime.date.fromordinal(msdateoff+int(dumbmssomething)).strftime('%d.%m.%Y')
just out of curiosity: why do i need to pass datemode to
xldate_as_tuple? i have the sheet already so xldate_as_tuple can do
something like sheet.parent.datemode
>>> which is probably not very good programing but works for me :-)
>> 1.0 - epsilon <= probability <= 1.0 :-)
>> It won't work if the datemode is 1 (i.e. Mac-style 1904-based dates), >> and it is likely to fail in the worst possible way: silently, with the >> result about 4 years out of whack.
> yes i never got mac-style excel file, but it could happen any time - > after some tinkering around i have two solutions
> 1)
> #!/usr/bin/python > # -*- coding: utf-8 -*-
> import datetime > import xlrd
> def dumbmsdate(dumbmssomething, msdateoff=692863): # a compromise between > # 693594 and 693594-1462 > if dumbmssomething == '': # just in case - can cell_type 3 be empty?
It's a float, and AFAIK not a NaN. Certainly str(such_a_float) != ''. Under what circumstances would you describe such a float as "empty"?
I don't suppose you'd be interested in using XL_CELL_DATE instead of 3.
> if balsheet.cell_type(rowx=1, colx=1) == 3: > if balbook.datemode == 0:
Call me crazy, but I'd hide all of that magic number stuff away in the function (with a datemode arg instead of an offset), and I'd make the function return a datetime.date object -- having it do strftime with a hard-coded format seems unduly restrictive.
> just out of curiosity: why do i need to pass datemode to > xldate_as_tuple? i have the sheet already so xldate_as_tuple can do > something like sheet.parent.datemode
And you have the book already also, so why not go straight for it?
Note that xldate_as_tuple is a stand-alone function; it's not a Sheet method.
Three choices: (a) 2nd arg is datemode and the function uses it directly (b) 2nd arg is sheet and the function uses sheet.book.datemode (c) 2nd arg is book and the function uses book.datemode
|
