Most vexing: Sed or Awk scripting for date conversion needed

Shell Programming and Scripting
1

Hi,

I have some files being sent to me that have dates in them in this format:
from
1/8/2011 15:14:20

and I need the dates in this format (mysql date format)
To
2011-01-08 15:14:20

all I have so far is the regexp that detects the format:

sed -r -e 's@[0-9]\1/[0-9]\2/[0-9]\3[0-9]\4[0-9]\5[0-9]\6[[:space:]][0-9][0-9]:[0-9][0-9]:[0-9][0-9]$@do something@'

How does one change places of the data in Sed so that it swaps the locations of the year and replaces the slashes with hyphens?

2

Hello, Astrocloud:

I think the easiest solution is some AWK pixie dust. The following one-liner not only shuffles the date components, but it makes certain to zero-pad them (1 to 01, 8 to 08, etc):

awk -F'/| ' '{printf("%.4d-%.2d-%.2d %s\n", $3, $1, $2, $4)}'

Regards and welcome to the forum,
Alister

3

Thanks!

I am guilty however of "dumbing down" the problem.

I have a csv file like the following:

"104787","MILLER,BOB M.","","Automatic refund. Expected over-payment","67.47","A","225","","128110048","1/8/2011 15:14:20"
"104196","REBECCA M MANNERS","","Automatic refund. Expected over-payment","53.21","A","225","","128110047","12/28/2011 15:14:20"
"104415","LEMMING,LETTUCE","","Automatic refund. Expected over-payment","257.03","A","225","","128110046","7/22/2011 15:14:20"
"104535","BUYTRON, LATOYA","","Automatic refund. Expected over-payment","281.51","A","225","","128110045","10/13/2011 15:14:20"
"104698","Jesus Rodriguez","Chase Bank","Prorate for december. 512-784-2711","200.00","N","225","","128110049","12/25/2011 15:14:20"

I am in the process of modifying your script... Perhaps I need better documentation on Awk... but basically I'm getting stuck.

How do you separate by

[quote]
[comma]

[quote]
"," rather than merely [comma] ,

4

To separate on a longer string, make the separator the longer string...
awk -v FS="\",\"" ...

5 
root@bt:/tmp# echo '"104787","MILLER,BOB M.","","Automatic refund. Expected over-payment","67.47","A","225","","128110048","1/8/2011 15:14:20"' 
| awk -F[\",] '{print $(NF-1)}'

1/8/2011 15:14:20
6

Using Perl -

$
$ cat csv.txt
"104787","MILLER,BOB M.","","Automatic refund. Expected over-payment","67.47","A","225","","128110048","1/8/2011 15:14:20"
"104196","REBECCA M MANNERS","","Automatic refund. Expected over-payment","53.21","A","225","","128110047","12/28/2011 15:14:20"
"104415","LEMMING,LETTUCE","","Automatic refund. Expected over-payment","257.03","A","225","","128110046","7/22/2011 15:14:20"
"104535","BUYTRON, LATOYA","","Automatic refund. Expected over-payment","281.51","A","225","","128110045","10/13/2011 15:14:20"
"104698","Jesus Rodriguez","Chase Bank","Prorate for december. 512-784-2711","200.00","N","225","","128110049","12/25/2011 15:14:20"
$
$
$ perl -ne 'chomp;
            m|^(.*)\b(\d+)/(\d+)/(\d+)\b(.*)$|;
            printf ("%s%04d-%02d-%02d%s\n", $1,$4,$3,$2,$5)
           ' csv.txt
"104787","MILLER,BOB M.","","Automatic refund. Expected over-payment","67.47","A","225","","128110048","2011-08-01 15:14:20"
"104196","REBECCA M MANNERS","","Automatic refund. Expected over-payment","53.21","A","225","","128110047","2011-28-12 15:14:20"
"104415","LEMMING,LETTUCE","","Automatic refund. Expected over-payment","257.03","A","225","","128110046","2011-22-07 15:14:20"
"104535","BUYTRON, LATOYA","","Automatic refund. Expected over-payment","281.51","A","225","","128110045","2011-13-10 15:14:20"
"104698","Jesus Rodriguez","Chase Bank","Prorate for december. 512-784-2711","200.00","N","225","","128110049","2011-25-12 15:14:20"
$
$

tyler_durden

7

How about plain old sed...

sed 's;\([0-9][0-9]*\)/\([0-9][0-9]*\)/\([0-9][0-9]*\);\3-\2-\1;' file
8

Egads! that's it.

I was playing around with Awk but I'm rather new to it.

perl -ne 'chomp;
            m|^(.*)\b(\d+)/(\d+)/(\d+)\b(.*)$|;
            printf ("%s%04d-%02d-%02d%s\n", $1,$4,$3,$2,$5)
           ' test.csv


root@astrocloud:/home/baldDude/Documents# perl -ne 'chomp;
            m|^(.*)\b(\d+)/(\d+)/(\d+)\b(.*)$|;
            printf ("%s%04d-%02d-%02d%s\n", $1,$4,$3,$2,$5)
           ' test.csv > test.2.csv
root@astrocloud:/home/baldDude/Documents# more test.2.csv
"104787","MILLER,BOB M.","","Automatic refund. Expected over-payment","67.47","A
","225","","128110048","2011-08-01 15:14:20"
"104196","REBECCA M MANNERS","","Automatic refund. Expected over-payment","53.21
","A","225","","128110047","2011-28-12 15:14:20"
"104415","LEMMING,LETTUCE","","Automatic refund. Expected over-payment","257.03"
,"A","225","","128110046","2011-22-07 15:14:20"
"104535","BUYTRON, LATOYA","","Automatic refund. Expected over-payment","281.51"
,"A","225","","128110045","2011-13-10 15:14:20"
"104698","Jesus Rodriguez","Chase Bank","Prorate for december. 512-784-2711","20
0.00","N","225","","128110049","2011-25-12 15:14:20"

---------- Post updated at 02:50 PM ---------- Previous update was at 02:47 PM ----------

With one minor adjustment:

perl -ne 'chomp;
            m|^(.*)\b(\d+)/(\d+)/(\d+)\b(.*)$|;
            printf ("%s%04d-%02d-%02d%s\n", $1,$4,$2,$3,$5)
           ' test.csv > test.2.csv

YYYY-MM-DD not YYY-DD-MM