format date column in csv with bash script

Question

I have multiple comma separated files in a folder. Each CSV file looks like following:

"Column1","Date","Column2"
"fdfsd","20151023","rwer"
"fsdsf","20151023","jjfg"
.
.

I need to modify the 2nd Date column to display the date in this format "10/14/2015" or "2015-10-14".

Please can someone help me with this?

Thanks

This is what i have tried:

cat test.csv | while read line ; do echo $line\;$(date -d "%Y%m%d" "+ YYYY/MM/DD") ; done

usage: date [-jnu] [-d dst] [-r seconds] [-t west] [-v[+|-]val[ymwdHMS]] ... [-f fmt date | [[[mm]dd]HH]MM[[cc]yy][.ss]] [+format] "Column1","Date","Column2";

usage: date [-jnu] [-d dst] [-r seconds] [-t west] [-v[+|-]val[ymwdHMS]] ... [-f fmt date | [[[mm]dd]HH]MM[[cc]yy][.ss]] [+format] "fdfsd","20151023","rwer";

usage: date [-jnu] [-d dst] [-r seconds] [-t west] [-v[+|-]val[ymwdHMS]] ... [-f fmt date | [[[mm]dd]HH]MM[[cc]yy][.ss]] [+format] "fsdsf","20151023","jjfg";

Sorry about not posting earlier. Please have a look. Thanks

user175084
– user175084

2015-10-26 16:37:49 +00:00
Commented Oct 26, 2015 at 16:37 — user175084
– user175084, Commented Oct 26, 2015 at 16:37
Can you use perl or awk or python?

dawg
– dawg

2015-10-26 16:50:12 +00:00
Commented Oct 26, 2015 at 16:50 — dawg
– dawg, Commented Oct 26, 2015 at 16:50
I can try using awk..

user175084
– user175084

2015-10-26 16:57:48 +00:00
Commented Oct 26, 2015 at 16:57 — user175084
– user175084, Commented Oct 26, 2015 at 16:57

glenn jackman · Accepted Answer · 2015-10-26 16:59:29Z

4

Since you just need to insert hyphens into the date, this is a string manipulation problem not a date manipulation problem:

sed -i.bak -re 's/([0-9]{4})([0-9]{2})([0-9]{2})/\1-\2-\3/' *.csv

answered Oct 26, 2015 at 16:59

glenn jackman

249k42 gold badges233 silver badges362 bronze badges

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

Comments

anubhava · Accepted Answer · 2015-10-26 17:04:57Z

3

You can use this awk to reformat a given date string:

awk 'BEGIN{FS=OFS=","} NR>1{cmd = "date -d \"" $2 "\" \"+%Y-%m-%d\"";
       cmd | getline out; $2=out; close("uuidgen")} 1' file
"Column1","Date","Column2"
"fdfsd",2015-10-23,"rwer"
"fsdsf",2015-10-14,"jjfg"

answered Oct 26, 2015 at 17:04

anubhava

790k67 gold badges603 silver badges671 bronze badges

2 Comments

user175084 Over a year ago

Hi.. This works great.. Is there way i can use it to just modify the column in the file and not print the entire thing?

anubhava Over a year ago

It will be:

awk 'BEGIN{FS=OFS=","} NR>1{cmd = "date -d \"" $2 "\" \"+%Y-%m-%d\""; cmd | getline out; $2=out; close("uuidgen")} 1' file > _file.tmp && mv _file.tmp file

dawg · Accepted Answer · 2015-10-26 17:29:10Z

1

Python has a very robust csv module.

You can do:

$ python -c '
import csv
import datetime
import fileinput

def line_out(line): 
    print ",".join(["\"{}\"".format(e) for e in line])

csv_data=csv.reader(fileinput.input())
line_out(next(csv_data))
for line in csv_data:
    line[1]=datetime.datetime.strptime(line[1], "%Y%m%d").date().isoformat()
    line_out(line)' file

Prints:

"Column1","Date","Column2"
"fdfsd","2015-10-23","rwer"
"fsdsf","2015-10-23","jjfg"

answered Oct 26, 2015 at 17:29

dawg

105k24 gold badges142 silver badges217 bronze badges

Collectives™ on Stack Overflow

format date column in csv with bash script

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related