sed/awk unix csv file modification

Question

I have a directory that is receiving .csv files.

column1,column2,column3,columb4
value1,0021,value3,value4,
value1,00211,value3,value4,

I want remove the header, pad the second column to 6 digits and add ":" so it is in HH:MM:SS format. e.g.

value1,00:00:21,value3,value4,
value1,00:02:11,value3,value4,

I can pad the characters to 6 digits using awk but I am not sure to insert the semicolumn every 2 characters for the second $2. Else can this be fully done in sed? which would be better for performance?

Thank you

With GNU awk, see ideone.com/gP0RgX

Wiktor Stribiżew
– Wiktor Stribiżew

2019-11-13 22:14:22 +00:00
Commented Nov 13, 2019 at 22:14 — Wiktor Stribiżew
– Wiktor Stribiżew, Commented Nov 13, 2019 at 22:14
why the 00211 is 000211 instead of 002101?

Kent
– Kent

2019-11-13 22:23:37 +00:00
Commented Nov 13, 2019 at 22:23 — Kent
– Kent, Commented Nov 13, 2019 at 22:23

Wiktor Stribiżew · Accepted Answer · 2019-11-13 22:25:04Z

2

You may do it all with GNU awk:

awk 'BEGIN{FS=OFS=","} {$2=sprintf("%06d", $2); $2=substr($2,1,2) gensub(/.{2}/,":&","g",substr($2,3))}1' file

See an online demo

Details

BEGIN{FS=OFS=","} - sets input/output field separator to a comma
$2=sprintf("%06d", $2) - pads Field 2 with zeros
$2=substr($2,1,2)""gensub(/.{2}/,":&","g",substr($2,3)) - sets Field 2 value equal to a first two chars of the field (substr($2,1,2)) plus the field substring starting from the third char with : inserted before each two char chunk.
1 - default print action.

answered Nov 13, 2019 at 22:25

Wiktor Stribiżew

631k41 gold badges502 silver badges632 bronze badges

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

Comments

RomanPerekhrest · Accepted Answer · 2019-11-13 22:34:41Z

2

With awk formatting + substitution magic:

awk 'BEGIN{ FS = OFS = "," }
     NR > 1{ $2=sprintf("%06d", $2); gsub(/[0-9]{2}/, "&:", $2); 
             $2=substr($2, 0, 8); print }' file

The output:

value1,00:00:21,value3,value4,
value1,00:02:11,value3,value4,

answered Nov 13, 2019 at 22:34

RomanPerekhrest

93.1k4 gold badges75 silver badges112 bronze badges

1 Comment

Rory Lester Over a year ago

Thank you. If i want to expand this to e.g. if $3 != "Processed" then remove line. How would the syntax be?

karakfa · Accepted Answer · 2019-11-13 22:15:43Z

1

with sed

$ sed -nE '2,$s/,([0-9]+)/,00000\1/;s/,0+(..)(..)(..),/,\1:\2:\3,/p' file

value1,00:00:21,value3,value4,
value1,00:02:11,value3,value4,

I think it can be simplified little bit.

answered Nov 13, 2019 at 22:15

karakfa

67.8k8 gold badges45 silver badges59 bronze badges

Collectives™ on Stack Overflow

sed/awk unix csv file modification

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related