I am trying to read dates/time off an excel sheet using Python, but I only want to read in the time so I can perform computations on it. For example if I have a date in the format: 3/11/2003 4:03:00 AM
on my excel sheet how can I read in just the 4:03:00 in Python? I ultimately want to be able to subtract hours, mins, or seconds from the read in time.
Add a comment
|
1 Answer
The best option would be to convert the date to datetime using xldate_as_tuple. Assuming you have an test.xls file with 3/11/2003 4:03:00 AM in the A1 cell:
from datetime import datetime, timedelta
import xlrd
book = xlrd.open_workbook(filename='test.xls')
sheet = book.sheet_by_name('Sheet1')
date = sheet.cell_value(0, 0)
datetime_value = datetime(*xlrd.xldate_as_tuple(date, 0))
print datetime_value # prints 2003-11-03 04:03:00
print datetime_value.time() # 04:03:00
print datetime_value - timedelta(hours=1) # prints 2003-11-03 03:03:00
Hope that helps.
3 Comments
terence vaughn
Ok so I have read in the date and time from the excel sheet and stored in a variable called date, but when
print date in python it outputs 37691.16875. Im not sure what format this is but after doing some searching this is how dates from excel sheets are read into python. SO I guess my real question is how to convert 37691.16875 in to 3/11/2003 4:03:00 AMterence vaughn
Ive tried that as well but I get an error:
Traceback (most recent call last): File "Data Cleaner.py", line 104, in <module> Rule_2(sheet) File "Data Cleaner.py", line 31, in Rule_2 date = xlrd.xldate_as_tuple(c_val, book.datemode) File "/Library/Python/2.7/site-packages/xlrd/xldate.py", line 61, in xldate_as_tuple xldays = int(xldate) ValueError: invalid literal for int() with base 10: '' any ideas?terence vaughn
You were right. my problem was that there were some blank fields in some of the cells and thats why my program was crashing. Thanks for the help!!!