Bash modify CSV to change a field

Question

I have a very big CSV file (aprox. 10.000 rows and 400 columns) and I need to modify certain columns (like 15, 156, 220) to change format from 20140321132233 to 2014-03-21 13:22:33. All fields that I need to modify are datetime.

I saw some examples using awk but for math modifications. Can I use something like this for doing the above change?

file.csv example:

19238328932|123233443|123|0|||||123123|20140321132233|1|0|0|....|20130211122143|...
12332312211|222321233|111|0|||||234432|20150222122354|1|0|0|....|20120112123133|...

@TomFenech uhms, doesn't GNU awk 3.1 have mktime? It was also a call to do some research instead of a just "give me my code" question. — fedorqui
– fedorqui, Commented Jun 22, 2015 at 10:04
@fedorqui I'm not 100% sure about which version introduced mktime (I had a feeling it might be 4 but could be wrong). Agreed that it would be nice to see some more input from the OP; my main point was that this problem would be quite a hassle to solve using date functions. — Tom Fenech
– Tom Fenech, Commented Jun 22, 2015 at 10:15
@TomFenech All of 3 has mktime. It also has match with 3 arguments which makes parsing the text simple. — 123
– 123, Commented Jun 22, 2015 at 11:24

NarūnasK · Accepted Answer · 2015-06-22 09:03:42Z

4

Please save following awk script as awk.src:

function date_str(val) {
  Y = substr(val,0,4);
  M = substr(val,5,2);
  D = substr(val,7,2);
  date = sprintf("%s-%s-%s",Y,M,D);
  return date;
}
function time_str(val) {
  h = substr(val,9,2);
  m = substr(val,11,2);
  s = substr(val,13,2);
  time = sprintf("%s:%s:%s",h,m,s);
  return time;
}

BEGIN {
  FS="|"
}
#
## MAIN Block
#
{
  for (i=1;i<=NF;i++) {
    if (i==10) {
      printf "%s %s", date_str($i), time_str($i);
    }
    else { printf $i; }
    if (i!=NF) {
      printf FS;
    }
    else { printf "\n"; }
  }
}

Now try it, it should print:

$ awk -f awk.src csv 
19238328932|123233443|123|0|||||123123|2014-03-21 13:22:33|1|0|0|....|20130211122143|...
12332312211|222321233|111|0|||||234432|2015-02-22 12:23:54|1|0|0|....|20120112123133|...

edited Jun 22, 2015 at 9:03

answered Jun 22, 2015 at 8:56

NarūnasK

4,9989 gold badges56 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

NarūnasK Over a year ago

Include other columns in the 'if' test and job's done :)

Collectives™ on Stack Overflow

Bash modify CSV to change a field

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related