1

I have written a script that reads from excel workbooks and writes new workbooks. Each row is a separate object, and one of the columns is a date. I have written the date as a NamedStyle using datetime to get what I think is the correct format:

    date_style = NamedStyle(name='datetime', number_format='YYYY-MM-DD')
    for row in range(2,ws_kont.max_row+1):
        ws_kont.cell(row = row, column = 4).style = date_style

The problem is that i need to import this excel workbook to an ancient database who for some reason dont accept a date-formating, only text like this "yyyy-dd-mm". I'm having trouble rewriting these cells as text.

I have tried using the =TEXT formula, but that wont work since you cant use the cell itself to calculate the result unless i duplicate the column for referencing in the formula:

name = str(ws_teg.cell(row = row, column = 4).coordinate)
date_f = "yyyy-mm-dd"
ws_kont[name] = "=TEXT(%s,%s)" % (name, date_f)

I need to do this a bunch of places in a couple of scripts, so I'm wondering if there is a simpler way to do this?

PS. I'm just a archaeologist trying to automate some tasks in my workday by dabbling in some simple code, please go easy on me if I seem a bit slow.

jankhe
  • 13
  • 1
  • 3

2 Answers2

2

Found another article that worked out well with minmal code:

writer = pd.ExcelWriter('Sample_Master_Data_edited.xlsx', engine='xlsxwriter', 
                        date_format='mm/dd/yyyy', datetime_format='mm/dd/yyyy')

Reference

h3t1
  • 1,126
  • 2
  • 18
  • 29
Snitzel
  • 21
  • 2
1

Most likely, it won't be enough to change the format of your date - you'll have to store the date as a string instead of a datetime object.

Loop over the column and format the dates with datetime.strftime:

for row in range(1, ws_kont.max_row+1):
    cell = ws_kont.cell(row = row, column = 4)
    cell.value = cell.value.strftime('%Y-%m-%d')
Aran-Fey
  • 39,665
  • 11
  • 104
  • 149