CODEWITHSUNDEEP
pythonpython-3.x
Sudhagar Rajaraman
Sudhagar Rajaraman

Reputation: 69

Change Data Format in CSV column

I'm trying to change Date format (DD-MMM-YY) to ( YYYY-MM-DD ) in a CSV file.

Its throwing errors . 

import csv
from datetime import datetime

f1 = open ("D:\\bio.csv","r") # open input file for reading

with open('D:\\mm.csv', 'wb') as f: # output csv file
    writer = csv.writer(f)
    with open('D:\\bio.csv','r') as csvfile: # input csv file
        reader = csv.reader(csvfile, delimiter=',')
        next(reader, None)
        for row in reader:
            dater = row[2]
            #print (dater)
            my_date = datetime.strptime(dater, '%d-%b-%Y')

            kr = (my_date.date())

            row[2] = kr


            data = [["Symbol","Series","Date","Prev Close","Open Price","High Price","Low Price","Last Price","Close Price","Average Price","Total Traded Quantity","Turnover","No. of Trades","Deliverable Qty","% Dly Qt to Traded Qty"],row]

            writer.writerow(data)

f1.close()

Sample CSV

Symbol,Series,Date,Prev Close,Open Price,High Price,Low Price,Last Price,Close Price,Average Price,Total Traded Quantity,Turnover,No. of Trades,Deliverable Qty,% Dly Qt to Traded Qty
BIOCON,EQ,21-Jul-17,402.6,403,409.55,393.2,399,400.25,401.52,3032146,1217472594,39314,321923,10.62
BIOCON,EQ,24-Jul-17,400.25,399,405.9,396.1,399.6,399,401.25,2090835,838941962.6,25520,392951,18.79

Trying to get output in multiple ways again and again its end up same error .

Error:

Traceback (most recent call last):
  File "C:/Users/admin/PycharmProjects/P1/n.py", line 23, in <module>
    writer.writerow(data)
TypeError: a bytes-like object is required, not 'str'

Upvotes: 1

Views: 63

Answers (2)

Andrej Kesely
Andrej Kesely

Reputation: 195543

Don't open your file in binary mode (open it without the b flag):

with open('data_out.csv', 'w') as f: # output csv file

Complete source (notice you can have multiple open() in with, also I changed %Y to %y in your strptime):

import csv
from datetime import datetime

with open ("data.csv","r") as f1, open('data_out.csv', 'w') as f: # output csv file
    writer = csv.writer(f)
    with open('data.csv','r') as csvfile: # input csv file
        reader = csv.reader(csvfile, delimiter=',')
        next(reader, None)
        for row in reader:
            dater = row[2]
            #print (dater)
            my_date = datetime.strptime(dater, '%d-%b-%y')
            kr = (my_date.date())
            row[2] = kr
            data = [["Symbol","Series","Date","Prev Close","Open Price","High Price","Low Price","Last Price","Close Price","Average Price","Total Traded Quantity","Turnover","No. of Trades","Deliverable Qty","% Dly Qt to Traded Qty"],row]
            writer.writerow(row)

The output of your file will become:

BIOCON,EQ,2017-07-21,402.6,403,409.55,393.2,399,400.25,401.52,3032146,1217472594,39314,321923,10.62
BIOCON,EQ,2017-07-24,400.25,399,405.9,396.1,399.6,399,401.25,2090835,838941962.6,25520,392951,18.79

Upvotes: 1

Rakesh
Rakesh

Reputation: 82785

This should help.

import csv
from datetime import datetime

with open(filename, "rU") as infile, open(filename2, "wb") as outfile:
    reader = csv.reader(infile)
    writer = csv.writer(outfile)
    writer.writerow(next(reader))   #Write Header
    result = []
    for line in reader:        #Iterate each line 
        temp = line
        temp[2] = datetime.strptime(temp[2], '%d-%b-%y').strftime("%Y-%m-%d")    #Update date format
        writer.writerow(temp)   #Write file

Output:

Symbol,Series,Date,Prev Close,Open Price,High Price,Low Price,Last Price,Close Price,Average Price,Total Traded Quantity,Turnover,No. of Trades,Deliverable Qty,% Dly Qt to Traded Qty
BIOCON,EQ,2017-07-21,402.6,403,409.55,393.2,399,400.25,401.52,3032146,1217472594,39314,321923,10.62
BIOCON,EQ,2017-07-24,400.25,399,405.9,396.1,399.6,399,401.25,2090835,838941962.6,25520,392951,18.79

Upvotes: 1

Related Questions