Reputation: 2552
I understand this has been asked a few times, but I can't quite figure out my issue even though I have tried many of the given answers. I am trying to read an Excel cell that is represented as a Date, but when it is imported into my program with Python, it is represented as a float.
The fourth column in my excel spreadsheet (i.e. index=3) contains all the dates that I am trying to convert from float to string. I am simply adding all the values to a list to do something with the entire row, before clearing it and moving on to the next row.
I have based my code below from this answer: https://stackoverflow.com/a/13962976/3480297
This is the sample data that I am working with (file):
import xlrd
workbook = xlrd.open_workbook(file)
worksheet = workbook.sheet_by_name('Sheet1')
num_rows = worksheet.nrows
num_cols = worksheet.ncols
values = []
i = 0
j = 0
while i < num_rows:
while j < num_cols:
if j == 3:
values.append(xlrd.xldate_as_tuple(str(worksheet.cell_value(i, j)), 0))
else:
values.append(worksheet.cell_value(i, j))
j += 1
j = 0
i += 1
values = []
However, my above code gives me the below error at print(xlrd.xldate_as_tuple(str(worksheet.cell_value(i, j)), 0))
and I cannot figure out why.
TypeError: '<' not supported between instances of 'str' and 'float'
EDIT:
After debugging, the code is throwing an error where worksheet.cell_value(i, j) = 43588.0
EDIT2:
I removed the str(...) cast around the Date to include: values.append(xlrd.xldate_as_tuple(worksheet.cell_value(i, j)), workbook.datemode)
but this is throwing an error:
TypeError: xldate_as_tuple() missing 1 required positional argument: 'datemode'
Upvotes: 1
Views: 1716
Reputation: 208
i'm highly recommend to use pandas:
import pandas as pd
def read_db():
data_list = []
try:
df = pd.read_excel(REVIEW_DB_URL, sheet_name='Sheet1') # open the db
for i in range(len(df)):
data_list.append(df['name'][i], df['version'][i], df['datetime'][i],
df['notes'][i])
return data_list
except Exception as e:
print(e)
Upvotes: 1
Reputation: 45762
I don't think you were supposed to cast the date as a string. Note I made a couple of other changes:
i
, and j
)xldate_as_datetime
makes more sense than xldate_as_tuple
note if you want the date displayed in a specific format try strftime
import xlrd
workbook = xlrd.open_workbook(file)
worksheet = workbook.sheet_by_name('Sheet1')
num_rows = worksheet.nrows
num_cols = worksheet.ncols
values = []
for row in range(1, num_rows):
row_values = []
for col in range(num_cols):
if col == 3:
datetime_ = xldate.xldate_as_datetime(worksheet.cell_value(row, col), workbook.datemode)
row_values.append(datetime_)
else:
row_values.append(worksheet.cell_value(row, col))
values.append(row_values)
Upvotes: 2