How to Merge Multiple Columns in to Two Columns based on Column 1 Value?

Question

I have a text file in the below sample format

File.txt

Record1 20 23;
Record2 256;
Record3 45 679 98 1;

The no of columns in each line can vary. I want to change the file with only two columns based on column 1 Value. Below is the target output.

File_2_Columns.txt

Record1 20;
Record1 23;
Record2 256;
Record3 45;
Record3 679;
Record3 98;
Record3 1;

steve · Accepted Answer · 2019-04-16 21:51:19Z

4

Here's one way

awk '{for(a=2;a<=NF;a++){printf "%s %s%c\n",$1,$a,a==NF ? "" : ";"}}' File.txt >File_2_Columns.txt

Good luck with the rest of your homework ;-)

If teacher is giving extra credits for shortest answer, try

awk '{for(a=2;a<=NF;){printf"%s%c\n",$1" "$a,a++-NF?";":""}}' File.txt >File_2_Columns.txt

answered Apr 16 '19 at 21:42

steve

17.5k2 gold badges36 silver badges61 bronze badges

1

Thank you so much. Got a better idea on loops inside awk. Code makes a lot of sense. – rkatraga Apr 16 '19 at 22:02
1

... or perhaps 'BEGIN{ORS=RS=";\n"} {for(a=2; a<=NF;) print $1,$(a++)}' – steeldriver Apr 16 '19 at 22:14

add a comment |

1 Answer 1