0

I have data in CSV, where 3rd column contain exact 5 digit number. Need to copy this number and paste in last column.

2020-05-19_19:03:07,210,HR051919040_to_17622_is_ok.   
2020-05-19_19:03:07,200,MP051919032700_to_Average_95840_is_ok.   
2020-05-19_19:03:07,200,   
2020-05-19_19:03:07,200,RJ0515_to_System__to_66638_is_nok.   

Output:

2020-05-19_19:03:07,210,HR051919040_to_17622_is_ok.,17622   
2020-05-19_19:03:07,200,MP051919032700_to_Average_95840_is_ok.,95840   
2020-05-19_19:03:07,200,,   
2020-05-19_19:03:07,200,RJ0515_to_System__to_66638_is_nok.,66638  

I tried to use sed to search for 5 digit number but it is not working as it has character as well.

sed -n '/\b[0-9]\{5\}\b/p'
1
  • The question is interesting, but just too hard to read for further generations of readers. – Volker Siegel May 28 '20 at 4:04
3
$ sed -E 's/_([0-9]{5})_(.*)$/_\1_\2,\1/' file
2020-05-19_19:03:07,210,HR051919040_to_17622_is_ok.,17622
2020-05-19_19:03:07,200,MP051919032700_to_Average_95840_is_ok.,95840
2020-05-19_19:03:07,200,
2020-05-19_19:03:07,200,RJ0515_to_System__to_66638_is_nok.,66638
  • _([0-9]{5})_ matches five digits between the _
  • (.*)$ matches til the end of line
  • _\1_\2,\1 restores the original part (_\1_\2) and then appends ,\1

Your sample data in the question had trailing space characters. If this also is the case in your live data use

sed -E 's/_([0-9]{5})_(.*\.)[[:blank:]]+$/_\1_\2,\1/' file

instead to strip trailing spaces.

3
  • Thanks for the quick response. It was very helpful as I was stuck on this from morning. – amit10504 May 25 '20 at 15:42
  • 1
    That would find 5 digit numbers surrounded by _ anywhere in the line, not just in the 3rd field, and it wouldn't find 5 digit numbers at the start or end of the 3rd field. Are those both OK? It's also worth saying that it requires a sed that supports -E, e.g. GNU sed, but won't work with all POSIX seds. – Ed Morton May 25 '20 at 16:14
  • @EdMorton There is currently nothing in the question which would indicate that the pattern won't match the input (but yes, it won't work for the cases you mention). Without a full specification of the input it's hard to prove the correctness of any solution (the 3rd field could include \, for instance as part of the text). – nohillside May 25 '20 at 17:22
4

You could use awk's match - something like

$ awk -F, 'BEGIN{OFS=FS} match($3,/_[0-9]{5}_/){$(NF+1) = substr($3,RSTART+1,RLENGTH-2)} 1' file.csv
2020-05-19_19:03:07,210,HR051919040_to_17622_is_ok.,17622
2020-05-19_19:03:07,200,MP051919032700_to_Average_95840_is_ok.,95840
2020-05-19_19:03:07,200,
2020-05-19_19:03:07,200,RJ0515_to_System__to_66638_is_nok.,66638

Including the leading and trailing underscores in the match and then trimming them with the substr ensures you don't match a sequence of 5 digits elsewhere in the field.

2
  • Thanks!!! this is also correct. – amit10504 May 25 '20 at 15:44
  • 1
    That's less fragile and more portable than the currently accepted sed solutions but still wouldn't find 5 digit numbers at the start or end of the 3rd field. Is that OK? – Ed Morton May 25 '20 at 16:16
1
$ perl -nle 'print "$_,",/_(\d{5})_/' ex
2020-05-19_19:03:07,210,HR051919040_to_17622_is_ok.,17622
2020-05-19_19:03:07,200,MP051919032700_to_Average_95840_is_ok.,95840
2020-05-19_19:03:07,200,,
2020-05-19_19:03:07,200,RJ0515_to_System__to_66638_is_nok.,66638

For each line in input perl -nle

  • print it and a "," print "$_,"
  • and also the number if present in the line /_(\d{5})_/

(we may want to tune the regular expression do deal extra constrains)

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.