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.
# 15  
Old 11-29-2009
I believe it could be re-written all in AWK but you should really post a real data simple (masking the sensitive data).

Quote:
How can I make sure that there is only one space between the col 1 & 2 ( in my input file) using SED.
As I said, we need to see the real data,
this may give you an idea tough (assuming col1 does not contain spaces):

Code:
% print 'a    b'                           
a    b
% print 'a    b' | sed 's/  */ /'
a b

# 16  
Old 11-29-2009
Files attached.
Here we go!!!

I am attaching the input files as you have requested.

file 1 : Raw input file from the system.
file 2 : File after running my sed commands.
file 3 : File after running your awk commands.

file4 : sed commands used.

I have updates each file with some comments at the end of the file.
The spaces in between column 1 & 2 of the raw input files are not uniform..at some places its one space and its more than one space in other places.

let me know if i have messed up with sed commands.
Raw Input.txt (10.5 KB) 
SED Output.txt (757 Bytes) 
AWK Output.txt (970 Bytes) 
SED Commands.txt (839 Bytes) 
Last edited by bluethunder; 11-29-2009 at 02:17 PM.. Reason: add comment on spaces.
# 17  
Old 11-29-2009
Using SED output.txt:
Code:
awk '/^VIRTUAL/{j=k=l=i;V[i++]=$2}
     /^SERVICE/{S[j++]=$2} j>i{i++}
     /^POOL/   {P[k++]=$2} k>i{i++}
     /^MEMBER/ {M[l++]=$2} l>i{i++}
     END       {print "VIRTUAL         SERVICE  POOL                  MEMBER" ;
                for (j=0;j<i;j++) printf "%-15s %-8s %-21s %s\n",V[j],S[j],P[j],M[j]}' 'SED Output.txt'

output:
Code:
VIRTUAL         SERVICE  POOL                  MEMBER
ANY                      Default_router        10.65.8.253:*
192.168.1.1
192.168.1.2
192.168.1.3
192.168.1.4
192.168.1.5
192.168.1.5
192.168.1.6
192.168.1.7
192.168.1.8     80       TermWiki-stag         192.168.1.2:80
                                               192.168.1.2:80
192.168.1.9     80       IIS-UAT-WebFarm-V4    192.168.1.2:80
                                               192.168.1.2:80
192.168.1.10    80       Distribution-pac-uat  192.168.1.2:80
                                               192.168.1.2:80



---------- Post updated at 20:27 ---------- Previous update was at 20:15 ----------

Using RAW input.txt:
Code:
sed 's/[^A-Z]*//' 'RAW Input.txt'|
awk '/^VIRTUAL/{j=k=l=i;V[i++]=$2}
     /^SERVICE/{S[j++]=$2} j>i{i++}
     /^POOL/   {P[k++]=$2} k>i{i++}
     /^MEMBER/ {M[l++]=$2} l>i{i++}
     END       {print "VIRTUAL         SERVICE  POOL                  MEMBER" ;
                for (j=0;j<i;j++) printf "%-15s %-8s %-21s %s\n",V[j],S[j],P[j],M[j]}'

output:
Code:
VIRTUAL         SERVICE  POOL                  MEMBER
ANY                      Default_router        10.65.8.253:*
192.168.1.1
192.168.1.2
192.168.1.3
192.168.1.4
192.168.1.5
192.168.1.5
192.168.1.6
192.168.1.7
192.168.1.8     80       TermWiki-stag         192.168.1.2:80
                                               192.168.1.2:80
192.168.1.9     80       IIS-UAT-WebFarm-V4    192.168.1.2:80
                                               192.168.1.2:80
192.168.1.10    80       Distribution-pac-uat  192.168.1.2:80
                                               192.168.1.2:80

Last edited by Scrutinizer; 11-29-2009 at 03:34 PM..
# 18  
Old 11-29-2009
Looks perfect scrutinizer Smilie...but the only missing part is the text " Default router" in the column 3 .

do you mind explaining this simple code .Smilie
# 19  
Old 11-29-2009
Debian
Plain awk against raw input
Code:
awk '{sub(/[^A-Z]*/,"")}
     /^VIRTUAL/{j=k=l=i;V[i++]=$2}
     /^SERVICE/{S[j++]=$2} j>i{i++}
     /^POOL/   {P[k++]=$2} k>i{i++}
     /^MEMBER/ {M[l++]=$2} l>i{i++}
     END       {print "VIRTUAL         SERVICE  POOL                  MEMBER" ;
                for (j=0;j<i;j++) printf "%-15s %-8s %-21s %s\n",V[j],S[j],P[j],M[j]}' 'RAW Input.txt'

Output:
Code:
VIRTUAL         SERVICE  POOL                  MEMBER
ANY                      Default_router        10.65.8.253:*
192.168.1.1
192.168.1.2
192.168.1.3
192.168.1.4
192.168.1.5
192.168.1.5
192.168.1.6
192.168.1.7
192.168.1.8     80       TermWiki-stag         192.168.1.2:80
                                               192.168.1.2:80
192.168.1.9     80       IIS-UAT-WebFarm-V4    192.168.1.2:80
                                               192.168.1.2:80
192.168.1.10    80       Distribution-pac-uat  192.168.1.2:80
                                               192.168.1.2:80



---------- Post updated at 20:57 ---------- Previous update was at 20:40 ----------
Quote:
Originally Posted by bluethunder
Looks perfect scrutinizer Smilie...but the only missing part is the text " Default router" in the column 3 .

do you mind explaining this simple code .Smilie
Hi bluethunder, I like to keep things simple Smilie
I see "Default_router" in column 3. Is that not OK?

I'll explain the last awk:

Code:
For every line in the input file:
1. Delete any character before the first capital letter
2. line starts with VIRTUAL: synchronize counters; put field 2 in array V; increment line counter i
3. line starts with SERVICE: put field 2 in array S; increment line counter j, if it is more than i then increment i
4. line starts with POOL:    put field 2 in array P; increment line counter k, if it is more than i then increment i
5. line starts with MEMBER:  put field 2 in array M; increment line counter l, if it is more than i then increment i

After the last line is read:
6. Print Header
7. for j=0 until i-1 use j as the index for V S P and M and print the content using printf formatting.

Because the script uses only certain lines and ignores all others, there is no need to discard all the lines that do not get used beforehand. Hence all the filtering sed statements from "SED Commands.txt" are no longer required.
Last edited by Scrutinizer; 11-29-2009 at 04:50 PM..
# 20  
Old 11-29-2009
I understood the printf line perfectly but the others were confusing & now you have cleared it for me.Smilie

Yes, I can see only one entry of Default_Router in the col 3 , where as it should be available for the rows 2-7 across column three.

Code:
 
VIRTUAL         SERVICE  POOL                  MEMBER
ANY                      Default_router        10.65.8.253:*
192.168.1.1              Default_router
192.168.1.2              Default_router
192.168.1.3              Default_router
192.168.1.4              Default_router
192.168.1.5              Default_router
192.168.1.5              Default_router
192.168.1.6              Default_router
192.168.1.7              Default_router
192.168.1.8     80       TermWiki-stag         192.168.1.2:80
                                               192.168.1.2:80
192.168.1.9     80       IIS-UAT-WebFarm-V4    192.168.1.2:80
                                               192.168.1.2:80
192.168.1.10    80       Distribution-pac-uat  192.168.1.2:80



---------- Post updated at 03:25 PM ---------- Previous update was at 03:21 PM ----------

You can ignore my comment...there was no Default_Router for the next rows in the input file...Smilie
# 21  
Old 12-07-2009
Enhancement on its way
I have got an another set of input files, which is similar to the older ones..but with additional column and data.
I will try my hand on it for the next couple of days based on Scrutinizer's explanation.
Will come back to guys , if it fails.Smilie
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