I have a CSV that has the output of time commands of different programs including the ID of the execution.

Therefore my files contain records in the following form:


Example Record:


I only need the number of execution and the number of seconds.

Therefore the above record needs to be

345,0047 (without the "0m" the "." and the "s").

Is it possible to achieve that?

  • $ time sleep 61 outputs: real 1m1.046s (ie, you don't want to get rid of the minutes... and you need sometimes to have leading zeroes (here, 1s is "1" and not "01")) – Olivier Dulac Jul 3 '14 at 7:44

Here is my solution:

sed -i 's/[0-9]*m\|s\|\.//g' file.csv

You may want to skip the first line, in case it contains the header, like this:

sed -i '2,$s/[0-9]*m\|s\|\.//g' file.csv

Note that the -i option causes sed to work "in place", altering the original file. If you want to create a new file instead, use this:

sed 's/[0-9]*m\|s\|\.//g' file.csv > new_file.csv

What if there are minutes, eh? Here's one that counts for that, converting them into seconds:

awk -F'[m,]' '{print $1 "," $2*60 + $3}' file.csv

And a few demos:

$ ... <(echo -e "345,0m0.047s\n345,1m0.047s\n345,1m2.047s")

I'm not sure why you'd ever want to remove the decimal point but you can pass the output through sed 's/\.//g' if you really need that:

$ ... <(echo -e "345,0m0.047s\n345,1m0.047s\n345,1m2.047s") | sed 's/\.//g'


And —assuming this is a time output— what if the value contains hours or even days? I've played around a bit and have come up with this little beaut:

awk -F'[dhms,]' 'BEGIN{split("1 60 3600 86400", T, " ")}{t=0; for (i=NF-1; i>1; i--) t+=T[NF-i]*$i; printf("%s,%.3f\n", $1, t)}'

This will handle the following formats: *s *m*s *h*m*s and *d*h*m*s and convert them all into seconds (to three decimal places). Obligatory demo time:

$ ... <(echo -e "345,1.2s\n345,12m5s\n345,1h2m5s\n345,1d2h1m2.047s")
  • So does mine. I think. :) – kraxor Jul 2 '14 at 9:01
  • 1
    @kraxor Yours doesn't multiply them by 60 though, you're just leaving the minute value on the front of the seconds. – Oli Jul 2 '14 at 9:05
  • @Oli you're right. I guess I took "the number of seconds" literally. OP also said "withouth the 0m". +1 to you though for a more generic solution. – kraxor Jul 2 '14 at 9:14

It should be:

sed 's/m0\.\(.*\)s$/\1/g'

  • 1
    ooohhh!!! thanks a lot Yaron! Upvoted :) and how I do that if my file has several records? – Jim Blum Jul 2 '14 at 8:54
  • I tried that at this record "5234,0m1.022s", but I think I cannot make it work – Jim Blum Jul 2 '14 at 8:58
  • Just write them line by line in a file (you can do this using your favourite text editor or automatically using the bash concatenation feature, and then just type cat myfile.txt | sed... or sed -i.bak sed ... myfile.txt, hope it works fine for you :) – Yaron Jul 2 '14 at 8:58
  • Sure @JimBlum it has 1. in it and the script is only removing 0., give me some more examples and I'll try to make something more general (the original and desired outcome of course) – Yaron Jul 2 '14 at 9:00

Another one through awk,

awk -F, -v OFS="," '{gsub(/0m/,"",$2);gsub(/\./,"",$2);gsub(/s/,"",$2); print $1,$2}' file


$ echo '345,0m0.047s' | awk -F, -v OFS="," '{gsub(/0m/,"",$2);gsub(/\./,"",$2);gsub(/s/,"",$2); print $1,$2}'

And the appropriate one,

$ echo '345,52m0.047s' | awk -F, -v OFS="," '{gsub(/^.*?m/,"",$2);gsub(/\./,"",$2);gsub(/s/,"",$2); print $1,$2}'

The answer of @kraxor uses sed to remove "everything else" from the line, and then just show the line.

But - what actually is "everything else"? Here we have an idea at least, from one sample line. But what when we get a CSV header line? What to remove?
In general, we don't know.

Therefore, it is better to actually show the data we want to show!

echo '345,0m0.047s' | sed -n -r 's/^(.*),.*[^0-9]([0-9]*)\.(.*)s$/\1,\2\3/p'


Works so far!

What does it do?
We do not print the line normaly (-n)
(and activate the nicer "extended regular expressions" (-r)) now look for the ID, second, and fractional second,
and if we found them, put them into the line, in the right format (adding ","),
and print the newly created line.

Now some other input, pretty normal, with two lines of data:

123, Oops a comment0m0.0333s

Huh?! Looks like real world data, actually!

echo "ID,execution_time\n123, Oops a comment0m0.0333s\n345,0m0.047s" | sed -r -n 's/^(.*),.*[^0-9]([0-9]*)\.(.*)s$/\1,\2\3/p'


Looks nice and just right!

To show that it has some merit to do it this way, I'll compare to the earlier answer:

echo "ID,execution_time\n123, Oops a comment0m0.0333s\n345,0m0.047s" | sed 's/[0-9]*m\|s\|\.//g'

123, Oop a coent00333

Ok, the actual, clean data lines went through nicely;
But the other parts, not so much (note the header was not just passed, but cut off.).

(Note there is a solution to explicitly skip the header line (or maybe the first data line, do we know?))

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.