Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Split text separated by ; in a column into multiple columns

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Split text separated by ; in a column into multiple columns
# 1  
Old 09-06-2013
Split text separated by ; in a column into multiple columns
Hi,

I need help to split a long text in a column which is separated by ; and i need to print them out in multiple columns. My input file is tab-delimited and has 11 columns as below:-

Code:
aRg02004	21452	asdfwf	21452	21452	4.6e-29	5e-29	-1	3	50	ffg|GGD|9009 14101.10 High class -node. ; ffg|GGD|969 101.10 no class -inode. ; ffg|GGD|1149 14101.10 High class RR-node.. ; ffg|GGD|9225 414101.10 class -node.. ; ffg|GGD|11457 2001.10 High class -node. ; ffg|GGD|778 4514.40 loss class -node.. ; 
aRg530	996552	ssawd	996552	996552	4e-21	 1e-9	-1	2	50	ffg|GGD|900 22101.10 High class -node. ;ffg|GGD|840 2241.7 iR class -node.

what i need is to split the last column ($11) to split into multiple columns based on ";" that it has and the output file should display columns $1 and $11 , which should become like this:-

Code:
aRg02004      ffg|GGD|9009 14101.10 High class -node.
aRg02004      ffg|GGD|969 101.10 no class -inode
aRg02004      ffg|GGD|1149 14101.10 High class RR-node..
aRg02004      ffg|GGD|9225 414101.10 class -node.. 
aRg02004      ffg|GGD|11457 2001.10 High class -node.
aRg02004      ffg|GGD|778 4514.40 loss class -node..

aRg530         ffg|GGD|900 22101.10 High class -node. 
aRg530         ffg|GGD|840 2241.7 iR class -node.

i did write an awk code to do this but it only print out the first part of the text in that column only for each entry id ($1). My code as below:-

Code:
awk -F ';' '{print $1 "\t" $11 }' input.txt  > output.txt

Appreciate your kind help on this. Thanks.
Moderator's Comments:
Mod Comment Please use CODE tags; not HTML tags when displaying sample input data, output data, and code segments.
Last edited by Don Cragun; 09-06-2013 at 07:54 PM.. Reason: Change HTML tags to CODE tags.
# 2  
Old 09-06-2013
Try:
Code:
awk -F"\t" '{n=split($11,a,";");for (i=1;i<=n;i++) print $1"\t"a[i]}' input.txt > output.txt

This User Gave Thanks to bartus11 For This Post:
redse171
# 3  
Old 09-06-2013
Hello,

a sed version:
Code:
sed 's/\([^\t]*\t\)\([^\t]*\t\)\{9\}/\1/;s/; *$/\n/;:bcl;s/^\(\([^\t]*\)\([^;]*\)\); */\1\n\2\t/;tbcl' input.txt

Regards.
This User Gave Thanks to disedorgue For This Post:
redse171
# 4  
Old 09-06-2013
Hi Both,

Thank you so much! Both codes work great. But, If you dont mind, can u guys please explain about the codes? Thanks. Smilie
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Using awk to split a column into two columns

Hi, I am trying to split the following output into two columns, where each column has Source: Destination: OUTPUT TO FILTER $ tshark -r Capture_without_mtr.pcap -V | awk '/ (Source|Destination): /' | more Source: x.x.x.x Destination: x.x.x.x Source:... (2 Replies)
Discussion started by: sand1234
2 Replies

2. Shell Programming and Scripting

awk split columns to row after N number of column

I want to split this with every 5 or 50 depend on how much data the file will have. And remove the comma on the end Source file will have 001,0002,0003,004,005,0006,0007,007A,007B,007C,007E,007F,008A,008C Need Output from every 5 tab and remove the comma from end of each row ... (4 Replies)
Discussion started by: ranjancom2000
4 Replies

3. Shell Programming and Scripting

Join columns across multiple lines in a Text based on common column using BASH

Hello, I have a file with 2 columns ( tableName , ColumnName) delimited by a Pipe like below . File is sorted by ColumnName. Table1|Column1 Table2|Column1 Table5|Column1 Table3|Column2 Table2|Column2 Table4|Column3 Table2|Column3 Table2|Column4 Table5|Column4 Table2|Column5 From... (6 Replies)
Discussion started by: nv186000
6 Replies

4. UNIX for Dummies Questions & Answers

Split 1 column into numerous columns based on patterns

Hi, I have a text file 'Item_List.txt' containing only 1 column. This column lists different products, each separated by the same generic string header "NEW PRODUCT, VERSION 1.1". After this the name of the product is given, then a delimiter string "PRODUCT FIELD", and then the name of the... (11 Replies)
Discussion started by: mmab
11 Replies

5. Shell Programming and Scripting

How to split all columns into multiple columns?

Hi, all. How can I split all columns into multiple columns separated by tab? Input: qq ee TT 12 m1 aa gg GG 34 2u zz dd hh 56 4h ww cc JJ 78 5y ss ff kk 90 j8 xx pp mm 13 p0 Output: q q e e T T 1 2 m 1 a a g g G G 3 4 2 u z z d d h h 5 6 4 h w w c c J J 7 8 5 y (8 Replies)
Discussion started by: huiyee1
8 Replies

6. UNIX for Dummies Questions & Answers

Split a column into multiple columns at certain character count

Hey everyone, I have an issue with a client that is passing me a list of values in one column, and occasionally the combination of all the values results in more than an 255 character string. My DB has a 255 character limit, so I am looking to take the column (comma delimited file), and if it... (1 Reply)
Discussion started by: perekl
1 Replies

7. Shell Programming and Scripting

Deleting a column in multiple files that are comma separated

Hi, I have a directory that contains say 100 files named sequencially like input_1.25_50_C1.txt input_1.25_50_C2.txt input_1.25_50_C3.txt input_1.25_50_C4.txt .. .. .. input_1.25_50_C100.txt an example of the content in each of the file is: "NAME" "MEM.SHIP" "cgd1_10" "cgd1_10"... (9 Replies)
Discussion started by: Lucky Ali
9 Replies

8. Shell Programming and Scripting

split one column into multiple columns

hey, i have the following data: 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 (7 Replies)
Discussion started by: zaneded
7 Replies

9. UNIX for Dummies Questions & Answers

split one column into multiple columns

hey guys... Im looking to do the following: 1 2 3 4 5 6 7 8 9 Change to: 1 4 7 2 5 8 3 6 9 Did use | perl -lpe'$\=$.%3?$":"\n"' , but it doesnt give me the matrix i want. (3 Replies)
Discussion started by: zaneded
3 Replies

10. Web Development

split the fields in a column into 3 columns

Have a column "address" which is combination of city, region and postal code like. Format is : city<comma><space>region<space>postal code abc, xyz 123456 All these three city, region and postal code are not mandatory. There can be any one of the above. In that case a nell... (2 Replies)
Discussion started by: rakshit
2 Replies
Login or Register to Ask a Question