Split second column -Values in to multiple rows based on the ';'

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Split second column -Values in to multiple rows based on the ';'

Sumesh
Hi Team,

I have a requirement of spiting column values in rows. my source file (csv ) contain two column called ACCOUNT and TERR and values look like this.


ACCOUNT,TERR
-----------------------
110        ,;TERR1;TERR2;TERR;
111        ,;TERR10;TERR3;


Expected results:

ACCOUNT,TERR
----------------------
110       , TERR1
110       , TERR2
110       , TERR3
111       , TERR10
111       ,TERR3

ideally second column values should be extracted (those comes between ; (semi colon))

thanks for the support in advance.
 
Reply | Threaded
Open this post in threaded view
|

Re: Split second column -Values in to multiple rows based on the ';'

Guru
Administrator
$ cat file
110        ,;TERR1;TERR2;TERR;
111        ,;TERR10;TERR3;
$

Using awk:

$ awk -F, '{split($2,a,";");for(i=2;i<=length(a)-1;i++){print $1,a[i];}}' OFS=, file
110        ,TERR1
110        ,TERR2
110        ,TERR
111        ,TERR10
111        ,TERR3
$