Need help with a script to rearrange columns

n3al10 · #1 (**permalink**) 12-07-2007

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

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

summer_cherry · #2 (**permalink**) 12-07-2007

hi

code:

Code:

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

n3al10 · #3 (**permalink**) 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?

vgersh99 · #4 (**permalink**) 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)
}

n3al10 · #5 (**permalink**) 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?

n3al10 · #6 (**permalink**) 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?

More UNIX and Linux Forum Topics You Might Find Helpful
Thread	Thread Starter	Forum	Replies	Last Post
AWK script to print all the columns excpet the one specified	kn.naresh	Shell Programming and Scripting	3	05-22-2008 07:34 AM
shell script required to convert rows to columns	suresh3566	Shell Programming and Scripting	2	05-07-2008 05:25 AM
Shell script to separte columns...	sam_78_nyc	Shell Programming and Scripting	2	07-09-2007 05:21 PM
Need help in AWK;Search String and rearrange columns	spring_buck	Shell Programming and Scripting	2	04-05-2007 11:40 AM
Rearrange bytes within a txt file	yankee428	UNIX for Dummies Questions & Answers	4	06-16-2005 01:55 PM

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode	Rate This Thread:


	Powered by