Sstrhsrtj

My input file layout is: mm/dd/yyyy,hh:mm,other fields

I need to format it as: yyyy-mm-dd hh:mm:00,other fields

sample input:

01/02/1998,09:30,0.4571,0.4613,0.4529,0.4592,6042175

01/02/1998,09:45,0.4592,0.4613,0.4529,0.4571,9956023

01/02/1998,10:00,0.4571,0.4613,0.455,0.4613,8939555

01/02/1998,10:15,0.4613,0.4697,0.4571,0.4697,12823627

01/02/1998,10:30,0.4676,0.4969,0.4613,0.4906,28145145

sample output:

1998-01-02 09:30:00,0.4571,0.4613,0.4529,0.4592,6042175

etc...

I tried to use:

sed -r 's/(^[0-9][0-9])(/[0-9][0-9]/)(/[0-9][0-9][0-9][0-9],)/312/g

sed -e 's/(..)/(..)/(....),(.....),(.*)/3-1-2 4:00,5/'

Edited to include the input from the comments below:

sed -e 's#(..).(..).(....),(.....),#3-1-2 4:00,#'

That worked for me:

sed -r 's/([0-9]{2})/([0-9]{2})/([0-9]{4}),([0-9:]{5})/3-1-2 4:00/g'

Match 2 digits (([0-9]{2})), slash, 2 digits (([0-9]{2})), slash, 4 digits (([0-9]{4})), and then digits and : (([0-9:]{5})). Replace it with the order you wish: 3-1-2 4:00 (year-month-day hour:minute:00).

sed 'y|/|-|

     s/,*(.....)-*([^,]*)/2-1/

     s// 1:00/2

'    <infile

OUTPUT:

1998-01-02 09:30:00,0.4571,0.4613,0.4529,0.4592,6042175

1998-01-02 09:45:00,0.4592,0.4613,0.4529,0.4571,9956023

1998-01-02 10:00:00,0.4571,0.4613,0.455,0.4613,8939555

1998-01-02 10:15:00,0.4613,0.4697,0.4571,0.4697,12823627

1998-01-02 10:30:00,0.4676,0.4969,0.4613,0.4906,28145145

With sed you don't usually need to try so hard - it often doesn't pay to try to explicitly enumerate the matches you're looking for. Rather, it is far simpler usually just to specify a few landmarks - delimiters - and let a pattern gobble up the interim for you.

Above sed first y/// translates / characters to - characters. Next it references the first not-comma (provided there are at least 5) character in pattern-space and and the next four characters as 1 while possibly ignoring a trailing -. It follows that on by referencing as many sequential ^not-comma chars in 2 as it might before the next occurring comma in pattern space. The result - for the first substitution - is that it puts mm-dd in 1 before matching - and then yyyy in 2. So we swap those, drop the - and insert a new one on the other side like:

s/.../2-1/

And last we do it again - reusing the same pattern for a different purpose. When I do:

s// 1:00/2

I'm instructing sed to reuse the last regexp (as signified by the // empty address), but this time to find the second occurrence of that pattern in pattern space - which does match a comma with ,* this time - it matches the comma separating this field and the last. It also matches HH:MM in 1 and (because that string is immediately followed by a comma) the '' null-string in 2. All that remains from there is to replace 1 with itself preceded by a <space> and followed by the :00 string. Both the intervening comma and the null-string are edited away.

If you feel you would rather get a little more specific after all, though, consider how much easier it might be with just a little abstraction. The primary benefit offered by regular expressions is that they provide us a means of quickly and efficiently abstracting away a repetitive task given only a clear understanding of what makes it repetitive in the first place.

If constructing your regexp becomes a repetitive task in and of itself, then, well... something's probably missing. One of the advantages of a simple regexp syntax, though, is that it too often makes a very good candidate for abstraction - and it is easily achieved.

For example:

d='[0-9][0-9]' T=$d:$d m=$d y=$d$d

sed -E "s|($m/$d)/($y),($T)|2-1 3:00|;s|/|-|"

I would suggest taking a slightly different approach - parse the timestamp, then spit out a formatted timestamp. And I'd use perl for this:

#!/usr/bin/perl



use strict;

use warnings;



use Time::Piece;



my $input_format  = '%m/%d/%Y,%H:%M';

my $output_format = '%Y-%m-%d %H:%M:%S';



while (<>) {

    my ( $date, $time, @stuff ) = split(",");

    my $timestamp = Time::Piece->strptime( "$date,$time", $input_format );

    print join( ",", $timestamp->strftime($output_format), @stuff );

}

Which you can reduce to a one liner thus:

perl -MTime::Piece -lne '($date,$time,@stuff) = split; print join ( ',', Time::Piece->strptime( "$date,$time", "%m/%d/%Y,%H:%M" ) -> strftime("%Y-%m-%d %H:%M:%S"), @stuff);'

Which with your sample data, spits out:

1998-01-02 09:45:00,0.4592,0.4613,0.4529,0.4571,9956023

1998-01-02 10:00:00,0.4571,0.4613,0.455,0.4613,8939555

1998-01-02 10:15:00,0.4613,0.4697,0.4571,0.4697,12823627

1998-01-02 10:30:00,0.4676,0.4969,0.4613,0.4906,28145145

And possible awk solution:

awk 'BEGIN { FS = OFS = ","; } { split($1, d, "/"); $2 = d[3] "-" d[1] "-" d[2] " " $2 ":00"; $1 = ""; } { for (i = 2; i < NF; i++) printf("%s", $i OFS); printf("%s", $NF ORS);}' file

Use this:

sed -n 's_^([^/]*)/([^]*)/([^,]*),([^:]*):([^,]*)_3-1-2 4:5:00_p' file.txt