mjhjmtu

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