CODEWITHSUNDEEP
pythoncsvdatetime
PraVision
PraVision

Reputation: 11

Changing date time format in all rows in csv file

I have a csv file with data in this form..

2018-06-01T09:20:00+0530,16.85,16.9,16.85,16.9,1000
2018-06-01T09:21:00+0530,16.9,17,16.9,17,500
2018-06-01T09:22:00+0530,17,17,15.5,15.5,500
2018-06-01T09:23:00+0530,15.5,15.5,15.5,15.5,0

And I need to get it into this format..

01/06/2018 09:20:00,16.85,16.9,16.85,16.9,1000
01/06/2018 09:21:00,16.9,17,16.9,17,500
01/06/2018 09:22:00,17,17,15.5,15.5,500
01/06/2018 09:23:00,15.5,15.5,15.5,15.5,0

Basically the date format has to be changed for all rows in the file.. I have not been able to code this and using macros is rather painful.

Upvotes: 0

Views: 3148

Answers (2)

PraVision
PraVision

Reputation: 11

The final code which works on python version 2.7.15, which is drawn from that posted by Martin Evans is as follows:

from datetime import datetime
import csv

with open('f:\Downloads\Sample.csv') as f_input, open('f:\Downloads\Temp.csv', 'w') as f_output:
    csv_input = csv.reader(f_input)
    csv_output = csv.writer(f_output)
    csv_input.next()                            # skip header line
    for row in csv_input:
        dt = datetime.strptime(row[0], '%Y-%m-%dT%H:%M:%S+0530')    #parse the datetime
        row[0] = dt.strftime('%d/%m/%Y %H:%M:%S')           #assign the revised format
        csv_output.writerow(row)

Thanx everyone..

Upvotes: 0

Martin Evans
Martin Evans

Reputation: 46759

This can be done using Python's CSV and datetime libraries as follows:

from datetime import datetime
import csv

with open('input.csv', 'rb') as f_input, open('output.csv', 'wb') as f_output:
    csv_input = csv.reader(f_input)
    csv_output = csv.writer(f_output)

    for row in csv_input:
        dt = datetime.strptime(row[0], '%Y-%m-%dT%H:%M:%S%z')
        row[0] = dt.strftime('%d/%m/%Y %H:%M:%S')
        csv_output.writerow(row)

This would create an output.csv file containing:

01/06/2018 09:20:00,16.85,16.9,16.85,16.9,1000
01/06/2018 09:21:00,16.9,17,16.9,17,500
01/06/2018 09:22:00,17,17,15.5,15.5,500
01/06/2018 09:23:00,15.5,15.5,15.5,15.5,0

strptime() is used to convert your existing date into a Python datetime object. This can then be converted back into the format you need using the strftime() function. These use a special format to identify parts of the date using a format specification.

The csv library is used to read a line of the file in and automatically split it into a row containing a list of strings. row[0] holds the first value.

Upvotes: 1

Related Questions