2

I have a file mdn.txt with following sample record.

mdn.txt:

123456,2711448,1,20150214092425,20150714092425,120,20150814163821,,123,,,123,20150214092425,,123,,,123,20150214092425,,123,Y

Now I want to process every record of my file by awk and want to change the format of date field like below:

123456,2711448,1,14-02-2015 09:24:25,14-07-2015 09:24:25,120,14-08-2015 16:38:21,,123,,,123,14-02-2015 09:24:25,,123,,,123,14-02-2015 09:24:25,,123,Y

Date input hereby is digits with (Year,Month,Day,Hour,Minute,Second)

YYYYMMDDHHMMSS

Output should be:

DD-MM-YYYY HH:MM:SS
1
  • Are the columns always the same, or do you need to replace any 14-digit strings into a date. If the columns are fixed, please state which fields (index numbers) contain dates to make it easier.
    – FelixJN
    Commented Aug 19, 2015 at 15:00

3 Answers 3

3
awk 'BEGIN{ FS=OFS="," 
            n=split("4,5,7,13,19", f)   # array of input field numbers
     }
     { for(i=1; i<=n; i++) if($f[i]) $f[i] = dconv($f[i]); print 
     }
     function dconv(x) {
       YY=substr(x, 1, 4)
       mm=substr(x, 5, 2)
       dd=substr(x, 7, 2)
       hh=substr(x, 9, 2)
       nn=substr(x,11, 2)
       ss=substr(x,13, 2)
       return dd"-"mm"-"YY" "hh":"nn":"ss  
     }' file

Below, is (fundamentally) the same script, with one more array (and without the function)

awk 'BEGIN{ FS=OFS=","
            nf=split("4,5,7,13,19", f)                           # array of input field numbers
            nd=split(",7,2,-,5,2,-,1,4, ,9,2,:,11,2,:,13,2", d)  # array of date subfield info (in output order): prefix(out),pos(in),len(in)         
     }
     { for(i=1; i<=nf; i++){ 
           if($f[i]) {
               fmod=""
               for(j=1; j<=nd; j+=3) fmod=fmod sprintf("%s", d[j] substr($f[i], d[j+1], d[j+2]))
               $f[i] = fmod
           }
       } print
     }' file
4
  • 1
    a variation for defining the function with gensub: function dconv(x) { x = gensub(/(....)(..)(..)(..)(..)(..)/,"\\3-\\2-\\1 \\4:\\5:\\6",1,x) ; return x }
    – FelixJN
    Commented Aug 19, 2015 at 15:47
  • 1
    gensub is an extensions in gawk (GNU awk), but is not in POSIX awk
    – Peter.O
    Commented Aug 19, 2015 at 15:56
  • if suppose any date field comes blank then below output comes by this command.which need to be handle. please suggest further. 123456,2711448,1,14-02-2015 09:24:25,14-07-2015 09:24:25,120,-- ::,,123,,,123,14-02-2015 09:24:25,,123,,,123,14-02-2015 09:24:25,,123,Y Commented Aug 20, 2015 at 5:57
  • The answer has been updated with a condition test for empty date fields. It now leave the field empty (as per the input)
    – Peter.O
    Commented Aug 20, 2015 at 7:53
1

Next will convert any field of 14 digits:

awk -F, ' function form(E){
              $E=substr($E,1,4) "-" substr($E,5,2) "-" substr($E,7,2) " "\
                 substr($E,9,2) ":" substr($E,11,2) ":" substr($E,13)
        }
        { for(i=1;i<=NF;i++)
              if($i ~ /[0-9]{14}/)
                  form(i)
          print
        }' OFS=, mdn.txt
2
  • i am getting the same output as input. the output is coming as below.123456,2711448,1,20150214092425,20150714092425,120,20150814163821,,123,,,123,20150214092425,,123,,,123,20150214092425,,123,Y Commented Aug 20, 2015 at 8:19
  • @user3548033 I have tried your string — all dates are successfully converted.
    – Costas
    Commented Aug 20, 2015 at 10:42
-1

If the source string is held in variable s, then you could just use printf and substr ie

printf("%2d-%2d-%4d %2d:%2d:%2d", substr(s,7,2), substr(s,5,2), substr(s,1,4), substr(s,9,2), substr(s,11,2), substr(s,13,2) )

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .