CODEWITHSUNDEEP
pythonexcel
Adam
Adam

Reputation: 2552

Read date from excel as string Python

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):

enter image description here

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

Answers (2)

yehezkel horoviz
yehezkel horoviz

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

Dan
Dan

Reputation: 45762

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)

Upvotes: 2

Related Questions