Need to change date format in a csv file using awk

Shell Programming and Scripting
1

Example:
Input csv file

00245DLS,Sitel Ocala,12/31/2014,18:45,1.00,7.00,0.00,0.00
00245DLS,Sitel Ocala,12/31/2014,19:00,-1.00,-1.00,-1.00,-1.00
00245HB,Charlotte,01/01/2015,00:00,-1.00,-1.00,-1.00,0.00

Output csv file

00245DLS,Sitel Ocala,2014/12/31,18:45,1.00,7.00,0.00,0.00
00245DLS,Sitel Ocala,2014/12/31,19:00,-1.00,-1.00,-1.00,-1.00
00245HB,Charlotte,2015/01/01,00:00,-1.00,-1.00,-1.00,0.00

i was able to do it as different field using this

cut -d',' -f3 test_tgt.csv | awk -F"/" '{ print $3"/"$1"/"$2 }'

Result:

2014/12/31
2014/12/31
2015/01/01

Please help..!!!

2

Try

awk -F, '{ $3 = substr($3,7,4)"/"substr($3,1,2)"/"substr($3,4,2); print }' OFS=, input.csv
1 Like
3

try

awk -F, '{split($3,a,"/"); $3=a[3]"/"a[1]"/"a[2]}1' OFS=,  input.csv
1 Like
4

Alternatively (if non-date fields have no slashes):

sed 's|\([^,]*\)/\([^,]*\)|\2/\1|g' file
1 Like
5

If you have gawk, this might help you

 awk '{print gensub(/(..)\/(..)\/(....)/,"\\3/\\1/\\2","",$0)}' infile

awk 'NF{$3=gensub(/(..)\/(..)\/(....)/,"\\3/\\1/\\2","",$3)}1' FS=, OFS=, infile
1 Like
6

Thanks.. Everyone.. All worked..!!

---------- Post updated at 08:48 PM ---------- Previous update was at 06:37 PM ----------

i was trying to sort this data but 2015 goes in the top as sort considers it as a string

ex:

00245HB,Charlotte,01/01/2015,00:30,-1.00,-1.00,-1.00,0.00
00245HB,Charlotte,01/01/2015,00:45,-1.00,-1.00,-1.00,0.00
00245HB,Charlotte,12/01/2014,00:00,-1.00,-1.00,-1.00,0.00
00245HB,Charlotte,12/02/2014,00:15,-1.00,-1.00,-1.00,0.00
00245HB,Charlotte,12/02/2014,00:30,-1.00,-1.00,-1.00,0.00
00245HB,Charlotte,12/02/2014,00:45,-1.00,-1.00,-1.00,0.00
00245HB,Charlotte,12/03/2014,00:00,-1.00,-1.00,-1.00,0.00

can any help me with this.
used number of sort options but could not get it right.

---------- Post updated at 08:49 PM ---------- Previous update was at 08:48 PM ----------

sort -u -o test.csv new_test.csv

was working till 2015 data started coming in.

---------- Post updated at 08:52 PM ---------- Previous update was at 08:49 PM ----------

so thought of changing date format to yyyy/mm/dd.
Any better idea?

7

Try:

sort -t, -k1,2 -k3.7,3.10 -k3.1,3.6 -k4 file
1 Like
8

It worked.. can you please explain it.

9

Sure:

-t,                  Use a comma as the field separator
-k1,2                Specify fields 1 and 2 as the primary key
-k3.7,3.10           Specify the 7th character through the 10th character of field 3 as the secondary key ( the year )
-k3.1,3.6            Specify rest of field 3 as the 3rd key
-k4                  Specify field 4 through the last field as the 4th key..

A numerical sort is not needed since the date parts are fixed length.

2 Likes