Python: xlrd outstanding dates from floats

Question

I wanted to import a file containing text, numbers and dates using xlrd in Python.

I tried something like:

if "/" in worksheet.cell_value: do_this else: do_that 

But that was useless, as I found that dates are stored as floating, not strings. To convert them to datetime type, I did:

 try: get_row = str(datetime.datetime(*xlrd.xldate_as_tuple(worksheet.cell_value(i, col - 1), workbook.datemode))) except: get_row = unicode(worksheet.cell_value(i, col - 1)) 

I have an exception when the cell contains text. Now I want to get numbers as numbers and dates as dates, because now all numbers are converted to dates.

Any ideas?

Answer 1

Well, no matter, I found a solution, and here it is!

 try: cell = worksheet.cell(row - 1, i) if cell.ctype == xlrd.XL_CELL_DATE: date = datetime.datetime(1899, 12, 30) get_ = datetime.timedelta(int(worksheet.cell_value(row - 1, i))) get_col2 = str(date + get_)[:10] d = datetime.datetime.strptime(get_col2, '%Y-%m-%d') get_col = d.strftime('%d-%m-%Y') else: get_col = unicode(int(worksheet.cell_value(row - 1, i))) except: get_col = unicode(worksheet.cell_value(row - 1, i)) 

A little explanation: it turns out that with xlrd you can check the type of cell and check whether it has a date or not. In addition, Excel seems like a weird way to save time. It saves them as floats (the left part for several days, the correct part for several hours), and then it takes a certain date (1899, 12, 30, it seems to work fine) and adds days and hours from the float to create a date. So, to create the date that I wanted, I just added them and saved only the first 10 letters ([: 10]) to get rid of the clock (00.00.00 or something ...). I also changed the order of days_months-years, because in Greece we use a different order. Finally, this code also checks to see if it can convert a number to an integer (I don't want any floats to appear in my program ...), and if all else fails, it just uses the cell as it is (in cases rows in cells ...). I hope you find this useful, I think there are other threads that say this is impossible or something like that.

Answer 2

I think you could make it a lot easier using more tools available in xlrd:

 cell_type = worksheet.cell_type(row - 1, i) cell_value = worksheet.cell_value(row - 1, i) if cell_type == xlrd.XL_CELL_DATE: # Returns a tuple. dt_tuple = xlrd.xldate_as_tuple(cell_value, workbook.datemode) # Create datetime object from this tuple. get_col = datetime.datetime( dt_tuple[0], dt_tuple[1], dt_tuple[2], dt_tuple[3], dt_tuple[4], dt_tuple[5] ) elif cell_type == xlrd.XL_CELL_NUMBER: get_col = int(cell_value) else: get_col = unicode(cell_value)