How to format datetime values in csv file/column in python?

Question

I have a csv file which contains several lines and in particular, column sending time. I wish to change the timedate formats from DD/MM/YYYYHH:MM:SS.DDDD to YYYYMMDD-HH:MM:SS for all values in column SendingTime.

CSV Example:

MsgType,CompID,SendingTime    
AR ,SDF,16/08/2021 09:13:13.09934

I have found a code snippet on StackOverflow, and I'm trying the following to change the datetime format, however, to no avail and the below error. Any help would be most appreciated?

import csv
import re
from datetime import datetime
 
lines = []
# open file as read-only
with open('datetimeissue.csv', "r", newline='') as data:
    reader = csv.reader(data)
    # go over all of its rows, and the row's items and change
    # items that match the date format
    for row in reader:
        for i, string in enumerate(row):
            if re.match(r"\d+\/\d+\/\d+ \d+\:\d+\:\d+", string):
                datetimeobject = datetime.strptime(string, '%d/%m/%Y %h:%m:%s')
                new_string = datetimeobject.strftime('%Y-%m-%d-%h:%m:%s')
                row[i] = new_string
                print("Replaced", string, "with", new_string)
        # save edited, and originally correct ones to new list
        new_row = row
        lines.append(new_row)
 
# write new rows by overwriting original file
with open('mynewoverwritten.csv', "w", newline='') as data:
    writer = csv.writer(data)
    writer.writerows(lines)

Error Extract

Traceback (most recent call last):
  File "time.py", line 14, in <module>
    datetimeobject = datetime.strptime(string, '%d/%m/%Y %h:%m:%s')
  File "/usr/lib64/python3.6/_strptime.py", line 565, in _strptime_datetime
    tt, fraction = _strptime(data_string, format)
  File "/usr/lib64/python3.6/_strptime.py", line 354, in _strptime
    (bad_directive, format)) from None
ValueError: 'h' is a bad directive in format '%d/%m/%Y %h:%m:%s'

Instead of using regular expressions use one of the many CSV libraries to read the data and save it again with the new format. Most libraries will actually parse the date as well, allowing you to just specify the new format. YYYYMMDD-HH:MM:SS is a very unusual format that won't be recognized by any application. The standard format for dates is ISO8601 - YYYY-MM-DD HH:mm:ss or YYYY-MM-DDTHH:mm:ss — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Aug 25, 2021 at 16:06
Hi Kanavos, thats one of the issues - I cannot install "third party" libraries on our RH systems. Otherwise, I'm literally seeing Pandas everywhere for such. — test1 1
– test1 1, Commented Aug 25, 2021 at 23:52
FYI if you just want to edit the csv and change the datetime format, there's a Notepad++ plug-in that can do that github.com/BdR76/CSVLint — BdR
– BdR, Commented Aug 26, 2021 at 14:01
thank you for the info - however, I have to automate this process without plugins or third party tools. The given answer is great because I even use it for lists as opposed to CSV file. — test1 1
– test1 1, Commented Aug 28, 2021 at 9:03
@Panagiotis yes the time format is strange but this is the format send to clients/clearing counter parties. This is a extract from the FIX message so the standard is used between customers I guess. — test1 1
– test1 1, Commented Aug 28, 2021 at 9:04

Mark Tolonen · Accepted Answer · 2021-08-25 16:32:07Z

2

%H:%M:%S (capitals) are the format strings for time. re seems unneeded if you know the time column:

import csv
from datetime import datetime
 
with open('input.csv', "r", newline='') as inf, \
     open('output.csv', "w", newline='') as outf:
    reader = csv.reader(inf)
    writer = csv.writer(outf)
    writer.writerow(next(reader)) # copy header
    for row in reader:
        timestamp = datetime.strptime(row[2], '%d/%m/%Y %H:%M:%S.%f')
        row[2] = timestamp.strftime('%Y-%m-%d %H:%M:%S')
        writer.writerow(row)

answered Aug 25, 2021 at 16:32

Mark Tolonen

181k26 gold badges182 silver badges278 bronze badges

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

1 Comment

test1 1 Over a year ago

Many thanks @Mark Tolonen. I can confirm this worked.

HelixAchaos · Accepted Answer · 2021-08-25 16:07:43Z

0

ValueError: 'h' is a bad directive in format '%d/%m/%Y %h:%m:%s'

There's a big hint. The error is either 'h' or close to it. In this case, it looks like it is the lower case 'h' that is messing you up. Have you tried using 'H'?

datetime.strptime(string, '%d/%m/%Y %H:%M:%S')

answered Aug 25, 2021 at 16:07

HelixAchaos

1311 silver badge3 bronze badges

1 Comment

test1 1 Over a year ago

Hi Helix, I changed time variables to capitals but get the following error now ` datetimeobject = datetime.strptime(string, '%d/%m/%Y %H:%H:%S') File "/usr/lib64/python3.6/sre_parse.py", line 759, in _parse raise source.error(err.msg, len(name) + 1) from None sre_constants.error: redefinition of group name 'H' as group 5; was group 4 at position 116 `

Collectives™ on Stack Overflow

How to format datetime values in csv file/column in python?

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related