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
0

1 Answer 1

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
5
  • @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, 2016 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, 2016 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, 2016 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, 2016 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, 2016 at 7:38

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .