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
  • 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. – Fiximan Aug 19 '15 at 15:00
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
  • 1
    a variation for defining the function with gensub: function dconv(x) { x = gensub(/(....)(..)(..)(..)(..)(..)/,"\\3-\\2-\\1 \\4:\\5:\\6",1,x) ; return x } – Fiximan Aug 19 '15 at 15:47
  • 1
    gensub is an extensions in gawk (GNU awk), but is not in POSIX awk – Peter.O Aug 19 '15 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 – user3548033 Aug 20 '15 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 Aug 20 '15 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
  • 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 – user3548033 Aug 20 '15 at 8:19
  • @user3548033 I have tried your string — all dates are successfully converted. – Costas Aug 20 '15 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) )

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.