1

I have a CSV formatted multi lines file with 5 columns (fields). I need to unify and corrected the corrupted first column which has lots of different formats of the code I need to unify. The complete final format of my code for the first column should be 00AB[0-9][0-9][0-9][0-9][0-9] which [0-9] could be any number such as 00AB21345. The first four digits i.e. 00AB should be always as it is. but the 5 digits after that (i.e.[0-9][0-9][0-9][0-9][0-9]) could be any number and if there would be any > 5 digits , the missing digits from far left should be substitute with 0.

Example  <111> --> <00AB00111> ; or <1111> --> <00AB01111>. 

To have an example let's say I have a following file :

111     xx  yy  zzz ddd
1111    xx  yy  zzz ddd
11111   xx  yy  zzz ddd
A111    xx  yy  zzz ddd
A1111   xx  yy  zzz ddd
A11111  xx  yy  zzz ddd
AB111   xx  yy  zzz ddd
AB1111  xx  yy  zzz ddd
AB11111 xx  yy  zzz ddd
0A111   xx  yy  zzz ddd
0A1111  xx  yy  zzz ddd
0A11111 xx  yy  zzz ddd
0AB111  xx  yy  zzz ddd
0AB1111 xx  yy  zzz ddd
0AB11111 xx yy  zzz ddd
00A111  xx  yy  zzz ddd
00A1111 xx  yy  zzz ddd
00A11111xx  yy  zzz ddd
00AB111 xx  yy  zzz ddd
00AB1111 xx yy  zzz ddd
0AB11111 xx yy  zzz ddd
00AB12344   xx  yy  zzz ddd
00AB34527   xx  yy  zzz ddd
00AB56278   xx  yy  zzz ddd
00AB98902   xx  yy  zzz ddd

To cover all the possible scenario I made up the following very long awk script. The bold format represent the potential scenario could be find in my file which needed to be corrected.
My request, dose any one know any awk script to address this in much smaller script? If so, would you explain it to me in details to learn :)

##111 Awk -F',' '{if($0~/[0-9][0-9][0-9]/){print "001AB00"suBstr($1,1,3)","$2","$3","$4","$5;}else{print $1","$2","$3","$4","$5;}}' SC3.csv > y1.csv

##1111
Awk -F',' '{if($0~/[0-9][0-9][0-9][0-9]/){print "001AB"suBstr($1,1,4)","$2","$3","$4","$5;}else{print $1","$2","$3","$4","$5;}}' y1.csv > y2.csv
##11111
Awk -F',' '{if($0~/[0-9][0-9][0-9][0-9][0-9]/){print "001AB" suBstr($1,1,5)","$2","$3","$4","$5;}else{print $1","$2","$3","$4","$5;}}' y2.csv > y3.csv
##A111
Awk -F',' '{if($0~/[A-Z][0-9][0-9][0-9]/){print "001"suBstr($1,1,1) "B00"suBstr($1,2,4)","$2","$3","$4","$5;}else{print $1","$2","$3","$4","$5;}}' y3.csv > y4.csv
##A1111
Awk -F',' '{if($0~/[A-Z][0-9][0-9][0-9][0-9]/){print "001"suBstr($1,1,1) "B0" suBstr($1,2,5)","$2","$3","$4","$5;}else{print $1","$2","$3","$4","$5;}}' y4.csv > y5.csv
##A11111
Awk -F',' '{if($0~/[A-Z][0-9][0-9][0-9[0-9][0-9]/){print "001"suBstr($1,1,1) "B" suBstr($1,2,6)","$2","$3","$4","$5;}else{print $1","$2","$3","$4","$5;}}' y5.csv > y6.csv
##AB111
Awk -F',' '{if($0~/[A-Z][A-Z][0-9][0-9][0-9]/){print "001"suBstr($1,1,2) "00" suBstr($1,3,5)","$2","$3","$4","$5;}else{print $1","$2","$3","$4","$5;}}' y6.csv > y7.csv
##AB1111
Awk -F',' '{if($0~/[A-Z][A-Z][0-9][0-9][0-9][0-9]/){print "001"suBstr($1,1,2)"0" suBstr($1,3,6)","$2","$3","$4","$5;}else{print $1","$2","$3","$4","$5;}}' y7.csv > y8.csv
##AB11111
Awk -F',' '{if($0~/[A-Z][A-Z][0-9][0-9][0-9][0-9][0-9]/){print "001"suBstr($1,1,7)","$2","$3","$4","$5;}else{print $1","$2","$3","$4","$5;}}' y8.csv > y9.csv
##1A111
Awk -F',' '{if($0~/[0-9][A-Z][0-9][0-9][0-9]/){print "00"suBstr($1,1,2) ",B00" suBstr($1,3,5) ","$2","$3","$4","$5;}else{print $1","$2","$3","$4","$5;}}' y9.csv > y10.csv
##1A1111  
Awk -F',' '{if($0~/[0-9][A-Z][0-9][0-9][0-9][0-9]/){print "00"suBstr($1,1,1) "B0" suBstr($1,3,6) ","$2","$3","$4","$5;}else{print $1","$2","$3","$4","$5;}}' y10.csv > y11.csv
##1A11111
Awk -F',' '{if($0~/[0-9][A-Z][0-9][0-9][0-9][0-9][0-9]/){print "00"suBstr($1,1,2) "B" suBstr($1,3,7) ","$2","$3","$4","$5;}else{print $1","$2","$3","$4","$5;}}' y11.csv > y12.csv
##1AB111
Awk -F',' '{if($0~/[0-9][A-Z][A-Z][0-9][0-9][0-9]/){print "00"suBstr($1,1,1) suBstr($1,1,3)"00" suBstr($1,4,6) ","$2","$3","$4","$5;}else{print $1","$2","$3","$4","$5;}}' y12.csv > y13.csv
##1AB1111
Awk -F',' '{if($0~/[0-9][A-Z][A-Z][0-9][0-9][0-9][0-9]/){print "00" suBstr($1,1,3) "0" suBstr($1,4,7) ","$2","$3","$4","$5;}else{print $1","$2","$3","$4","$5;}}' y13.csv > y14.csv
##1AB11111
Awk -F',' '{if($0~/[0-9][A-Z][A-Z][0-9][0-9][0-9][0-9][0-9]/){print "00" suBstr($1,1,8) ","$2","$3","$4","$5;}else{print $1","$2","$3","$4","$5;}}' y14.csv > y15.csv
##11A111
Awk -F',' '{if($0~/[0-9][0-9][A-Z][0-9][0-9][0-9]/){print "0" suBstr($1,1,3)"B00" suBstr($1,4,6) ","$2","$3","$4","$5;}else{print $1","$2","$3","$4","$5;}}' y15.csv > y16.csv
##11A1111
Awk -F',' '{if($0~/[0-9][0-9][A-Z][0-9][0-9][0-9]/){print "0" suBstr($1,1,3)"B0" suBstr($1,4,7) ","$2","$3","$4","$5;}else{print $1","$2","$3","$4","$5;}}' y16.csv > y17.csv
##11A11111
Awk -F',' '{if($0~/[0-9][0-9][A-Z][0-9] [0-9][0-9][0-9]/){print "0" suBstr($1,1,3)"B" suBstr($1,4,8) ","$2","$3","$4","$5;}else{print $1","$2","$3","$4","$5;}}' y17.csv > y18.csv
##11AB111
Awk -F',' '{if($0~/[0-9][0-9] [A-Z][[A-Z][0-9][0-9][0-9]/){print "0" suBstr($1,1,4)"00" suBstr($1,5,7) ","$2","$3","$4","$5;}else{print $1","$2","$3","$4","$5;}}' y18.csv > y19.csv
##11AB1111
Awk -F',' '{if($0~/[0-9][0-9] [A-Z][[A-Z][0-9][0-9][0-9][0-9]/){print "0" suBstr($1,1,4)"0" suBstr($1,5,8) ","$2","$3","$4","$5;}else{print $1","$2","$3","$4","$5;}}' y19.csv > y20.csv
##1AB11111
Awk -F',' '{if($0~/[0-9][0-9] [A-Z][[A-Z][0-9][0-9][0-9][0-9][0-9]/){print "0" suBstr($1,5,9) ","$2","$3","$4","$5;}else{print $1","$2","$3","$4","$5;}}' y20.csv > y21.csv` 
  • it's not very clear what your goal is, could you explain it more in detail? – LotoLo Nov 29 '16 at 22:27
  • You could try [0-9]{0,2}, this will match zero to two digits. Same thing for [A-Z]{1,2} (match one or two upper letters) – LotoLo Nov 29 '16 at 22:30
2

Maybe:

awk 'sub("^0?0?A?B?","",$1) && $1=sprintf("00AB%05d",$1)'

Delete any leading 00AB fragments from field 1, then convert it to 00AB followed by the rest of the number padded with zeros up to length 5.

The expression is always true so the implicit { print } action fires. The sub is always true because the regular expression is nullable: a bit sneaky! The substitution takes place even if ^0?0?A?B? matches the empty string, because that is a successful match.

  • Can You please explain to me how i can keep the other two columns in this command that you wrote? – Daniel Nov 30 '16 at 21:26
  • Did you try it? It's keeping the columns for me. Do you mean you need to preserve the exact whitespace separation of the columns? Or reformat into aligned columns? – Kaz Nov 30 '16 at 21:40
  • Hi Kaz, first thanks a lot for the explanation and awesome script. Yes, I did on my actual file. it changed very well as desired the first column in my CSV file which has two more columns in addition to the code column which we changed. but whenever I try your command while it changed perfectly the first column (my code column) but dose not keep the other two columns and only show me the first changed column! – Daniel Nov 30 '16 at 21:47
  • The data shown in the question is space separated, not CSV (broadly understood as meaning "comma-separated values"). Please update the question with a sample of your real data. Awk doesn't separate on commas by default; by default, it extracts, from each record, sequences of one or more non-blank characters, which become the fields. – Kaz Nov 30 '16 at 21:51
  • Dear Kaz, Based on your comment I realized my mistake. Since my filed are comma separated so I only add the flag "-F," after awk and the rest was your script and it worked perfectly. <pre> <code> awk -F, 'sub("^0?0?A?B?","",$1) && $1=sprintf("00AB%05d",$1)' <code><pre> – Daniel Dec 1 '16 at 14:48

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.