convert text file column field with data dd.mm.yy to dd.mm.YYYY

-1

How to format date for file content using linux command line tools?

Input:
aaa;1.70;ccc;20.01.13;zz;2013;
uuu;2.70;lll;17.12.10;zz;2013;
fff;3.70;nnn;31.01.98;zz;2013;
auu;8.70;nuu;30.02.96;zz;2013;

Output:
aaa;1.70;ccc;20.01.2013;zz;2013;
uuu;2.70;lll;17.12.2010;zz;2013;
fff;3.70;nnn;31;01.1998;zz;2013;
auu;8.70;nuu;30.02.1996;zz;2013;

Maris

Posted 2013-12-11T15:24:32.097

Reputation: 53

Answers

Assuming that any 2-digit year between 70 and 99 occurs last century:

sed 's/[7-9][0-9];$/19&/; s/[0-6][0-9];$/20&/' file

Or, let someone else decide:

perl -MTime::Piece -F';' -ane '
    $F[-2] = Time::Piece->strptime($F[-2], "%d.%m.%y")->strftime("%d.%m.%Y");
    print join(";", @F)
' file

Options

-MTime::Piece imports the standard Perl Time::Piece module
-F';' -a options split each line by semicolons and stores the result in the @F array
-n loops over the lines in the input data (stdin or files) without automatically printing each line.

Code

$F[-2] is the 2nd last field (which is where the date was in your original question).
I use the strptime and strftime methods to parse and format the date.

So, if you have dates that should be changes in columns 12 through 15, use

perl -MTime::Piece -F';' -ane '
    sub reformat {
        return Time::Piece->strptime(shift, "%d.%m.%y")->strftime("%d.%m.%Y");
    }
    for my $i (11..14) {
        $F[$i] = reformat $F[$i];
    }
    print join(";", @F)
' file

glenn jackman

Posted 2013-12-11T15:24:32.097

Reputation: 18 546

Can you comment, how it works? It does well with example, but when I put longer row with multiple dates, it somehow gives 01.01.1970 in the end of the row. Would like that it changes date format for column wich is number 12. and after column 12 there are other 10 columns. Dates should be changed in column 12 and 15. Other fields/columns should not be changed. – Maris – 2013-12-16T12:33:27.397

Works like charm! Only I seperated for my $i (11) and second time I take for my $i (14), did not worked together 11..14, Error parsing time at /usr/lib/perl/5.14/Time/Piece.pm line 469, <> line 1.:) – Maris – 2013-12-17T09:22:20.150

It can be done with awk:

   awk -F'[;.]' '{if ($6 <= 13) x=20$6; else x = 19$6 ; print \
                  $1";"$2";"$3";"$4"."$5"."x";"}'

This commands separates fields with separators ; and .. It then modifies the last field in this way: if it is smaller than or equal to 13, it is transformed to 20last_field, otherwise it is transformed to 19last_filed, then it prints the fields in the same order as before, with the same separators, including the modified sixth field (now called x).

MariusMatutiae

Posted 2013-12-11T15:24:32.097

Reputation: 41 321

This could be good solution too, but problem is that fields can not be seperated with "." because sometimes there can be some iformation with point before $6 and it will be counted then wrongly. – Maris – 2013-12-16T12:24:13.453

@Maris You should have stated this in the OP. – MariusMatutiae – 2013-12-16T12:43:19.263

Using dconv from dateutils:

dconv -Si '%d.%m.%y' -f '%d.%m.%Y' <<EOF
aaa;1.70;ccc;20.01.13;zz;2013;
uuu;2.70;lll;17.12.10;zz;2013;
fff;3.70;nnn;31.01.98;zz;2013;
auu;8.70;nuu;30.02.96;zz;2013;
EOF

produces exactly the output you want.

hroptatyr

Posted 2013-12-11T15:24:32.097

Reputation: 516