Python 3.6 CSV Date Formatting d/m/yyyy to ddmmyyyy

Question

I have a date column in a CSV file which I am trying to format from dd/mm/yyyy to ddmmyyyy. Some of the days and months are single digit which leave them as dmyyyy. When I run a print statement all of the rows output correctly.

import csv with open(r'input file path,'r') as csvfile: with open(r'outputfilepath,'w') as output: w = csv.writer(output) r = csv.reader(csvfile) for row in r: #this takes care of incomplete rows at the end if len(row[6])>1: dt = row[6].split("/") n = 0 for n in range(len(dt)): if len(dt[n])<2: dt[n] = '0'+dt[n] else: dt[n] row[6] = dt[0]+dt[1]+dt[2] print(row) else: break

Print Output:

['a', '', 'Tom', 'Smith', 'J ', '', '12201956'] ['b', '', 'Rick ', 'JOHNSON ', ' ', '', '08121922'] ['c', '', 'Morty', 'Harvey', ' ', '', '06031940']

When I change the print to write rows:

import csv with open(r'input file path,'r') as csvfile: with open(r'outputfilepath,'w') as output: w = csv.writer(output) r = csv.reader(csvfile) for row in r: #this takes care of incomplete rows at the end if len(row[6])>1: dt = row[6].split("/") n = 0 for n in range(len(dt)): if len(dt[n])<2: dt[n] = '0'+dt[n] else: dt[n] row[6] = dt[0]+dt[1]+dt[2] w.writerows(row) else: break

I get the output below. I've tried moving the writerows function around with no luck. Looking at the CSV module documentation it should delimit on the commas. I'm relatively new to python.

Change w.writerows(row) to w.writerow(row). Plural vs singular. You would also be well-served to be adding newline='' to your open() because the csv writer does its own newline handling. That would explain the extra newlines in your already messed up data. — Steven Rumbalski
– Steven Rumbalski, Commented Jan 11, 2018 at 19:35
row[6] = datetime.datetime.strptime(row[6],'%m/%d/%Y').strftime('%m%d%Y') — Steven Rumbalski
– Steven Rumbalski, Commented Jan 11, 2018 at 19:43
Why not row[6].replace('/', '')? Don't need to import datetime — r.ook
– r.ook, Commented Jan 11, 2018 at 19:48
@Idlehands: He's doing dates that are sometimes missing leading zeros. Like 1/11/2018 or 12/1/2017. That can be handled with string methods, but it can be handled entirely mindlessly with datetime methods. It takes a bit more thought to write ''.join(x.zfill(2) for x in row[6].split('/')). More importantly though is it's clear to the reader what the datetime method is doing. — Steven Rumbalski
– Steven Rumbalski, Commented Jan 11, 2018 at 20:03

Steven Rumbalski · Accepted Answer · 2018-01-11 21:35:52Z

To fix your problem change w.writerows(row) to w.writerow(row). The difference is between the singular and the plural is that the plural version thinks its getting a collection of rows to write. It treats each item in the row you gave as a single row.

Also newline='' to your open because the csv module interacts poorly with universal newline mode on windows. (It tries to write '\r\n'. Universal newline translates that to '\r\r\n'.)

Finally, use datetime to fix your dates.

import csv from datetime import datetime with open(inpath, 'r', newline='') as fin: with open(outpath, 'w', newline='') as fout: reader = csv.reader(fin) writer = csv.writer(fout) for row in reader: row[6] = datetime.strptime(row[6], '%m/%d/%Y').strftime('%m%d‌%Y') writer.writerow(row)

Thank you kind sir for the exceptionally great and thorough explanation. Changing "writerows" to "writerow" worked perfectly. When I attempted to run the code you provided I received an "invalid syntax" error on the second open statement. This was fixed by adding a "with" before the second open. It now throws an encoding error. I will try to track down the culprit and fix the characters. Thank you again.

Collectives™ on Stack Overflow

Python 3.6 CSV Date Formatting d/m/yyyy to ddmmyyyy

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related