Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Need help with a script to rearrange columns

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Need help with a script to rearrange columns
# 1  
Old 12-07-2007
Need help with a script to rearrange columns
I have a file that is semi-colon delimited and the column headers are always the same but the column number is totally random each time this file is generated. I don't have the skills to make a script for this so maybe someone can help.

I would like to be able to take this file which has over 80 columns and grab about 5-10 columns and rearrange them and put that into another file so I can run my script to get the information I need.

Just for a test here are some columns I would like out of this test file below:

Current Order:
num
date
time
org
product
rule

Would like to rearrange to this order:

rule
product
org
time
date
num

Code:
num;date;time;orig;type;action;alert;i/f_name;i/f_dir;product;log_sys_message;rule
0;21-Nov-07;23:59:00;2.2.2.2;control; ;;daemon;inbound;VPN-1 & FireWall-1;Log file has been switched to: 2007-11-21_235900.log;
1;22-Nov-07;0:53:28;3.3.3.3;log;drop;;Lan1;inbound;VPN-1 & FireWall-1;;16
2;22-Nov-07;0:53:29;3.3.3.3;log;accept;;Lan1;inbound;VPN-1 & FireWall-1;;3
3;22-Nov-07;0:53:29;3.3.3.3;log;accept;;Lan1;inbound;VPN-1 & FireWall-1;;3
4;22-Nov-07;0:53:30;3.3.3.3;log;accept;;Lan1;inbound;VPN-1 & FireWall-1;;15
5;22-Nov-07;0:53:30;3.3.3.3;log;drop;;Lan1;inbound;VPN-1 & FireWall-1;;16
6;21-Nov-07;23:20:39;4.4.4.4;log;accept;;eth3c0;inbound;VPN-1 & FireWall-1;;8
7;21-Nov-07;23:20:40;4.4.4.4;log;accept;;eth3c0;inbound;VPN-1 & FireWall-1;;8
8;21-Nov-07;23:20:41;4.4.4.4;log;accept;;eth3c0;inbound;VPN-1 & FireWall-1;;8

# 2  
Old 12-07-2007
awk
hi

code:

Code:
nawk 'BEGIN{
FS=";"
format="%s;%s;%s;%s;%s;%s\n"
}
{
printf(format,$12,$10,$4,$3,$2,$1)
}' filename

# 3  
Old 12-07-2007
Sherry thanks for that!! But that is print columns based on their position in the file correct? The problem I have is that the column header stays the same but the number of the column in the file changes for example:

date may be column $2 today and $5 tomorrow and $7 the day after. It is totally random. So how could I print data by specifying which header name I want to print?
# 4  
Old 12-07-2007
# this will produce the 'default' columns: "rule product orig time date num"
nawk -f n3.awk myInputFile

# this will produce the 'custom' columns: "num orig time"
nawk -v cols='num orig time' -f n3.awk myInputFile

n3.awk:
Code:
BEGIN {
  FS=OFS=";"
  DEFcols="rule product orig time date num"

  if (cols == "")
    cols=DEFcols

  colsN=split(cols, colsA, " ")
}

FNR==1 {
  for(i=1; i<=NF; i++)
     headA[$i] = i
  next
}
{
  for(i=1; i<=colsN; i++)
    printf("%s%s", $headA[colsA[i]], (i==colsN) ? ORS : OFS)
}

# 5  
Old 12-08-2007
Thanks for all the help but I have a gentoo system that does not have nawk on it. Is there a way to install that on gentoo, I have not been able to find it yet. Or is there a way to run this script with awk or some other utility like perl that may be on my system?
# 6  
Old 12-08-2007
sorry for that post above I got this working with awk which is sym linked to gawk.

Thanks for this script its awesome!! Is there a way for it to print the header at the top also?
# 7  
Old 12-08-2007
I don't think this is working the way I need it, maybe the example I gave was not enough, I thought I could have figured out the script with some help from this forum but here goes some more:

Code:
My header can be from 70-100 columns(separated by semi-colons):

num;date;time;orig;type;action;alert;i/f_name;i/f_dir;product;log_sys_message;rule;rule_uid;rule_name;SmartDefense profile;service_id;ICMP;src;dst;proto;ICMP Type;ICMP Code;service;s_port;xlatesrc;xlatedst;NAT_rulenum;NAT_addtnl_rulenum;xlatedport;xlatesport;message_info;scheme:;methods:;peer gateway;encryption failure:;partner;community;fw_subproduct;vpn_feature_name;msg;scan direction;attack;Attack Info;TCP packet out of state;tcp_flags;srckeyid;dstkeyid;IKE:;CookieI;CookieR;msgid;IKE notification:;Certificate DN:;IKE IDs:;user;reason:;reject_category;Session:;L2TP:;PPP:;MAC:;OM:;om_method:;assigned_IP:;machine:;VPN internal source IP;start_time;vpn_user;message;old IP;old port;new IP;new port;spi;encryption fail reason:;rpc_prog;sys_message:;elapsed;reject_reason;System Alert message;Object;Event;Parameter;Condition;Current value;H.323 message;src phone number;dst phone number;H.323 Illegal redirection;dst scheme:;dst methods:;dst peer gateway;route status:;dst partner;dst community;Internal_CA:;serial_num:;dn:;message:


Out of all of these the ones I am looking for are (notice that one name has spaces in it):

date;time;origin;src;user;VPN internal source IP

The column names always stay the same but their order changes. I can't seem to get the code above to work with this many columns. Would be nice to print the header in the outpout file also
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

A cleaner way to rearrange column

Hello, I have some tab delimited text data, index name chg_p chg_m 1 name,1 1 0 2 name,2 1 1 3 name,3 1 0 4 name,4 1 0 5 name,5 1 1 I need to duplicate the "index" column, call it "id" and insert it after the... (8 Replies)
Discussion started by: LMHmedchem
8 Replies

2. UNIX for Dummies Questions & Answers

Transpose matrix, and rearrange columns common with another file

This is my first post, I apologize if I have broken rules. Some assistance with the following will be very helpful. I have a couple of files, both should ultimately have common columns only, arranged in the same order. This file needs to be transposed, to bring the rows to columns ... (2 Replies)
Discussion started by: abh.kumar
2 Replies

3. Shell Programming and Scripting

Rearrange Lines with awk

I need to rearrange the lines in the input file in the example below: Input: LG1 R500 A-170 F1:81 F1:22 F2:32 F1:71 LG1 R700 A-203 F2:17 E2:18 LG1 R700 B-224 E1:9 LG2 R500 C-235 E2:9 F2:17 Output: LG1 R500 A-170 F1:81 LG1 R500 A-170 F1:22 LG1 R500 A-170 F2:32 LG1 R500 A-170... (2 Replies)
Discussion started by: aydj
2 Replies

4. Shell Programming and Scripting

Using awk to rearrange fields

Hi, I am required to arrange columns of a file i.e make the 15th column into the 1st column. I am doing awk 'begin {fs=ofs=","} {print $15,$1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14}' ad.data>ad.csv the problem is that column 15 gets to column 1 but it is not comma separated with the... (10 Replies)
Discussion started by: seddoubt
10 Replies

5. Shell Programming and Scripting

how to rearrange a matrix with awk

Hi, every one. I have two files ,one is in matrix like this, one is a list with the same data as the matrix. AB AE AC AD AA AF SA 3 4 5 6 4 6 SC 5 7 2 8 4 3 SD 4 6 5 3 8 3 SE 45 ... (5 Replies)
Discussion started by: xshang
5 Replies

6. Shell Programming and Scripting

script to rearrange data.

Hello. I have data in the following format (the spaces at the beginning of lines are included): 1 2 2 0.39621 0.00000 1 2 2 0.00000+-0.0000 * 1 2 ... (5 Replies)
Discussion started by: andersgs
5 Replies

7. UNIX for Dummies Questions & Answers

Rearrange columns and rows with awk

Hello, I have the following problem I have two columns with numbers arranged as follows: x1 y1 x2 y2 .... .... x250 y250 Now I need them arranged as follows: "string a" x1 y1 x1 y2 "string b" "string a" x1 y2 x2 y2 (3 Replies)
Discussion started by: Tom46
3 Replies

8. Shell Programming and Scripting

rearrange a file

hi! in awk, i have a file like this: Trace1: WRIT,Trace2: BLAN,Trace3: BLAN, -47.2120018005371,,,39815.4809027778 -46.3009986877441,,,39815.4809027778 -46.277000427246,,,39815.4809143519 -46.7389984130859,,,39815.4809259259 -46.3460006713867,,,39815.4809259259... (10 Replies)
Discussion started by: riderman
10 Replies

9. UNIX for Dummies Questions & Answers

Help rearrange the content of the file.

Hi all, I need to rearrange the content of the file. I need to move line 1 to 4, 2 to 3, 3 to 2, and 4 to 1. IPlease help. I was thinking about using head and tail command. Here is the original file. aa bb cc dd Here is what I need it to be. dd cc bb aa Thanks. (6 Replies)
Discussion started by: sirrtuan
6 Replies

10. Shell Programming and Scripting

Need help in AWK;Search String and rearrange columns

Hi AWK Experts, file1.txt contains: 29b11b820ddcc:-|OHad.perWrk|spn_id=AH111|spn_ordtyp=MY_REQ|msg_typ=ah.ntf.out|spn_ordid=928176|spn_nid=3|msg_strt=1175615334703|msg_que=oput|diff=371|17:48:55,074|17:48:55,084|10 file2.txt contains:... (2 Replies)
Discussion started by: spring_buck
2 Replies
Login or Register to Ask a Question