Solaris - Filter columns in text file and adding new column | Unix Linux Forums | UNIX for Dummies Questions & Answers

  Go Back    


UNIX for Dummies Questions & Answers If you're not sure where to post a UNIX or Linux question, post it here. All UNIX and Linux newbies welcome !!

Solaris - Filter columns in text file and adding new column

UNIX for Dummies Questions & Answers


Tags
awk, cut, linux, script, solaris

Closed Thread    
 
Thread Tools Search this Thread Display Modes
    #1  
Old 09-19-2013
jpbastos jpbastos is offline
Registered User
 
Join Date: Sep 2013
Last Activity: 4 October 2013, 12:30 PM EDT
Posts: 5
Thanks: 3
Thanked 0 Times in 0 Posts
Solaris - Filter columns in text file and adding new column

Hello,
I am very now to this, hope you can help,
I am looking into editing a file in Solaris, with dinamic collums (lenght varies) and I need 2 things to be made, the fist is to filter the first column and third column from the file bellow file.txt, and create a new file with the 2 filtered collums, plus a third column with a value (0, 1 or 2) depending on the values of the second collum (0 for values 0-10, 1 for values 11-100, and 2 for values above 100)
EXAMPLE:
ORIGINAL FILE:
Code:
EH.ERROR.ADB_INSERT --------- 5* 0 11 0.0 Kb
EH.ERROR.ADB_UPDATE --------- 5* 0 0 0.0 Kb
ERP.SAP-PI.bpmMensagemSMS.EH ---+----- 5* 0 234 0.0 Kb
ERRORHANDLER.UI.CFG.POLICY.REQUEST ---+----- 5* 0 1000 0.0 Kb

RESULT FILE:
Code:
EH.ERROR.ADB_INSERT 11 1
EH.ERROR.ADB_UPDATE 0 0
ERP.SAP-PI.bpmMensagemSMS.EH 234 2
ERRORHANDLER.UI.CFG.POLICY.REQUEST 1000 2

NOTE: Collumns are alligned in original file, here in fórum they got out of place

---------- Post updated at 12:02 PM ---------- Previous update was at 11:59 AM ----------

As preiously stated, in original file collumns are alligned.
In bold the values to filter:
EH.ERROR.ADB_INSERT --------- 5* 0 11 0.0 Kb


Last edited by vbe; 09-19-2013 at 01:14 PM..
Sponsored Links
    #2  
Old 09-19-2013
vbe's Avatar
vbe vbe is offline Forum Staff  
Moderator
 
Join Date: Sep 2005
Last Activity: 17 April 2014, 3:54 AM EDT
Location: Switzerland - GE
Posts: 5,490
Thanks: 148
Thanked 362 Times in 339 Posts
columns format and indetation in code are kept if you use (and are asked to...) code tags!
Code tags are to be use for code AND data...
The Following User Says Thank You to vbe For This Useful Post:
jpbastos (10-02-2013)
Sponsored Links
    #3  
Old 09-19-2013
jpbastos jpbastos is offline
Registered User
 
Join Date: Sep 2013
Last Activity: 4 October 2013, 12:30 PM EDT
Posts: 5
Thanks: 3
Thanked 0 Times in 0 Posts
Thank you vbe!


Code:
ORIGINAL FILE:
EH.ERROR.ADB_INSERT                    ---------    5*     0       11     0.0 Kb
EH.ERROR.ADB_UPDATE                    ---------    5*     0        0     0.0 Kb
ERP.SAP-PI.bpmMensagemSMS.EH           ---+-----    5*     0      234     0.0 Kb
ERRORHANDLER.UI.CFG.POLICY.REQUEST     ---+-----    5*     0     1000     0.0 Kb

RESULT FILE:
EH.ERROR.ADB_INSERT                      11     1
EH.ERROR.ADB_UPDATE                       0     0
ERP.SAP-PI.bpmMensagemSMS.EH            234     2
ERRORHANDLER.UI.CFG.POLICY.REQUEST     1000     2

    #4  
Old 09-19-2013
in2nix4life's Avatar
in2nix4life in2nix4life is offline
Registered User
 
Join Date: Oct 2007
Last Activity: 22 April 2014, 10:12 PM EDT
Location: East Coast
Posts: 564
Thanks: 0
Thanked 161 Times in 147 Posts

Code:
awk '($5>=0&&$5<=10){a=0}($5>=11&&$5<=100){a=1}($5>100){a=2}{printf("%-40s %-10s %-10s\n",$1,$5,a)}' file

The Following User Says Thank You to in2nix4life For This Useful Post:
jpbastos (10-02-2013)
Sponsored Links
    #5  
Old 10-02-2013
jpbastos jpbastos is offline
Registered User
 
Join Date: Sep 2013
Last Activity: 4 October 2013, 12:30 PM EDT
Posts: 5
Thanks: 3
Thanked 0 Times in 0 Posts
Thank you very much for the reply, it has helped me a lot!!!

However I need an extra upgrade I guess shouldn´t be too complicated to implement:

The awk command gave me the result I needed, however I will need to change thresholds depending on the domain (eg: EH, ERP, etc), and above that add 2 extra columns with the min and max thresholds that are set for that row.

NOW:

Code:
 
awk '($5>=0&&$5<=10){a=0}($5>=11&&$5<=100){a=1}($5>100){a=2}{printf("%-40s %-10s %-10s\n",$1,$5,a)}' file
 
RESULT FILE:
EH.ERROR.ADB_INSERT                      11     1
EH.ERROR.ADB_UPDATE                       0     0
ERP.SAP-PI.bpmMensagemSMS.EH            234     2
ERRORHANDLER.UI.CFG.POLICY.REQUEST     1000     2
 
 
Now what I am trying to do is some if condition to distinguish the thresholds by domain, and add the 2 columns with the set low and max thresholds, like (example EH with lower thresholds and ERP with higher)
 
ORIGINAL FILE:
 
EH.ERROR.ADB_INSERT                    ---------    5*     0       11     0.0 Kb
EH.ERROR.ADB_UPDATE                    ---------    5*     0        5     0.0 Kb
ERP.SAP-PI.bpmMensagemSMS.EH           ---+-----    5*     0      234     0.0 Kb
ERRORHANDLER.UI.CFG.POLICY.REQUEST     ---+-----    5*     0     1000     0.0 Kb
 
if EH do  awk '($5>=0&&$5<=10){a=0}($5>=10&&$5<=20){a=1}($5>20){a=2}{printf("%-40s %-10s %-10s\n",$1,$5,a)}' 
if ERP do  awk '($5>=0&&$5<=300){a=0}($5>=301&&$5<=500){a=1}($5>500){a=2}{printf("%-40s %-10s %-10s\n",$1,$5,a)}' 
else do  awk '($5>=0&&$5<=10){a=0}($5>=11&&$5<=100){a=1}($5>100){a=2}{printf("%-40s %-10s %-10s\n",$1,$5,a)}' 
file
 
RESULTS:
EH.ERROR.ADB_INSERT                      11     2   10  20
EH.ERROR.ADB_UPDATE                       0     2   10  20
ERP.SAP-PI.bpmMensagemSMS.EH            234     0    300   500
ERRORHANDLER.UI.CFG.POLICY.REQUEST     1000     2     10   100

Any help is very much appreciatted!! Thank you in advance for your time!
Sponsored Links
    #6  
Old 10-02-2013
Don Cragun's Avatar
Don Cragun Don Cragun is offline Forum Staff  
Moderator
 
Join Date: Jul 2012
Last Activity: 23 April 2014, 2:39 AM EDT
Location: San Jose, CA, USA
Posts: 3,484
Thanks: 141
Thanked 1,207 Times in 1,022 Posts
Being given requirements little bits at a time, instead of giving us all of the requirements up front wastes our time and is likely to introduce significant delays in getting the results you want...

You explicitly state that the lower bound for range 1 is 0 and in2nix4life's script assumes that $5 will never contain a negative number. (If a negative number is given, in2nix4life's script will print the range value for the previous input line.)

You said that field widths will vary but in2nix4life's script assumed upper bounds of 40 characters for column 1 and 10 characters for column 5.

The following script seems to meet your updated requirements (and it should be clear how to add additional domains with different sets of ranges). This script reads the file twice; the 1st pass gets the maximum field widths present in your input, and the 2nd pass prints the results. If column 5's value is less than zero, this script will print -1 for the range code. You can ignore that test if you know no input will ever have a negative value, or you can do something else where that test occurs if you want to print an error message, just remove that line's data from the output, or take some other action.


Code:
/usr/xpg4/bin/awk '
BEGIN {#ERE to match domain     Range 0 max     Range 1 max
        dom[0] = "^EH\.";       rm[0,0] = 10;   rm[0,1] = 20     
        dom[1] = "^ERP\.";      rm[1,0] = 300;  rm[1,1] = 500     
        dom[2] = ".*";          rm[2,0] = 10;   rm[2,1] = 100       
        ndom=3
        # determine # of columns needed to print range values...  
        for(i = 0; i < ndom; i++) {    
                if(length(rm[i,0]) > mr0) mr0 = length(rm[i,0])  
                if(length(rm[i,1]) > mr1) mr1 = length(rm[i,1])   
        }
}
FNR == NR {
        # 1st pass through the file; get max lengths of fields 1 and 5    
        if(length($1) > m1) m1 = length($1)
        if(length($5) > m5) m5 = length($5)
        next
}
FNR == 1 {
        # 1st line in 2nd pass; create format string to be used...  
        fmt = sprintf("%%-%ds%%%dd%%3d%%%dd%%%dd\n",
                m1, m5 + 1, mr0 + 1, mr1 + 1)
}
{       # 2nd pass; print the results...
        for(i = 0; i < ndom; i++) {
                if($1 ~ dom[i]) {
                        r = ($5 > rm[i,0]) + ($5 > rm[i,1])
                        break
                }        }
        if($5 < 0) r = -1
        printf(fmt, $1, $5, r, rm[i,0], rm[i,1])
}' file file

Since you said you're doing this on a Solaris system, I specified /usr/xpg4/bin/awk (instead of the default /bin/awk or /usr/bin/awk ) as the utility to use to run this awk script. If you want to run it on another system, change the first line of the script from:

Code:
/usr/xpg4/bin/awk '

to just:
Code:
awk '

The Following User Says Thank You to Don Cragun For This Useful Post:
jpbastos (10-03-2013)
Sponsored Links
    #7  
Old 10-03-2013
jpbastos jpbastos is offline
Registered User
 
Join Date: Sep 2013
Last Activity: 4 October 2013, 12:30 PM EDT
Posts: 5
Thanks: 3
Thanked 0 Times in 0 Posts
Don thank you very very much for your help, it is working nearly to perfection

My appologies if I don´t give all info at once, I am quite newbie to this as I mention, and just learning as I go along.....

Your solution works perfect, the only thing I am not very clear is how I can add a specific range for a specific line, is that possible?

Say I would only want to change thresholds for EH.ERROR.ADB_INSERT but not EH.ERROR.ADB_UPDATE, I tried this but does not work


Code:
 
EH.ERROR.ADB_INSERT --------- 5* 0 11 0.0 Kb
EH.ERROR.ADB_UPDATE --------- 5* 0 0 0.0 Kb
ERP.SAP-PI.bpmMensagemSMS.EH ---+----- 5* 0 234 0.0 Kb
ERRORHANDLER.UI.CFG.POLICY.REQUEST ---+----- 5* 0 1000 0.0 Kb


I tried this but did not work....


Code:
BEGIN {#ERE to match domain            Range 0 max        Range 1 max
        dom[0] = ".*";                 rm[0,0] = 1000;    rm[0,1] = 2000     
        dom[1] = "^EH.ERROR.ADB_INSERT\.";        rm[1,0] = 15000;      rm[1,1] = 20000     
        dom[2] = "^ERP\.";       rm[2,0] = 10000;      rm[2,1] = 20000
        ndom=3
        # determine # of columns needed to print range values...  
        for(i = 0; i < ndom; i++) {    
                if(length(rm[i,0]) > mr0) mr0 = length(rm[i,0])  
                if(length(rm[i,1]) > mr1) mr1 = length(rm[i,1])   
        }


Again, thank you very much for your time and patience with this newbie :P
Sponsored Links
Closed Thread

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

More UNIX and Linux Forum Topics You Might Find Helpful
Thread Thread Starter Forum Replies Last Post
Adding a column to a text file with row numbers evelibertine UNIX for Dummies Questions & Answers 5 12-20-2012 09:04 AM
Adding a new column in a file with other existing columns Pratik4891 Shell Programming and Scripting 8 09-21-2010 03:24 PM
Suggestions for adding columns to text file LMHmedchem Shell Programming and Scripting 9 03-28-2010 01:13 AM
Adding a column to a text based on file name rlapate Shell Programming and Scripting 12 05-23-2009 09:22 PM
Adding a new column in a text file snahata Shell Programming and Scripting 10 03-13-2009 07:00 AM



All times are GMT -4. The time now is 08:10 AM.