Read date from excel as string Python

Question

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'

Try print(worksheet.cell_value(i, j)) in order to help you debug, just to make sure you're not off by one column. Also you should use for loops here instead of while and your code will be a lot more readable if you replace i with row and j with column. — Dan
– Dan, Commented Mar 3, 2020 at 12:01
Try removing the str call, i.e. print(xlrd.xldate_as_tuple(worksheet.cell_value(i, j), 0)) — Foxocube
– Foxocube, Commented Mar 3, 2020 at 12:01
@PedroLobito thanks for spotting that. I missed that out in my copy and paste. I've included it — Adam
– Adam, Commented Mar 3, 2020 at 12:03
i know thats not the question. but maybe you want to use pandas, which would probably make the problem much easier — gbruenjes
– gbruenjes, Commented Mar 3, 2020 at 12:04
@Dan I've included print(...) as per your suggestion but I still don't see a reason why this error would be thrown. — Adam
– Adam, Commented Mar 3, 2020 at 12:04

Dan · Accepted Answer · 2020-03-03 12:31:29Z

2

I don't think you were supposed to cast the date as a string. Note I made a couple of other changes:

used for loops instead of while loops
please don't use single letter var names (like 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)

edited Mar 3, 2020 at 12:31

answered Mar 3, 2020 at 12:20

Dan

45.8k20 gold badges98 silver badges169 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Adam Over a year ago

Thank you Dan! The problem was with casting the date to string and wrong parentheses location. Is there a way to make the date into dd/mm/yyyy format rather than (yyyy, mm, dd, hh, mm, ss) as it currently is? For example, one of the outputs is (2019, 9, 10, 0, 0, 0)

Dan Over a year ago

Yes, the answer is in the link you posted i.e. datetime.datetime(* will convert from the tuple to a datetime object. If you then want a string with your format use strftime. I'll edit to parse the tuple to a datetime object, but I'll leave converting to a string to you.

Dan Over a year ago

Just saw this: stackoverflow.com/a/28220798/1011724 it makes much more sense. I'm going to edit again. Please test it and let me know if there's a mistake.

Adam Over a year ago

Ok will do! I'll test that out in just a bit. Thanks again!

Adam Over a year ago

You're right! What I didn't put (and therefore didn't mention) was that I'm doing some extra stuff with each entire row (and therefore the header one as well) which I'm disregarding in some calculations by doing "if row == 0". So long story short, you're answer still stands as correct.

|

Tamil Selvan S · Accepted Answer · 2020-08-05 11:01:33Z

1

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)

edited Aug 5, 2020 at 11:01

Tamil Selvan S

72612 silver badges30 bronze badges

answered Mar 3, 2020 at 12:20

yehezkel horoviz

2083 silver badges10 bronze badges

2 Comments

Dan Over a year ago

except Exception don't catch raw Exceptions. Also, if you're using pandas, then might as well just leave it as a df, no need to convert to a list.

yehezkel horoviz Over a year ago

looks good, i saw you're using datatime module, pay attention to other dated you are working with , read about the functionality of the module, it will keep you out of trouble.

Collectives™ on Stack Overflow

Read date from excel as string Python

2 Answers 2

7 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related