Date Time to Date from Excel

Anvil Q&A
1

Hello

I am importing a excel file with a date column to data tables. However, Anvil automatically imports this column as a date time object, while I need only a date object. Is there anyway to do this conversion?

Regards
Rajesh

2

When you define the table in Anvil’s IDE, you can define the column as a “Date” column, instead of a “Date and Time” column:
image
As a Date column, the value in that column can only be a Date object, or None.

3

Hi

Thank you. I have set a column to date object but I import the file, Anvil throws an error that I am trying to write a date time object into a date column?

anvil.tables.TableError: Column ‘jobDate’ is a date - cannot set it to a datetime

Regards
Rajesh

4

Could you show us the function (or script) where that error is occurring?

Or have a look at the docs for this Python standard library function.

5

Another option is to leave the column as datetime, but only show the date part in the data grid. It just depends on whether you might want the time portion for use somewhere else or not.

To do that, drag a label onto the column in the row template and bind the text field to

self.item['created_on'].strftime("%b %d %Y")

Adjust the format to suit what you want displayed, and change the field name to your column name.

1 Like
6

Thanks @p.colbert

I have the below function in my server module

 @anvil.server.callable   
def store_jobhistory_data(file):
  with anvil.media.TempFile(file) as file_name:
    if file.content_type == 'text/csv':
     df = pd.read_csv(file_name)
     
    else:
     df = pd.read_excel(file_name)
        
    df = df.fillna(0)
    
    
    for d in df.to_dict(orient="records"):
      # d is now a dict of {columnname -> value} for this row
      # We use Python's **kwargs syntax to pass the whole dict as
      # keyword arguments
      app_tables.jobhistorymaster.add_row(**d)

And then I upload an excel file that has a date column amongst others. Anvil autocreates a datetime column and the upload works fine.

I have no use for time and would prefer just date. So, I created a new table with jobDate as a Date column and reuploaded the file. When I do that, I get the below error.

anvil.tables.TableError: Column ‘jobDate’ is a date - cannot set it to a datetime

I now probably have to use datetime to write out a separate date column. Is there a more elegant way to do this?

Regards
Rajesh

7

Thanks @jshaffstall This solution worked beautifully. I am not sure how this drains resources since every call to the server now I am getting more info than I need. Probably its not a big deal, but obviously I know very little.

Other solution was to add the below two lines to my code after reading the excel file


df['jobDate'] = df['job date'].dt.date
del df['job date']

Thanks both for your time.

1 Like