Problem while reading date in date format from excel sheet.
Mayur
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.
Adrian Klaver
> Hi All,
>
> 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.
>
See here:
http://www.lexicon.net/sjmachin/xlrd.html
Section:
Dates in Excel spreadsheets
Also function:
xldate_as_tuple(xldate, datemode)
--
Adrian Klaver
akl...@comcast.net
John Machin
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
See also the tutorial that you'll find at http://www.python-excel.org
HTH,
John
Георги Георгиев
def dumbmsdate(dumbmsstring):
msdateoff = 693594
if dumbmsstring == '':
return ''
else:
return
datetime.date.fromordinal(msdateoff+int(dumbmsstring)).strftime('%d.%m.%Y')
which is probably not very good programing but works for me :-)
i found the msdateoff variable using scientific approach (i.e. trial and
error ;-) )
hope this helps,
georgi
Georgi Georgiev
DIRECT SERVICES Ltd.
tel: +359-2-9609754
+359-2-9609737
fax: +359-2-9609738
www.directservices.bg
John Machin
> i use this function
>
> def dumbmsdate(dumbmsstring):
string? So you get the float that xlrd delivers and convert it to a
string ...
> msdateoff = 693594
> if dumbmsstring == '':
> return ''
> else:
> return
> datetime.date.fromordinal(msdateoff+int(dumbmsstring)).strftime('%d.%m.%Y')
and then convert it to an int ...
> 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.
Георги Георгиев
John Machin wrote:
On 22/10/2009 6:33 PM, Георги Георгиев wrote:i use this function def dumbmsdate(dumbmsstring):string? So you get the float that xlrd delivers and convert it to a string ...
so yeah it should be for example
def dumbmsdate(dumbmsfloat):
:-)
msdateoff = 693594 if dumbmsstring == '': return '' else: return datetime.date.fromordinal(msdateoff+int(dumbmsstring)).strftime('%d.%m.%Y')and then convert it to an int ...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.
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')
balname = 'cards_izvl_bal.XLS'
balbook = xlrd.open_workbook(balname)
balsheet = balbook.sheet_by_index(0)
if balsheet.cell_type(rowx=1, colx=1) == 3:
if balbook.datemode == 0:
msdateoff = 693594
elif balbook.datemode == 1:
msdateoff = 693594-1462
else:
raise ValueError('Unknown datemode ' + str(balbook.datemode) + \
' in workbook ' + balname)
print 'date'
print dumbmsdate(balsheet.cell_value(rowx=1, colx=1), msdateoff)
else:
print 'something else'
print balsheet.cell_value(rowx=1, colx=1)
and even better
2)
#!/usr/bin/python
# -*- coding: utf-8 -*-
import datetime
import xlrd
balname = 'cards_izvl_bal.XLS'
balbook = xlrd.open_workbook(balname)
balsheet = balbook.sheet_by_index(0)
if balsheet.cell_type(rowx=1, colx=1) == 3:
print 'date'
print datetime.datetime(* (xlrd.xldate_as_tuple(balsheet.cell_value(rowx=1, colx=1), \
balbook.datemode))).strftime('%d.%m.%Y')
else:
print 'something else'
print balsheet.cell_value(rowx=1, colx=1)
John, thank you again :-)
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
John Machin
>
> John Machin wrote:
>> On 22/10/2009 6:33 PM, Георги Георгиев wrote:
>>
>>> i use this function
>>>
>>> def dumbmsdate(dumbmsstring):
>>>
>>
>> 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
Bad attitude, dude. Ignorance-induced bliss is ephemeral :-)
> - 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):
> :-)
We are in agreement on at least the first 4 letters of your naming
convention ;-)
>>> msdateoff = 693594
>>> if dumbmsstring == '':
>>> return ''
>>> else:
>>> return
>>> datetime.date.fromordinal(msdateoff+int(dumbmsstring)).strftime('%d.%m.%Y')
>>>
>>
>> and then convert it to an int ...
>>
>>
>>> 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.
> return ''
> else:
> return
> datetime.date.fromordinal(msdateoff+int(dumbmssomething)).strftime('%d.%m.%Y')
>
>
>
> balname = 'cards_izvl_bal.XLS'
> balbook = xlrd.open_workbook(balname)
> balsheet = balbook.sheet_by_index(0)
>
> 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.
> msdateoff = 693594
> elif balbook.datemode == 1:
> msdateoff = 693594-1462
Ummm ... perhaps you need a + and some test cases:
With datemode 0 (1900 system), xldate 61 is Gregorian 1900-03-01.
With datemode 1 (1904 system), xldate 1 is Gregorian 1904-01-02.
>>> datetime.date.fromordinal(693594 + 61)
datetime.date(1900, 3, 1)
>>> datetime.date.fromordinal(693594 - 1462 + 1)
datetime.date(1895, 12, 30) # incorrect
>>> datetime.date.fromordinal(693594 + 1462 + 1)
datetime.date(1904, 1, 2) # correct
>>>
> else:
> raise ValueError('Unknown datemode ' + str(balbook.datemode) + \
> ' in workbook ' + balname)
Good style is in the eye of the beholder; here's an alternative:
if book.datemode not in (0, 1):
raise SeriousBugInXlrd(.......)
msdateoff = 693594 + 1462 * book.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
I'm sticking with (a).
Cheers,
John