2

Input file

jayesh  30,20,50,60 30:20:40,60:55  A   AB,KL,CD        SM1,SM2
rahul   10,80,50,90 25:55:60,25     SGF AAAA,BCD,RTY    SM3,SM4,SM4
pravin  89,78,40,20 25:30:55,96:25  M   J               SD10,SD12
sarika  10,20,48    29:50:30,25     T   K,L             SD20,SD39

I want remove comma's from column 5 and print those words in new line which are present after the comma(Note:- each single cell of the 5th column contains many comma's but i show only few)

Expected output

jayesh  30,20,50,60      30:20:40,60:55 A   AB   SM1,SM2
jayesh  30,20,50,60      30:20:40,60:55 A   KL   SM1,SM2
jayesh  30,20,50,60      30:20:40,60:55 A   CD   SM1,SM2
rahul   10,80,50,90,120  25:55:60,25    SGF AAAA SM3,SM4,SM4
rahul   10,80,50,90,120  25:55:60,25    SGF BCD  SM3,SM4,SM4
rahul   10,80,50,90,120  25:55:60,25    SGF RTY  SM3,SM4,SM4
pravin  89,78,40,20      25:30:55,96:25 M   J    SD10,SD12
sarika  10,20,48         29:50:30,25    T   K    SD20,SD39
sarika  10,20,48         29:50:30,25    T   L    SD20,SD39

I tried the following using awk but not giving expected results. (to write code i take help from this site How to remove the comma and print the entire row again for the words which are place after the comma)

awk '{
split ($5,w5,",");
for (i in w5) 
{ print $1"\t"$2"\t"$3"\t"$4"\t"w5[i]"\t"$6";}}'

@sundeep, when i tried the following command for my input file, 5th and 6th column get mixed with each other.(i just show here total 6 columns but my file have more than 6 columns)

following output is obtained when i open the output file in excel

output

$ awk '{ split ($5,w5,","); for (i in w5) { print $1"\t"$2"\t"$3"\t"$4"\t"w5[i]"\t"$6 } }' ip.txt

jayesh  30,20,50,60 30:20:40,60:55  A   "ABSM1,SM2" 
jayesh  30,20,50,60 30:20:40,60:55  A    KL         SM1,SM2
jayesh  30,20,50,60 30:20:40,60:55  A    CD"        SM1,SM2
rahul   10,80,50,90 25:55:60,25     SGF  AAAASM3,SM4,SM4"   
rahul   10,80,50,90 25:55:60,25     SGF  BCD        SM3,SM4,SM4
rahul   10,80,50,90 25:55:60,25     SGF  RTY"       SM3,SM4,SM4
pravin  89,78,40,20 25:30:55,96:25  M    J          SD10,SD12
sarika  10,20,48    29:50:30,25     T    KSD20,SD39"    
sarika  10,20,48    29:50:30,25     T    L"         SD20,SD39
3

The awk command used by OP just has syntax issue, the "; at end of print statement

$ awk '{ split ($5,w5,","); for (i in w5) { print $1"\t"$2"\t"$3"\t"$4"\t"w5[i]"\t"$6 } }' ip.txt
jayesh  30,20,50,60 30:20:40,60:55  A   AB  SM1,SM2
jayesh  30,20,50,60 30:20:40,60:55  A   KL  SM1,SM2
jayesh  30,20,50,60 30:20:40,60:55  A   CD  SM1,SM2
rahul   10,80,50,90 25:55:60,25 SGF AAAA    SM3,SM4,SM4
rahul   10,80,50,90 25:55:60,25 SGF BCD SM3,SM4,SM4
rahul   10,80,50,90 25:55:60,25 SGF RTY SM3,SM4,SM4
pravin  89,78,40,20 25:30:55,96:25  M   J   SD10,SD12
sarika  10,20,48    29:50:30,25 T   K   SD20,SD39
sarika  10,20,48    29:50:30,25 T   L   SD20,SD39

Also, can set output field separator for a cleaner syntax, thanks @fedorqui for the suggestion

awk -v OFS='\t' '{ split ($5,w5,","); for (i in w5) { print $1,$2,$3,$4,w5[i],$6 } }' ip.txt

or

awk -v OFS='\t' '{ split ($5,w5,","); for (i in w5) { $5 = w5[i]; print } }' ip.txt


Similar solution with perl

$ perl -lane 'print join "\t", @F[0..3],$_,@F[5..$#F] foreach split /,/,$F[4]' ip.txt 
jayesh  30,20,50,60 30:20:40,60:55  A   AB  SM1,SM2
jayesh  30,20,50,60 30:20:40,60:55  A   KL  SM1,SM2
jayesh  30,20,50,60 30:20:40,60:55  A   CD  SM1,SM2
rahul   10,80,50,90 25:55:60,25 SGF AAAA    SM3,SM4,SM4
rahul   10,80,50,90 25:55:60,25 SGF BCD SM3,SM4,SM4
rahul   10,80,50,90 25:55:60,25 SGF RTY SM3,SM4,SM4
pravin  89,78,40,20 25:30:55,96:25  M   J   SD10,SD12
sarika  10,20,48    29:50:30,25 T   K   SD20,SD39
sarika  10,20,48    29:50:30,25 T   L   SD20,SD39
  • @Sundeep when tried above all awk and perl commands, i find that in output file my 6th column entries get mixed with 5th column. please can you solve this problem – rohan Nov 19 '16 at 7:27
  • also, see if you have dos style line endings for your input file... you will see ^M if it is so with cat -A inputfile – Sundeep Nov 19 '16 at 7:43
  • @Sundeep, yes my input file have dos style ending line. at each end of line, i can see ^M also i find ^I after at the end of each column. – rohan Nov 19 '16 at 12:01
  • @rohan, see if you have unix2dos command.. else you can use perl -i -pe 's|\r\n|\n|' inputfile and then use any of these commands... – Sundeep Nov 19 '16 at 12:11
  • 1
    @Sundeep, yes after removing dos style ending line with unix2dos commands, all above commands work properly. once again thanks for your help – rohan Nov 20 '16 at 7:38

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.