Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Rows to columns transposing and reformating.

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Rows to columns transposing and reformating.
# 1  
Old 11-28-2009
Rows to columns transposing and reformating.
----File attached.

Input file
===========
Code:
COL_1 <IP Add 1> 
COL_2 <Service1> 
COL_3 <ABCDEFG>
COL_4 <IP ADD:PORT> 
COL_4 <IP ADD:PORT>
 
COL_1 <IP Add 2> 
COL_2 <Service2>
COL_2 <Service3>
COL_2 <Service4>
COL_3 <AAAABBB>
COL_4 <IP ADD:PORT> 
COL_4 <IP ADD:PORT> 
COL_4 <IP ADD:PORT> 
COL_4 <IP ADD:PORT>

Output :

Code:
COL_1        COL_2           COL_3         COL_4
<IP Add 1> <Service1>    <ABCDEFG>  <IP ADD:PORT>
                                                     
<IP Add 2> <Service2>    <AAAABBB>  <IP ADD:PORT>


There will be only one entry in the COL_1 & COL_3 columns but the number of items on the COL_2 & COL_4 may vary.
The name of the column headers will be repeated throughout the file like COL_1/COL_2/COL_3/COL_4 in the row format.
I need to have these headers only once as column headers and append their respective data below.
I did try for the last couple of days by using sed/awk but not able to achieve this output.
I have also searched this forum but no relevant topics found.
I would be grateful , if someone could help out regarding this.

Shout if you need some more details...i will be online trackng this post for the next 12 hrs..Smilie

Cheers & Happy Thanksgiving folks.
contents.txt (620 Bytes) 
Last edited by bluethunder; 11-28-2009 at 02:02 PM.. Reason: Use code tags, please!
# 2  
Old 11-28-2009
Edit: Inserted for clarity after thread splitting.


Quote:
Originally Posted by radoulov
Quote:
Originally Posted by bluethunder
[...]
There will be only one entry in the COL_1 & COL_3 columns but the number of items on the COL_2 & COL_4 may vary.
[...]
And you want only the first entry for col_2/4 per record?
Yes. I need only the first entry as the rest of the entries are just repeated ones..

pasting the actual input file for your reference.

Code:
VIRTUAL <192.168.1.1> UNIT 1
SERVICE <8181>
POOL <TestPool1>
MEMBER <192.168.1.2:8181>
MEMBER <192.168.1.3:8181>
VIRTUAL <192.168.2.1> UNIT 1
SERVICE <8182>
SERVICE <8183>
SERVICE <8184>
POOL <TestPool2>
MEMBER <192.168.2.2:8182>
MEMBER <192.168.2.3:8183>
MEMBER <192.168.2.4:8184>

the output should be like this.

Code:
VIRTUAL SERVICE POOL MEMBER
192.168.1.1 8181 TestPool1 192.168.1.2:8181
192.168.1.3:8181//this should be under MEMBER column
 
192.168.2.1 8182 TestPool2 192.168.2.2:8182
8183//this should be under SERVICE column 192.168.2.3:8183//this should be under MEMBER column
8184//this should be under SERVICE column 192.168.2.3:8184//this should be under MEMBER column


Thanks for your prompt reply.

cheers.

---------- Post updated at 02:07 PM ---------- Previous update was at 10:54 AM ----------

Can someone help on this ...Smilie
Last edited by radoulov; 11-29-2009 at 09:03 AM.. Reason: added code tags
# 3  
Old 11-28-2009
A bit straightforward:
Code:
awk -F '[><]' '/^VIRTUAL/{V[i++]=$2}
               /^SERVICE/{S[j++]=$2}
               /^POOL/   {P[k++]=$2}
               /^MEMBER/ {M[l++]=$2}
               END       {print "VIRTUAL         SERVICE POOL       MEMBER" ;
                         for (i in V) printf "%-15s %-7s %-10s %s\n",V[i],S[i],P[i],M[i]}' infile

Output:
Code:
VIRTUAL         SERVICE POOL       MEMBER
192.168.1.1     8181    TestPool1  192.168.1.2:8181
192.168.2.1     8182    TestPool2  192.168.1.3:8181

# 4  
Old 11-28-2009
Thanks for the simple code
Quote:
Output:
Code:
VIRTUAL         SERVICE POOL       MEMBER
192.168.1.1     8181    TestPool1  192.168.1.2:8181
192.168.2.1     8182    TestPool2  192.168.1.3:8181

Thanks for the simple code. But I am not able to obtain the output.
Expected output is little different.
INPUT as follows. It doesnt have any <> symbols.
Code:
VIRTUAL 192.168.1.1 
SERVICE 8181
POOL TestPool1
MEMBER 192.168.1.2:8181
MEMBER 192.168.1.3:8181
VIRTUAL 192.168.2.1 
SERVICE 8182
SERVICE 8183
SERVICE 8184
POOL TestPool2
MEMBER 192.168.2.2:8182
MEMBER 192.168.2.3:8183
MEMBER 192.168.2.4:8184

OUTPUT as follows.

Code:
 
VIRTUAL       SERVICE     POOL         MEMBER
192.168.1.1    8181      TestPool1   192.168.1.2:8181
                                     192.168.1.3:8181 
192.168.2.1    8182      TestPool2   192.168.1.3:8182
               8183                  192.168.1.3:8183
               8184                  192.168.1.3:8184

The entries in column 2/4 may be one or many but the column 1/3 will always have only one entry. Hope this clears the air.Smilie
Phew it took one hell of a time to get used to this forum posting / formatting Smilie

tks
Last edited by radoulov; 11-29-2009 at 09:04 AM.. Reason: to make it clear for forummers; radoulov - added code tags
# 5  
Old 11-28-2009
OK, small adjustment. See if this works:
Code:
awk -F '[><]' '/^VIRTUAL/{i=j=k=l;V[i++]=$2}
               /^SERVICE/{S[j++]=$2}
               /^POOL/   {P[k++]=$2}
               /^MEMBER/ {M[l++]=$2}
               END       {print "VIRTUAL         SERVICE POOL       MEMBER" ;
                         for (i in M) printf "%-15s %-7s %-10s %s\n",V[i],S[i],P[i],M[i]}' infile

output:
Code:
VIRTUAL         SERVICE POOL       MEMBER
192.168.1.1     8181    TestPool1  192.168.1.2:8181
                                   192.168.1.3:8181
192.168.2.1     8182    TestPool2  192.168.2.2:8182
                8183               192.168.2.3:8183
                8184               192.168.2.4:8184

# 6  
Old 11-28-2009
We are half way thro..
I get an output like this.Smilie the data for each rows are juggled..

Code:
 
VIRTUAL         SERVICE POOL       MEMBER
                8184               192.168.2.4:8184
192.168.1.1     8181    TestPool1  192.168.1.2:8181
                                   192.168.1.3:8181
192.168.2.1     8182    TestPool2  192.168.2.2:8182
                8183               192.168.2.3:8183

BTW there are no >< symbols in the input file.

this is the actual input.
Code:
VIRTUAL 192.168.1.1 
SERVICE 8181
POOL TestPool1
MEMBER 192.168.1.2:8181
MEMBER 192.168.1.3:8181
VIRTUAL 192.168.2.1 
SERVICE 8182
SERVICE 8183
SERVICE 8184
POOL TestPool2
MEMBER 192.168.2.2:8182
MEMBER 192.168.2.3:8183
MEMBER 192.168.2.4:8184

Last edited by radoulov; 11-29-2009 at 09:05 AM.. Reason: added code tags
# 7  
Old 11-28-2009
But you posted this originally:
Quote:
VIRTUAL <192.168.1.1> UNIT 1
SERVICE <8181>
POOL <TestPool1>
MEMBER <192.168.1.2:8181>
MEMBER <192.168.1.3:8181>
VIRTUAL <192.168.2.1> UNIT 1
SERVICE <8182>
SERVICE <8183>
SERVICE <8184>
POOL <TestPool2>
MEMBER <192.168.2.2:8182>
MEMBER <192.168.2.3:8183>
MEMBER <192.168.2.4:8184>
Alright, without the brackets:

Code:
awk '/^VIRTUAL/{i=j=k=l;V[i++]=$2}
     /^SERVICE/{S[j++]=$2}
     /^POOL/   {P[k++]=$2}
     /^MEMBER/ {M[l++]=$2}
     END       {print "VIRTUAL         SERVICE POOL       MEMBER" ;
                for (i in M) printf "%-15s %-7s %-10s %s\n",V[i],S[i],P[i],M[i]}' infile

Output:
Code:
VIRTUAL         SERVICE POOL       MEMBER
192.168.1.1     8181    TestPool1  192.168.1.2:8181
                                   192.168.1.3:8181
192.168.2.1     8182    TestPool2  192.168.2.2:8182
                8183               192.168.2.3:8183
                8184               192.168.2.4:8184
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Transposing rows to columns with multiple similar lines

Hi, I am trying to transpose rows to columns for thousands of records. The problem is there are records that have the same lines that need to be separated. the input file as below:- ID 1A02_HUMAN AC P01892; O19619; P06338; P10313; P30444; P30445; P30446; P30514; AC Q29680; Q29837;... (2 Replies)
Discussion started by: redse171
2 Replies

2. Shell Programming and Scripting

transposing lines to columns

Okay folks, here's a question. I tried searching but couldn't find exactly what I needed. I have a text file (excerpt below). This text file is an extract I did from several hundred pages of datasheets using grep so I could look only at the site history for each site. The problem is that... (2 Replies)
Discussion started by: jbrandt1979
2 Replies

3. Shell Programming and Scripting

Transposing rows and columns (pivoting) using shell scripting

Here is the contents of an input file. A,1,2,3,4 10,aaa,bbb,ccc,ddd 11,eee,fff,ggg,hhh 12,iii,jjj,lll,mmm 13,nnn,ooo,ppp I wanted the output to be A 10 1 aaa 10 2 bbb 10 3 ccc 10 4 ddd 11 1 eee 11 2 fff 11 3 ggg 11 4 hhh ..... and so on How to do it in ksh... (9 Replies)
Discussion started by: ksatish89
9 Replies

4. Shell Programming and Scripting

transposing columns into rows

Hi, I need to transpose columns of my files into rows and save it as individual files. sample contents of the file below. 0.9120 0.7782 0.6959 0.6904 0.6322 0.8068 0.9082 0.9290 0.7272 0.9870 0.7648 0.8053 0.8300 0.9520 0.8614 0.6734 0.7910 0.6413 0.7126 0.7364 0.8491 0.8868 0.7586 0.8949... (8 Replies)
Discussion started by: ida1215
8 Replies

5. Shell Programming and Scripting

Help for a Perl newcomer! Transposing data from columns to rows

I have to create a Perl script which will transpose the data output from my experiment, from columns to rows, in order for me to analyse the data. I am a complete Perl novice so any help would be greatly appreciated. The data as it stands looks like this: Subject Condition Fp1 ... (12 Replies)
Discussion started by: Sarah_W
12 Replies

6. Shell Programming and Scripting

Transposing Repeated Rows to Columns.

I have 1000s of these rows that I would like to transpose to columns. However I would like the transpose every 3 consecutive rows to columns like below, sorted by column 3 and provide a total for each occurrences. Finally I would like a grand total of column 3. 21|FE|41|0B 50\65\78 15... (2 Replies)
Discussion started by: ravzter
2 Replies

7. Shell Programming and Scripting

awk, string as record separator, transposing rows into columns

I'm working on a different stage of a project that someone helped me address elsewhere in these threads. The .docs I'm cycling through look roughly like this: 1 of 26 DOCUMENTS Copyright 2010 The Age Company Limited All Rights Reserved The Age (Melbourne, Australia) November 27, 2010... (9 Replies)
Discussion started by: spindoctor
9 Replies

8. Shell Programming and Scripting

Transposing rows into columns

I have a file like the one given below P1|V1|V2 P1|V1|V3 P1V1|V2 P2|V1|V4 P2|V2|V6 P2|V1|V4 I want it convert to P1|V1|V2|V2|V3 P2|V1|V4|V2|V6 2nd and 3rd column should be considered as together and so the tird row is duplicate Any ideas? (3 Replies)
Discussion started by: prasperl
3 Replies

9. Shell Programming and Scripting

Transposing columns with awk

I want a sweet simple time efficient awk script in online which gets output 001_r 0.0265185 0.0437049 0.0240642 0.0310264 0.0200482 0.0146746 0.0351344 0.0347856 0.036119 1.49 firstcoloumnvalue allvaluesof 'c' in one row 001_r : 002_r c: 0.0265185 N: 548 001_r : 007_r c:... (5 Replies)
Discussion started by: phoenix_nebula
5 Replies

10. Shell Programming and Scripting

awk - reformating rows into columns

looking to do the following... What the data looks like server1 02/01/2008 groups 10 server1 03/01/2008 groups 15 server1 04/01/2008 groups 20 server2 02/01/2008 users 50 server2 03/01/2008 users 75 server2 04/01/2008 users 100 server2 04/01/2008 users 125 What I would like the... (1 Reply)
Discussion started by: jmd2004
1 Replies
Login or Register to Ask a Question